Help with formula
Posted by Ron M. on January 23, 2002 9:32 PM
I total cells A1:D1 which are formated for Fraction up to 2 digits and if
Total is 0-72 display in cell E1 6
Total is 72-144 display in cell E1 12
Total is 144-216 display in cell E1 18
Total is 216-288 display in cell E1 24
Total is 288-360 display in cell E1 30
Total is 360-432 display in cell E1 36
Then multiply E1 x F1
Total displayed in cell G1
Posted by Tom Dickinson on January 23, 2002 11:52 PM
In cell E1:
=If(or(Sum(A1:D1)<0,Sum(A1:D1)>432),"",INT(C2/72) + IF(MOD(C2,72)>0,1,0))
In cell G1
=F1 * G1
Posted by Tom Dickinson on January 23, 2002 11:55 PM
Forgot the factor; try this instead
Posted by Carl B on January 24, 2002 12:23 AM
Could be an easier way, it looks to me as if a nested IF statement would work.
In cell E1 IF(SUM(A1:D1)<72.01,6,IF(SUM(A1:D1)<144.01,12,IF(SUM(A1:D1)<216.01,18,IF(SUM(A1:D1)<288.01,24,IF(SUM(A1:D1)<360.01,30,36)))))
In cell G1 SUM(E1*F1)
Posted by Aladin Akyurek on January 24, 2002 5:38 AM
In E1 enter: =IF(COUNT(A1:D1),VLOOKUP(SUM(A1:D1),{0,6;74,12;144,18;216,24;288,30;360,36},2),0)
If there is nothing to sum in A1:D1, you get 0 as result. And, if the sum of A1:D1 is greater than 432, you'll get 36 as result.
In G1 enter: =E1*F1
============ I total cells A1:D1 which are formated for Fraction up to 2 digits and if
