Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IIF issue
I need to do the calculation according to the availability of data as
following example. 1. Provision of both cell A1 and A2, perform formula 1. 2. Provision of all cell A1, A2, A3 & A4, perform formula 2. 3. Other than above condition, do not perform any calculation. Can someone advise if I should use IIF or other function to accomplish the required result. In addition, what is the maximum length for a cell to accept the statement? Thanks, Scott |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IIF issue
I'm guessing that cells A1:A4 contain numbers....
Perhaps one of these will work: B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(COUNT(A1:A2)=2," FORMULA_1","DO NOTHING")) or B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),"FORMULA_1","DO NOTHING")) Does either of those help? *********** Regards, Ron XL2002, WinXP-Pro "Scott" wrote: I need to do the calculation according to the availability of data as following example. 1. Provision of both cell A1 and A2, perform formula 1. 2. Provision of all cell A1, A2, A3 & A4, perform formula 2. 3. Other than above condition, do not perform any calculation. Can someone advise if I should use IIF or other function to accomplish the required result. In addition, what is the maximum length for a cell to accept the statement? Thanks, Scott |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IIF issue
Ron,
Thanks for your suggestion. Both work correctly to my requirement. However, there are two minor issues. 1. If the available data do not conform to the requirements for formula 1 or 2, it shows FALSE. I do not need FALSE and only blank the resulting cell. 2. If the data are available on A1, A2 & A3 or A1, A2 & A4, the resulting cell remains the result of formula 1. Is it possible? As the formulas are quite long, I am afraid to exceed the permissible limit of each cell. If so, I need to do it in a few cells. Scott "Ron Coderre" wrote in message ... I'm guessing that cells A1:A4 contain numbers.... Perhaps one of these will work: B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(COUNT(A1:A2)=2," FORMULA_1","DO NOTHING")) or B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),"FORMULA_1","DO NOTHING")) Does either of those help? *********** Regards, Ron XL2002, WinXP-Pro "Scott" wrote: I need to do the calculation according to the availability of data as following example. 1. Provision of both cell A1 and A2, perform formula 1. 2. Provision of all cell A1, A2, A3 & A4, perform formula 2. 3. Other than above condition, do not perform any calculation. Can someone advise if I should use IIF or other function to accomplish the required result. In addition, what is the maximum length for a cell to accept the statement? Thanks, Scott |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IIF issue
If I understand you correctly, then:
If A1:A4 all have values...then FORMULA_2 Otherwise, if A1:A2 have values....then FORMULA_1 (and it doesn't matter if A3 or A4 have values or not) If that's correct then: B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(COUNT(A1:A2)=2," FORMULA_1","DO NOTHING")) Did I get it right? *********** Regards, Ron XL2002, WinXP-Pro "Scott" wrote: Ron, Thanks for your suggestion. Both work correctly to my requirement. However, there are two minor issues. 1. If the available data do not conform to the requirements for formula 1 or 2, it shows FALSE. I do not need FALSE and only blank the resulting cell. 2. If the data are available on A1, A2 & A3 or A1, A2 & A4, the resulting cell remains the result of formula 1. Is it possible? As the formulas are quite long, I am afraid to exceed the permissible limit of each cell. If so, I need to do it in a few cells. Scott "Ron Coderre" wrote in message ... I'm guessing that cells A1:A4 contain numbers.... Perhaps one of these will work: B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(COUNT(A1:A2)=2," FORMULA_1","DO NOTHING")) or B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),"FORMULA_1","DO NOTHING")) Does either of those help? *********** Regards, Ron XL2002, WinXP-Pro "Scott" wrote: I need to do the calculation according to the availability of data as following example. 1. Provision of both cell A1 and A2, perform formula 1. 2. Provision of all cell A1, A2, A3 & A4, perform formula 2. 3. Other than above condition, do not perform any calculation. Can someone advise if I should use IIF or other function to accomplish the required result. In addition, what is the maximum length for a cell to accept the statement? Thanks, Scott |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IIF issue
Ron,
Your understanding is quite correct. I forgot one condition. If the data are available on A1, A2 & A3 or A1, A2 & A4, the resulting cell remains the result of formula 1. The four cell figures are come from different sections at different time. Scott "Ron Coderre" wrote in message ... If I understand you correctly, then: If A1:A4 all have values...then FORMULA_2 Otherwise, if A1:A2 have values....then FORMULA_1 (and it doesn't matter if A3 or A4 have values or not) If that's correct then: B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(COUNT(A1:A2)=2," FORMULA_1","DO NOTHING")) Did I get it right? *********** Regards, Ron XL2002, WinXP-Pro "Scott" wrote: Ron, Thanks for your suggestion. Both work correctly to my requirement. However, there are two minor issues. 1. If the available data do not conform to the requirements for formula 1 or 2, it shows FALSE. I do not need FALSE and only blank the resulting cell. 2. If the data are available on A1, A2 & A3 or A1, A2 & A4, the resulting cell remains the result of formula 1. Is it possible? As the formulas are quite long, I am afraid to exceed the permissible limit of each cell. If so, I need to do it in a few cells. Scott "Ron Coderre" wrote in message ... I'm guessing that cells A1:A4 contain numbers.... Perhaps one of these will work: B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(COUNT(A1:A2)=2," FORMULA_1","DO NOTHING")) or B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),"FORMULA_1","DO NOTHING")) Does either of those help? *********** Regards, Ron XL2002, WinXP-Pro "Scott" wrote: I need to do the calculation according to the availability of data as following example. 1. Provision of both cell A1 and A2, perform formula 1. 2. Provision of all cell A1, A2, A3 & A4, perform formula 2. 3. Other than above condition, do not perform any calculation. Can someone advise if I should use IIF or other function to accomplish the required result. In addition, what is the maximum length for a cell to accept the statement? Thanks, Scott |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IIF issue
The formula I submitted evaluates as follows:
A1 A2 A3 A4 Result VALUE VALUE VALUE VALUE Formula_2 VALUE VALUE VALUE blank Formula_1 VALUE VALUE blank VALUE Formula_1 VALUE VALUE blank blank Formula_1 ALL OTHER COMBINATIONS Nothing If that is not doing what you want, I'll need a little more guidance. *********** Regards, Ron XL2002, WinXP-Pro "Scott" wrote: Ron, Your understanding is quite correct. I forgot one condition. If the data are available on A1, A2 & A3 or A1, A2 & A4, the resulting cell remains the result of formula 1. The four cell figures are come from different sections at different time. Scott "Ron Coderre" wrote in message ... If I understand you correctly, then: If A1:A4 all have values...then FORMULA_2 Otherwise, if A1:A2 have values....then FORMULA_1 (and it doesn't matter if A3 or A4 have values or not) If that's correct then: B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(COUNT(A1:A2)=2," FORMULA_1","DO NOTHING")) Did I get it right? *********** Regards, Ron XL2002, WinXP-Pro "Scott" wrote: Ron, Thanks for your suggestion. Both work correctly to my requirement. However, there are two minor issues. 1. If the available data do not conform to the requirements for formula 1 or 2, it shows FALSE. I do not need FALSE and only blank the resulting cell. 2. If the data are available on A1, A2 & A3 or A1, A2 & A4, the resulting cell remains the result of formula 1. Is it possible? As the formulas are quite long, I am afraid to exceed the permissible limit of each cell. If so, I need to do it in a few cells. Scott "Ron Coderre" wrote in message ... I'm guessing that cells A1:A4 contain numbers.... Perhaps one of these will work: B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(COUNT(A1:A2)=2," FORMULA_1","DO NOTHING")) or B1: =IF(COUNT(A1:A4)=4,"FORMULA_2",IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),"FORMULA_1","DO NOTHING")) Does either of those help? *********** Regards, Ron XL2002, WinXP-Pro "Scott" wrote: I need to do the calculation according to the availability of data as following example. 1. Provision of both cell A1 and A2, perform formula 1. 2. Provision of all cell A1, A2, A3 & A4, perform formula 2. 3. Other than above condition, do not perform any calculation. Can someone advise if I should use IIF or other function to accomplish the required result. In addition, what is the maximum length for a cell to accept the statement? Thanks, Scott |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IIF issue
Assuming you have four variables, arrange your data as shown
Dim Got dia 4.56 1 len 2.36 1 thk 0 wth 3.32 1 Pattern 5 Result 7.84 Name the Dim and Got column and Pattern cell Enter values in Dim or leave blank. In Got, enter this formula: =--NOT(ISBLANK(Dim)) In Pattern enter this formula: =16-SUMPRODUCT(Got,{1;2;4;8}) Record the Pattern number you get with different blank entries in Dim. Select the Result cell and type into the formula bar: =CHOOSE() and click on the Insert Function button. Enter into the Function Arguments window, Index Number: Pattern Enter formulas in Value1 thru Value29 that correspond to Pattern numbers. For example Value5 has the formula: len*wth. If you have more than 4 variables, Pattern might exceed 29. In that case make a lookup table that AND/ORs duplicate or don't care Pattern numbers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Issue with zero/# issue!! | Excel Worksheet Functions | |||
vlookup issue ( not working and im tearing my hair out) | Excel Discussion (Misc queries) | |||
Vlookup Calculation Issue | Excel Worksheet Functions | |||
Another rounding issue | Excel Worksheet Functions | |||
Excel Viewer performance issue | Excel Discussion (Misc queries) |