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