Nested IF & MAX Function
Hi, Ive created a formula with nested IF & MAX functions, as follows:
=SUM(ROUND(IF(M13="N",IF(N13=0,MAX((P13/1000)*5,35),(P13/1000)*3.1),IF(N13=0,MAX((P13/1000)*7,35),(P13/1000)*5.1)),2))
Column M is either N or Y (True or False) Default value =N
Column N may or may not have a numeric value.
Column P may or may not have a numeric value.
The above formula needs to return a different value depending upon whether M
is N or Y, AND whether there is a value or not in N. (If there is no
value in P, it should return 0, see below)
The formula works fine, and where it returns a value, this should be a
minimum value of 35 (hence MAX function returning the greater of the product,
or 35.
This again works fine.
However, where there is no value in either N or P i.e. blank rows on the
sheet that have yet to be used, it is still returning 35. How can I get it to
return 0 when there are no entries in P ?
Im sure it is a relatively simple solution but I just cant see where to go
with it, any help very much appreciated .
|