IIF issue

S

Scott

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
 
R

Ron Coderre

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
 
S

Scott

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
 
R

Ron Coderre

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
 
S

Scott

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
 
R

Ron Coderre

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
 
H

Herbert Seidenberg

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