=MAX(MIN(2.63,B2-C2+D2+E2),0)

Here's an alternative for 3 numbers.

=MEDIAN(0, B2-C2+D2+E2, 2.63)

--
Dana DeLouis


"Farmer Jeff" wrote in message ...

Sandy, Thank you so much, you have no idea how much work this is going to
save me!

How is the weather in Scotland?

Cheers,
Farmer Jeff

"Sandy Mann" wrote:


Try:

=MAX(MIN(2.63,B2-C2+D2+E2),0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Farmer Jeff" <Farmer wrote in message
...

I need to create a tricky formula.
It needs to start with my soil's water carrying capacity (2.63, in Column
B), subtract from it the daily evaporation amount (Column C), add any rain
(Column D)or irrigation water (Column E), and give me the current moisture
level in the soil (Column F). Simple up to this point, I just do: F=
B-C+D+E.

The tricky part is this: the total cannot exceed 2.63, even if 4 inches of
rain fall, since over 2.63 the water runs off, and it also cannot fall
below
0, even if the evaporation on a given day exceeds 2.63, since you can't
have
less than no water.

I have been trying to figure out how to write this using the IF function,
but I end up getting a TRUE or FALSE instead of a numeric value. I really
would appreciate the help on this. Thanks,
Farmer Jeff