Sumif
Posted by Lars on November 02, 2001 2:39 PM
A B
Jan 150,000
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
each month I will add a number - I want to create a formula that will add the numbers in column b and multiply it by 1.5 until it reaches 1,000,000. Then when this number exceeds 1,000,000 multiply the excess over 1,000,000 by 1.65 and add that to what the product was from 1 - 1,000,000. Then when the total becomes greater than 1,400,000 multiply that excess over 1.4M by 1.8 and add that.
summarize 1-1M * 1.5
1.1M - 1.4M * 1.65
1.41M - and greater * 1.8
I need a miracle!
Posted by Ken on November 02, 2001 8:08 PM
IF January starts in A1 the first input number in cell B1 then cell C1 would have:
=IF(B1>1410000,B1*1.8,IF(B1>1100000,B1*1.65,IF(B1>0,B1*1.5,"")))
C2:
=IF(B1+B2>1410000,B2*1.8,IF(B1+B2>1100000,B2*1.65,IF(B1+B2>0,B2*1.5,"")))
C3:
=IF(SUM(B1:B3)>1410000,B3*1.8,IF(SUM(B1:B3)>1100000,B3*1.65,IF(SUM(B1:B3)>0,B3*1.5,"")))
C4:
=IF(SUM(B1:B4)>1410000,B4*1.8,IF(SUM(B1:B4)>1100000,B4*1.65,IF(SUM(B1:B4)>0,B4*1.5,"")))
C5:
=IF(SUM(B1:B5)>1410000,B5*1.8,IF(SUM(B1:B5)>1100000,B5*1.65,IF(SUM(B1:B5)>0,B5*1.5,"")))
C6:
=IF(SUM(B1:B6)>1410000,B6*1.8,IF(SUM(B1:B6)>1100000,B6*1.65,IF(SUM(B1:B6)>0,B6*1.5,"")))
C7:
=IF(SUM(B1:B7)>1410000,B7*1.8,IF(SUM(B1:B7)>1100000,B7*1.65,IF(SUM(B1:B7)>0,B7*1.5,"")))
C8:
=IF(SUM(B1:B8)>1410000,B8*1.8,IF(SUM(B1:B8)>1100000,B8*1.65,IF(SUM(B1:B8)>0,B8*1.5,"")))
C9:
=IF(SUM(B1:B9)>1410000,B9*1.8,IF(SUM(B1:B9)>1100000,B9*1.65,IF(SUM(B1:B9)>0,B9*1.5,"")))
C10:
=IF(SUM(B1:B10)>1410000,B10*1.8,IF(SUM(B1:B10)>1100000,B10*1.65,IF(SUM(B1:B10)>0,B10*1.5,"")))
C11:
=IF(SUM(B1:B11)>1410000,B11*1.8,IF(SUM(B1:B11)>1100000,B11*1.65,IF(SUM(B1:B11)>0,B11*1.5,"")))
C12:
=IF(SUM(B1:B12)>1410000,B12*1.8,IF(SUM(B1:B12)>1100000,B12*1.65,IF(SUM(B1:B12)>0,B12*1.5,"")))
Thanks
Ken
Posted by Osvaldo Ardiles on November 03, 2001 1:13 AM
I haven't checked whether or not the formula works, but assuming it does, it is not necessary to have 12 different formulas. Just put the following in C1 and drag it down :-
=IF(SUM($B$1:B1)>1410000,B1*1.8,IF(SUM($B$1:B1)>1100000,B1*1.65,IF(SUM($B$1:B1)>0,B1*1.5,"")))
Posted by Aladin Akyurek on November 03, 2001 7:42 AM
Lars --
What should be the result(s) if we have the following entries?
{"Jan",150000;
"Feb",200000;
"Mar",650000;
"Apr",100000;
"May",350000;
"Jun","";
"Jul","";
"Aug","";
"Sep","";
"Oct","";
"Nov","";
"Dec",""}
Aladin
===========
