MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Aladin - Follow up from 11/2 on sumif


Posted by Lars on November 05, 2001 10:30 AM

the result would be for Jan - Mar the total of 1,000,000 times 1.4 and and Apr - May the total of 450K times 1.65


Posted by Aladin Akyurek on November 05, 2001 11:32 AM

> the result would be for Jan - Mar the total of 1,000,000 times 1.4 and and Apr - May the total of 450K times 1.65

This gives an end-result of 2,142,500.00. I see you have changed a multiplier (1.5 is now 1.4) and a rule: when "1.1M-1.4M * 1.65".
My end result is: 2,250,000.00 (see below).

Here is what I've got on the test sample that I suggested:

{"Jan",150000,150000,225000;
"Feb",200000,350000,525000;
"Mar",650000,1000000,1500000;
"Apr",100000,1100000,1665000;
"May",350000,1450000,2250000;
"Jun","",1450000,2250000;
"Jul","",1450000,2250000;
"Aug","",1450000,2250000;
"Sep","",1450000,2250000;
"Oct","",1450000,2250000;
"Nov","",1450000,2250000;
"Dec","",1450000,2250000}

The second column houses the entries of the test sample.
The third column houses the running total.
The fourth column is the result of computations according to the rules you previously specified.

I thought you are only interested in the value in D that corresponds to May, the last entry. Is this what you are looking for?

Aladin


Posted by Lars on November 05, 2001 12:04 PM

Woops it is 1.5 not 1.4, looks good can you send me the formula or explain how you got this. Let me put it to the test and see what happens

Thanks
Lars


Posted by Aladin Akyurek on November 05, 2001 12:48 PM

Formulas

Assuming that Jan is A1 and the corresponding amount if any in B1,

in C1 enter: =IF(LEN(B1),SUM($B$1:B1),"")

Copy down this till the row of Dec.

In D1 enter: =IF(LEN(C1),((C1 <= 1000000)*(C1*1.5)+(C1>1000000)*(1000000*1.5)+(C1-1000000>0)*((C1>1000000)*((C1>1400000)*(400000*1.65)+((C1 < 1400000)*(C1-1000000)*1.65)))+(C1>1400000)*(C1-1400000)*1.8),"")

Copy down this too till the row of Dec.

The last formula makes use of Boolean terms. I didn't seek to simplify it.

If you'd like to have a copy of the workbook containing all this, just drop me a line (I didn't see your e-mail address, whence the proposal).

Aladin

=======

: My end result is: 2,250,000.00 (see below). : "Feb",200000,350000,525000; : "Mar",650000,1000000,1500000; : "Apr",100000,1100000,1665000; : "May",350000,1450000,2250000; : "Jun","",1450000,2250000; : "Jul","",1450000,2250000; : "Aug","",1450000,2250000; : "Sep","",1450000,2250000; : "Oct","",1450000,2250000; : "Nov","",1450000,2250000; : "Dec","",1450000,2250000} : The third column houses the running total. : The fourth column is the result of computations according to the rules you previously specified.


Posted by Lars on November 05, 2001 12:51 PM

Re: Formulas

Thanks Aladin my email address is Lromeis@princesscruises.com

Thanks again

: Lars