IIF issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott - 18 Dec 2005 17:16 GMT

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

Reply to this Message

Ron Coderre - 18 Dec 2005 17:27 GMT

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

> I need to do the calculation according to the availability of data as
> following example.
[quoted text clipped - 10 lines]
>
> Scott

Reply to this Message

Scott - 18 Dec 2005 18:09 GMT

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

> I'm guessing that cells A1:A4 contain numbers....
>
[quoted text clipped - 29 lines]
>>
>> Scott

Reply to this Message

Ron Coderre - 18 Dec 2005 18:21 GMT

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

> Ron,
>
[quoted text clipped - 47 lines]
> >>
> >> Scott

Reply to this Message

Scott - 18 Dec 2005 18:53 GMT

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

> If I understand you correctly, then:
>
[quoted text clipped - 69 lines]
>> >>
>> >> Scott

Reply to this Message

Ron Coderre - 18 Dec 2005 19:14 GMT

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

> Ron,
>
[quoted text clipped - 78 lines]
> >> >>
> >> >> Scott

Reply to this Message

Herbert Seidenberg - 18 Dec 2005 19:55 GMT

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 to this Message