Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some function help...
I have this function in a particular cell;
=IF(D550,IF(AND(E18=1,D14<26),6,IF(AND(E17&E18="1 0",D14=26),"0",IF(E17=1,IF(D14<36.344,"0",IF(AND( D14=36.344,D14<39.549),1,2)),IF(D14<37.11,"0",IF( AND(D14=37.11,D14<39.174),3,4))))),0) I need to add the following to it, and I can't figure out how to do it without having 7 nested 'IF' statements; =IF(AND(D14<36.344,E17=1),"0",IF(AND(D14<37.11,E17 =2),"0","X")) In addition, is it possible to somehow merge the functions that require a returned value of "0" with 'OR' statements so that I can minimize my nested 'IF' statements? I can't glean too much information from the 'help' examples that is provided with Excel, or from the on-line help. The reason that I have quotations around the zeros is because I need the returned value of zero do display, and I have 'display zero values' turned off for a particular reason. Thank you in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some function help...
I would create a lookup table and run VLOOKUPs off that.
Or split the IF formula up into multiple formulas. Some other thoughts are he http://www.cpearson.com/excel/nested.htm Dave -- Brevity is the soul of wit. "TR Young" wrote: I have this function in a particular cell; =IF(D550,IF(AND(E18=1,D14<26),6,IF(AND(E17&E18="1 0",D14=26),"0",IF(E17=1,IF(D14<36.344,"0",IF(AND( D14=36.344,D14<39.549),1,2)),IF(D14<37.11,"0",IF( AND(D14=37.11,D14<39.174),3,4))))),0) I need to add the following to it, and I can't figure out how to do it without having 7 nested 'IF' statements; =IF(AND(D14<36.344,E17=1),"0",IF(AND(D14<37.11,E17 =2),"0","X")) In addition, is it possible to somehow merge the functions that require a returned value of "0" with 'OR' statements so that I can minimize my nested 'IF' statements? I can't glean too much information from the 'help' examples that is provided with Excel, or from the on-line help. The reason that I have quotations around the zeros is because I need the returned value of zero do display, and I have 'display zero values' turned off for a particular reason. Thank you in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some function help...
It might be easier to provide you with a solution if you enumerate in a
list what conditions result in what values, and what precedence they have. That way, we can help you with the simplification without compromising your results. ie. Put an * where the value can be anything. Also, are E17/E18 limited to 0 or 1 as values? IF(D55<=0, E17=*, E18 = *, D14 = *) - 0 (Highest precedence) IF(D550, E17=*, E18=1,D14<26) - 0 (Next) .... etc. Scott TR Young wrote: I have this function in a particular cell; =IF(D550,IF(AND(E18=1,D14<26),6,IF(AND(E17&E18="1 0",D14=26),"0",IF(E17=1,IF(D14<36.344,"0",IF(AND( D14=36.344,D14<39.549),1,2)),IF(D14<37.11,"0",IF( AND(D14=37.11,D14<39.174),3,4))))),0) I need to add the following to it, and I can't figure out how to do it without having 7 nested 'IF' statements; =IF(AND(D14<36.344,E17=1),"0",IF(AND(D14<37.11,E17 =2),"0","X")) In addition, is it possible to somehow merge the functions that require a returned value of "0" with 'OR' statements so that I can minimize my nested 'IF' statements? I can't glean too much information from the 'help' examples that is provided with Excel, or from the on-line help. The reason that I have quotations around the zeros is because I need the returned value of zero do display, and I have 'display zero values' turned off for a particular reason. Thank you in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some function help...
It might be easier to provide you with a solution if you enumerate in a
list what conditions result in what values, and what precedence they have. That way, we can help you with the simplification without compromising your results. ie. Put an * where the value can be anything. Also, are E17/E18 limited to 0 or 1 as values? IF(D55<=0, E17=*, E18 = *, D14 = *) - 0 (Highest precedence) IF(D550, E17=*, E18=1,D14<26) - 0 (Next) .... etc. Scott TR Young wrote: I have this function in a particular cell; =IF(D550,IF(AND(E18=1,D14<26),6,IF(AND(E17&E18="1 0",D14=26),"0",IF(E17=1,IF(D14<36.344,"0",IF(AND( D14=36.344,D14<39.549),1,2)),IF(D14<37.11,"0",IF( AND(D14=37.11,D14<39.174),3,4))))),0) I need to add the following to it, and I can't figure out how to do it without having 7 nested 'IF' statements; =IF(AND(D14<36.344,E17=1),"0",IF(AND(D14<37.11,E17 =2),"0","X")) In addition, is it possible to somehow merge the functions that require a returned value of "0" with 'OR' statements so that I can minimize my nested 'IF' statements? I can't glean too much information from the 'help' examples that is provided with Excel, or from the on-line help. The reason that I have quotations around the zeros is because I need the returned value of zero do display, and I have 'display zero values' turned off for a particular reason. Thank you in advance! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some function help...
D55 is either going to be 0 or the name of an extrusion we use. If D55=0,
then the rest of the function is null. E17 and E18 are limited to 0 and 1. D14 is a number which will be entered by a user. The number can be any number between 20 and 39.549. Hope that helps! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some function help...
A bit, but not completely. Looking at your formula, some of the
statements seem to conflict with each other. Based on your formula, it seems that you want this: E17 = 1, E18 = 1, then for D14: a) 6 for [20, 26) b) 0 for [26, 36.344) c) 1 for [36.344, 39.549) d) 2 for [39.549] Note that square brackets mean that the value on that side is included in the range, and round brackets mean that the value on that side is excluded. For example, in (a), 26 is followed by a round bracket, so it is not in this range. In (b), it is after a square bracket, so it is included in this range, and would return a value of 0. (Just clarifying in case you're not familiar with the notation.) If you could break down the range like I have, so that all values for D14 from 20 to 39.549 are covered for each combination: Assuming D55 0, of course. (1) E17 = 1, E18 = 1 (2) E17 = 1, E18 = 0 (3) E17 = 0, E18 = 1 (4) E17 = 0, E18 = 0 Hopefully that makes sense. Scott TR Young wrote: D55 is either going to be 0 or the name of an extrusion we use. If D55=0, then the rest of the function is null. E17 and E18 are limited to 0 and 1. D14 is a number which will be entered by a user. The number can be any number between 20 and 39.549. Hope that helps! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need some function help...
Thanks for you help; I finally figured out how to do it.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |