IF AND...

[Gale Gorman]

I have the following formula:
=SUM(IF (E81 <> 0, (((((E81 / 15) +1.25) * (I4 + 110)) +
150) * 2)))

and this works.

I am determining move in/move out times for a crane to a
jobsite.

The reason for the IF <> 0 is there are several rows with
different size cranes and I only want to calculate the one
that is chosen for the job.
E81 contains a qty of miles traveled.
I divide that by 15mph and then add 1.25 miles.
Then I multiply that times the rate per hour for the crane
(I4) and add $110 for a truck that must accompany the crane.
Next is a motorcycle escort that is a flat $150, and finally
the 'times 2' accounts for 'move out.'

What I want this to do is test E81 to see if the number is
greater than 15 and then divide the amount over 15 miles by
40mph. The first 15 miles needs to remain at 15 mph.

Thanks in advance,

Gale Gorman
Houston

 

[Bob Phillips]

Gale,

Is this what you want?

=SUM(IF(E81<>0,((((MIN(E81,15)/15+(E81-15)*(E81>15)/40)+1.25)*(I4+110))+150)
*2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Gale Gorman]

That worked beautifully! I just noticed your reply came
from the UK. Newsgroups have really cut the world down to a
manageable size.

Thanks,

Gale Gorman
Houston