Criteria Calculation

[raymondsum]

I want to sum up certain rows base on a criteria. eg

Col A Col B Col C Col D Col E
(5%)
1 ABC $12000 $600 $12000*5%
2 ABC $1300 $65
3 ABC $4000 $17200 $200 $865
---------

4 DEF $19000 $884 $19000*1000/21500

5 DEF $2500 $21500 $116 $1000
--------

Col C was calculated by formula =if(A1<>A2,sumif(A$1:A1,A1,B$1:B1)"")

The max value in Col E, it should be $1000 only. If the value of Col C
* 5% was greater than $1000, max $1000. Then $1000 should be
proportional to Col B value.

How to write the formula in col D and Col E in form of VB?

Regards

Raymond

 

[Tom Ogilvy]

You say nothing about what should be in Column D and what you say about
column E isn't clear.

Max of 1000 seems clear but proportional to B????

 

[raymondsum]

Dear Tom,

Sorry! I elborate the case more details

Case 1
Col A Col B Col C remarks
(Salary) (5%
deduction)
1 ABC 12000 600 12000*5%
2 ABC 1300 65 1300*5%
3 ABC 4000 200 4000*5%
total 17300 865 17300*5%

Case 2
Col A Col B Col C Col D Col E
4 DEF 12000 ??? (Cell E4)
5 DEF 15000 27000 1000 ??? (Cell E5)

as deduction 27000*5% is greater than 1000, therefore
the max is 1000(Cell D5) . so the deduction may be in proportional to
salary (Col E) ,so 12000*1000/27000 = 444.44
15000 *1000/27000 = 555.56

The solution of Case 1 is ok, thanks
How about Case 2?

the following is my suggestion but it is too clumsy
I assume there are only two rows, if there are more row to consider, I
use more"IF" function, but the maximum is 7 times.

in the Cell(E4)
=if(iserror(+b4*d4/c4>0)=true,if(iserror(+b4*d5/c5>0)=false,+b4*d5/c5,""),+d4*d4/c4)

Is any good suggestion?

Regards

Raymond