Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and OR nested function | Excel Discussion (Misc queries) | |||
Nested IF Function | Excel Worksheet Functions | |||
can you nested sum and round function within if function? | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |