Nested IF & MAX Function

[Graham]

Hi, I’ve 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 ?

I’m sure it is a relatively simple solution but I just cant see where to go
with it, any help very much appreciated .

 

[Teethless mama]

=IF(P13="",0,your_formula)

 

[Bernard Liengme]

First, you do not need the SUM function in this formula
Second, to return zero when cell M is empty use
=IF(M13<>"",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),0)
If you need to check that both M and P cells are not empty
=IF(AND(M13,P13)<>"",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),0)
best wishes

 

[Stefi]

And consider again using SUM! There is no reason for it in this formula!

Regards,
Stefi


„Teethless mama†ezt írta:

 

[Graham]

Thanks to all of you for your help. The SUM function was I think
automatically added by the Function Builder, but I've now taken it out.
It didn't seem to like ...P13="".... so as the column has a default '0',
I've used that instead, and all appears to be working OK.
Many Thanks again!!