functions
Posted by matt on November 27, 2000 10:48 AM
I need to create a mulitple function but nothing i do seems to work can anyone helo with this?
If total is less than 16 824 enter zero
If total is less than 33648 then subtact 16824 from total
if total is great than 33648 then divide total by 2
if total is greater than 168 240 then subtract 84120 from total
the total is (B20+H29+B38+B45+B52+B59)
THANK YOU
Posted by Aladin Akyurek on November 27, 2000 2:03 PM
Name the cell where the total is calculated Total and put your 16 824 in some cell and name it 'bval' (which appears to be your base value, whence bval), then use the formula:
=IF(Total<bval,0,IF(Total<2*bval,Total-bval,IF(AND(Total>2*bval,Total<10*bval),Total/2,IF(Total>10*bval,Total-5*bval,""))))
Aladin
Posted by Aladin Akyurek on November 27, 2000 2:37 PM
In my previous post, the formula was crippled during paste. Here is the correct one:
=IF(Total<bval,0,IF(Total<2*bval,Total-bval,IF(AND(Total>2*bval,Total<10*bval),Total/2,IF(Total>10*bval,Total-5*bval,""))))
See for "Total" and "bval" the previous post.
Aladin
Posted by Aladin Akyurek on November 27, 2000 2:51 PM
Things are messed up in my earlier attempts to post. It seems Copy and Paste doesn't work!
What I wanted to say is:
Name the cell you calculate the total "Total", put the number 16824 in a cell and name "Bval" (which appears to be your base value).
Then use the formula:
=IF(Total<Bval,0,IF(Total<2*Bval,Total-Bval,IF(AND(Total>2*Bval,Total<10*Bval),Total/2,IF(Total>10*Bval,Total-5*Bval,""))))
Cheers.
Aladin
Posted by Aladin Akyurel on November 27, 2000 2:55 PM
I give it up. It seems impossible to type the formula!
Posted by Baki on December 01, 2000 7:39 PM
Here another alternative:
=+IF(Total>168240,Total-84120,IF(Total>33648,Total/2,IF(Total>16824,Total-16824,0)))
good luck
