MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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