software

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 amp; 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
I'm guessing that cells A1:A4 contain numbers....

Perhaps one of these will work:
B1: =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(COUNT(A1:A2)=2,quot; FORMULA_1quot;,quot;DO
NOTHINGquot;))
or
B1:
=IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),quot;FORMULA_1quot;,quot;DO NOTHINGquot;))

Does either of those help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Scottquot; wrote:

gt; I need to do the calculation according to the availability of data as
gt; following example.
gt;
gt; 1. Provision of both cell A1 and A2, perform formula 1.
gt; 2. Provision of all cell A1, A2, A3 amp; A4, perform formula 2.
gt; 3. Other than above condition, do not perform any calculation.
gt;
gt; Can someone advise if I should use IIF or other function to accomplish the
gt; required result. In addition, what is the maximum length for a cell to
gt; accept the statement?
gt;
gt; Thanks,
gt;
gt; Scott
gt;
gt;
gt;

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 amp; A3 or A1, A2 amp; 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

quot;Ron Coderrequot; gt; wrote in message
...
gt; I'm guessing that cells A1:A4 contain numbers....
gt;
gt; Perhaps one of these will work:
gt; B1: =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(COUNT(A1:A2)=2,quot; FORMULA_1quot;,quot;DO
gt; NOTHINGquot;))
gt; or
gt; B1:
gt; =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),quot;FORMULA_1quot;,quot;DO
gt; NOTHINGquot;))
gt;
gt; Does either of those help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Scottquot; wrote:
gt;
gt;gt; I need to do the calculation according to the availability of data as
gt;gt; following example.
gt;gt;
gt;gt; 1. Provision of both cell A1 and A2, perform formula 1.
gt;gt; 2. Provision of all cell A1, A2, A3 amp; A4, perform formula 2.
gt;gt; 3. Other than above condition, do not perform any calculation.
gt;gt;
gt;gt; Can someone advise if I should use IIF or other function to accomplish
gt;gt; the
gt;gt; required result. In addition, what is the maximum length for a cell to
gt;gt; accept the statement?
gt;gt;
gt;gt; Thanks,
gt;gt;
gt;gt; Scott
gt;gt;
gt;gt;
gt;gt;
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,quot;FORMULA_2quot;,IF(COUNT(A1:A2)=2,quot; FORMULA_1quot;,quot;DO
NOTHINGquot;))Did I get it right?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Scottquot; wrote:

gt; Ron,
gt;
gt; Thanks for your suggestion. Both work correctly to my requirement.
gt; However, there are two minor issues.
gt;
gt; 1. If the available data do not conform to the requirements for formula 1
gt; or 2, it shows FALSE. I do not need FALSE and only blank the resulting
gt; cell.
gt;
gt; 2. If the data are available on A1, A2 amp; A3 or A1, A2 amp; A4, the resulting
gt; cell remains the result of formula 1. Is it possible?
gt;
gt; As the formulas are quite long, I am afraid to exceed the permissible limit
gt; of each cell.
gt; If so, I need to do it in a few cells.
gt;
gt; Scott
gt;
gt; quot;Ron Coderrequot; gt; wrote in message
gt; ...
gt; gt; I'm guessing that cells A1:A4 contain numbers....
gt; gt;
gt; gt; Perhaps one of these will work:
gt; gt; B1: =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(COUNT(A1:A2)=2,quot; FORMULA_1quot;,quot;DO
gt; gt; NOTHINGquot;))
gt; gt; or
gt; gt; B1:
gt; gt; =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),quot;FORMULA_1quot;,quot;DO
gt; gt; NOTHINGquot;))
gt; gt;
gt; gt; Does either of those help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Scottquot; wrote:
gt; gt;
gt; gt;gt; I need to do the calculation according to the availability of data as
gt; gt;gt; following example.
gt; gt;gt;
gt; gt;gt; 1. Provision of both cell A1 and A2, perform formula 1.
gt; gt;gt; 2. Provision of all cell A1, A2, A3 amp; A4, perform formula 2.
gt; gt;gt; 3. Other than above condition, do not perform any calculation.
gt; gt;gt;
gt; gt;gt; Can someone advise if I should use IIF or other function to accomplish
gt; gt;gt; the
gt; gt;gt; required result. In addition, what is the maximum length for a cell to
gt; gt;gt; accept the statement?
gt; gt;gt;
gt; gt;gt; Thanks,
gt; gt;gt;
gt; gt;gt; Scott
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

Ron,

Your understanding is quite correct. I forgot one condition. If the data
are available on A1, A2 amp; A3 or A1, A2 amp; A4, the resulting cell remains the
result of formula 1. The four cell figures are come from different sections
at different time.

Scott

quot;Ron Coderrequot; gt; wrote in message
...
gt; If I understand you correctly, then:
gt;
gt; If A1:A4 all have values...then FORMULA_2
gt; Otherwise, if A1:A2 have values....then FORMULA_1
gt; (and it doesn't matter if A3 or A4 have values or not)
gt;
gt; If that's correct then:
gt; B1: =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(COUNT(A1:A2)=2,quot; FORMULA_1quot;,quot;DO
gt; NOTHINGquot;))
gt;
gt;
gt; Did I get it right?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Scottquot; wrote:
gt;
gt;gt; Ron,
gt;gt;
gt;gt; Thanks for your suggestion. Both work correctly to my requirement.
gt;gt; However, there are two minor issues.
gt;gt;
gt;gt; 1. If the available data do not conform to the requirements for
gt;gt; formula 1
gt;gt; or 2, it shows FALSE. I do not need FALSE and only blank the resulting
gt;gt; cell.
gt;gt;
gt;gt; 2. If the data are available on A1, A2 amp; A3 or A1, A2 amp; A4, the
gt;gt; resulting
gt;gt; cell remains the result of formula 1. Is it possible?
gt;gt;
gt;gt; As the formulas are quite long, I am afraid to exceed the permissible
gt;gt; limit
gt;gt; of each cell.
gt;gt; If so, I need to do it in a few cells.
gt;gt;
gt;gt; Scott
gt;gt;
gt;gt; quot;Ron Coderrequot; gt; wrote in message
gt;gt; ...
gt;gt; gt; I'm guessing that cells A1:A4 contain numbers....
gt;gt; gt;
gt;gt; gt; Perhaps one of these will work:
gt;gt; gt; B1: =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(COUNT(A1:A2)=2,quot; FORMULA_1quot;,quot;DO
gt;gt; gt; NOTHINGquot;))
gt;gt; gt; or
gt;gt; gt; B1:
gt;gt; gt; =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),quot;FORMULA_1quot;,quot;DO
gt;gt; gt; NOTHINGquot;))
gt;gt; gt;
gt;gt; gt; Does either of those help?
gt;gt; gt;
gt;gt; gt; ***********
gt;gt; gt; Regards,
gt;gt; gt; Ron
gt;gt; gt;
gt;gt; gt; XL2002, WinXP-Pro
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Scottquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; I need to do the calculation according to the availability of data as
gt;gt; gt;gt; following example.
gt;gt; gt;gt;
gt;gt; gt;gt; 1. Provision of both cell A1 and A2, perform formula 1.
gt;gt; gt;gt; 2. Provision of all cell A1, A2, A3 amp; A4, perform formula 2.
gt;gt; gt;gt; 3. Other than above condition, do not perform any calculation.
gt;gt; gt;gt;
gt;gt; gt;gt; Can someone advise if I should use IIF or other function to accomplish
gt;gt; gt;gt; the
gt;gt; gt;gt; required result. In addition, what is the maximum length for a cell
gt;gt; gt;gt; to
gt;gt; gt;gt; accept the statement?
gt;gt; gt;gt;
gt;gt; gt;gt; Thanks,
gt;gt; gt;gt;
gt;gt; gt;gt; Scott
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
The formula I submitted evaluates as follows:

A1A2A3A4Result
VALUEVALUEVALUEVALUEFormula_2
VALUEVALUEVALUEblankFormula_1
VALUEVALUEblankVALUEFormula_1
VALUEVALUEblankblankFormula_1
ALL OTHER COMBINATIONSNothing

If that is not doing what you want, I'll need a little more guidance.

***********
Regards,
Ron

XL2002, WinXP-Proquot;Scottquot; wrote:

gt; Ron,
gt;
gt; Your understanding is quite correct. I forgot one condition. If the data
gt; are available on A1, A2 amp; A3 or A1, A2 amp; A4, the resulting cell remains the
gt; result of formula 1. The four cell figures are come from different sections
gt; at different time.
gt;
gt; Scott
gt;
gt; quot;Ron Coderrequot; gt; wrote in message
gt; ...
gt; gt; If I understand you correctly, then:
gt; gt;
gt; gt; If A1:A4 all have values...then FORMULA_2
gt; gt; Otherwise, if A1:A2 have values....then FORMULA_1
gt; gt; (and it doesn't matter if A3 or A4 have values or not)
gt; gt;
gt; gt; If that's correct then:
gt; gt; B1: =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(COUNT(A1:A2)=2,quot; FORMULA_1quot;,quot;DO
gt; gt; NOTHINGquot;))
gt; gt;
gt; gt;
gt; gt; Did I get it right?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Scottquot; wrote:
gt; gt;
gt; gt;gt; Ron,
gt; gt;gt;
gt; gt;gt; Thanks for your suggestion. Both work correctly to my requirement.
gt; gt;gt; However, there are two minor issues.
gt; gt;gt;
gt; gt;gt; 1. If the available data do not conform to the requirements for
gt; gt;gt; formula 1
gt; gt;gt; or 2, it shows FALSE. I do not need FALSE and only blank the resulting
gt; gt;gt; cell.
gt; gt;gt;
gt; gt;gt; 2. If the data are available on A1, A2 amp; A3 or A1, A2 amp; A4, the
gt; gt;gt; resulting
gt; gt;gt; cell remains the result of formula 1. Is it possible?
gt; gt;gt;
gt; gt;gt; As the formulas are quite long, I am afraid to exceed the permissible
gt; gt;gt; limit
gt; gt;gt; of each cell.
gt; gt;gt; If so, I need to do it in a few cells.
gt; gt;gt;
gt; gt;gt; Scott
gt; gt;gt;
gt; gt;gt; quot;Ron Coderrequot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; I'm guessing that cells A1:A4 contain numbers....
gt; gt;gt; gt;
gt; gt;gt; gt; Perhaps one of these will work:
gt; gt;gt; gt; B1: =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(COUNT(A1:A2)=2,quot; FORMULA_1quot;,quot;DO
gt; gt;gt; gt; NOTHINGquot;))
gt; gt;gt; gt; or
gt; gt;gt; gt; B1:
gt; gt;gt; gt; =IF(COUNT(A1:A4)=4,quot;FORMULA_2quot;,IF(AND(COUNT(A1:A2) =2,COUNT(A1:A4)=2),quot;FORMULA_1quot;,quot;DO
gt; gt;gt; gt; NOTHINGquot;))
gt; gt;gt; gt;
gt; gt;gt; gt; Does either of those help?
gt; gt;gt; gt;
gt; gt;gt; gt; ***********
gt; gt;gt; gt; Regards,
gt; gt;gt; gt; Ron
gt; gt;gt; gt;
gt; gt;gt; gt; XL2002, WinXP-Pro
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Scottquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; I need to do the calculation according to the availability of data as
gt; gt;gt; gt;gt; following example.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; 1. Provision of both cell A1 and A2, perform formula 1.
gt; gt;gt; gt;gt; 2. Provision of all cell A1, A2, A3 amp; A4, perform formula 2.
gt; gt;gt; gt;gt; 3. Other than above condition, do not perform any calculation.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Can someone advise if I should use IIF or other function to accomplish
gt; gt;gt; gt;gt; the
gt; gt;gt; gt;gt; required result. In addition, what is the maximum length for a cell
gt; gt;gt; gt;gt; to
gt; gt;gt; gt;gt; accept the statement?
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Thanks,
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Scott
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

Assuming you have four variables, arrange your data as shown
DimGot
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.