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 .

Nested IF & MAX Function
 

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


"Graham" wrote:

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 .

Nested IF & MAX Function
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Graham" wrote in message
...
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 .

Nested IF & MAX Function
 

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

Regards,
Stefi


€žTeethless mama€� ezt Ã*rta:

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


"Graham" wrote:

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 .

Nested IF & MAX Function
 

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Graham" wrote in message
...
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 .