Min Value

[dksaluki]

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 Rothstein \(MVP - VB\)]

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

 

[Earl Kiosterud]

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)

 

[dksaluki]

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

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- 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