Min Value

Here is an equation I'm using to calculate intersection sight
distance:
ISD = 1.47 (Vmajor) [T + (G - 3%)0.2 + (W/12)0.5]

Each variable will be a cell, for instance, G could be B12. When G
is less that 3%, I don't want it to do that part of the equation. The
actual value of G could be 2.57%, but when it is, I don't it to
"subtract 3%, then mult. by .2 In the EQUATION, i want that term,
(G - 3%)0.2, to be a minimum of zero. See, when G is less than 3%,
that whole term is negative, and i don't want that. Can anyone help
me think of a way to not do that part of the equation when G < 3%?
Is there some Excel function I'm not thinking of? I'm trying to avoid
tons of nested IF statements.

Much appreciated,
dk

Rick
Sat May 10 22:55:35 CDT 2008

Ah, that takes me back. Somewhere around 1990 or so, I wrote a Fortran IV
program to calculate any part of the sight distance triangle at a railroad
crossing given the other parts (I was a Civil Engineer in our Road Design
Unit doing various computer programming on the side back then). Anyway, for
simplicity sake, let's assume your variable letters are Column letters in
Row 1. Then I think this formula will do same calculation that the formula
you show does...

=1.47*V1*(T1+0.2*MAX(0,G1-3%)+W1/24)

Rick


"dksaluki" <dksaluki@gmail.com> wrote in message
news:0c29b4a1-d681-424e-82c1-bb15876a671e@e39g2000hsf.googlegroups.com...
> Here is an equation I'm using to calculate intersection sight
> distance:
> ISD = 1.47 (Vmajor) [T + (G - 3%)0.2 + (W/12)0.5]
>
> Each variable will be a cell, for instance, G could be B12. When G
> is less that 3%, I don't want it to do that part of the equation. The
> actual value of G could be 2.57%, but when it is, I don't it to
> "subtract 3%, then mult. by .2 In the EQUATION, i want that term,
> (G - 3%)0.2, to be a minimum of zero. See, when G is less than 3%,
> that whole term is negative, and i don't want that. Can anyone help
> me think of a way to not do that part of the equation when G < 3%?
> Is there some Excel function I'm not thinking of? I'm trying to avoid
> tons of nested IF statements.
>
> Much appreciated,
> dk

Earl
Sat May 10 23:08:51 CDT 2008

dk,

This will not allow G - 3% to evaluate to less than zero.
MAX(0, G - 3%)

I don't know what you mean with the brackets []. In a formula, you must use the * operator
for multiplication. So I think you mean
= 1.47 *(Vmajor) * (T + (G - 3%) * 0.2 + (W/12) * 0.5)

so you need:
= 1.47 *(Vmajor) * (T + MAX(0, G - 3%) * 0.2 + (W/12) * 0.5)

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"dksaluki" <dksaluki@gmail.com> wrote in message
news:0c29b4a1-d681-424e-82c1-bb15876a671e@e39g2000hsf.googlegroups.com...
> Here is an equation I'm using to calculate intersection sight
> distance:
> ISD
>
> Each variable will be a cell, for instance, G could be B12. When G
> is less that 3%, I don't want it to do that part of the equation. The
> actual value of G could be 2.57%, but when it is, I don't it to
> "subtract 3%, then mult. by .2 In the EQUATION, i want that term,
> (G - 3%)0.2, to be a minimum of zero. See, when G is less than 3%,
> that whole term is negative, and i don't want that. Can anyone help
> me think of a way to not do that part of the equation when G < 3%?
> Is there some Excel function I'm not thinking of? I'm trying to avoid
> tons of nested IF statements.
>
> Much appreciated,
> dk

dksaluki
Sun May 11 14:01:10 CDT 2008

On May 10, 11:55=A0pm, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Ah, that takes me back. Somewhere around 1990 or so, I wrote a Fortran IV
> program to calculate any part of the sight distance triangle at a railroad=

> crossing given the other parts (I was a Civil Engineer in our Road Design
> Unit doing various computer programming on the side back then). Anyway, fo=
r
> simplicity sake, let's assume your variable letters are Column letters in
> Row 1. Then I think this formula will do same calculation that the formula=

> you show does...
>
> =3D1.47*V1*(T1+0.2*MAX(0,G1-3%)+W1/24)
>
> Rick
>
> "dksaluki" <dksal...@gmail.com> wrote in message
>
> news:0c29b4a1-d681-424e-82c1-bb15876a671e@e39g2000hsf.googlegroups.com...
>
>
>
> > Here is an equation I'm using to calculate intersection sight
> > distance:
> > ISD =3D 1.47 (Vmajor) [T + (G - 3%)0.2 + (W/12)0.5]
>
> > Each variable will be a cell, for instance, G could be B12. =A0 When G
> > is less that 3%, I don't want it to do that part of the equation. =A0The=

> > actual value of G could be 2.57%, but when it is, I don't it to
> > "subtract 3%, then mult. by .2 =A0 =A0In the EQUATION, i want that term,=

> > (G - 3%)0.2, to be a minimum of zero. =A0See, when G is less than 3%,
> > that whole term is negative, and i don't want that. =A0Can anyone help
> > me think of a way to not do that part of the equation when G < 3%?
> > Is there some Excel function I'm not thinking of? =A0I'm trying to avoid=

> > tons of nested IF statements.
>
> > Much appreciated,
> > dk- Hide quoted text -
>
> - Show quoted text -

ahh! the max function! see, i knew there was a way! awesome, thank
you so much!!
it's funny how it seems so simple once it gets pointed out! hehe

Thanks again,
dk