Help Me on Excel Formula

[Aria Bima]

Hi,


please help me on how to solve this excel formula.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total[/TD]
[TD]<200 ($ 5)[/TD]
[TD]>200 < 400 ($ 10)[/TD]
[TD]> 400 ($ 15)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]1000[/TD]
[TD] ???[/TD]
[TD]????[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]

i want to fill the number on column B2,C2 and D2 with some scenario.

B2 = if the number on column A2 > 0 and < 200 then the column on B2 result will be 200 x $ 5 = $1000
C2 = if the number on column A2 > 200 and < 400 then the column on C2 result will be 200 x $ 10 = $2000
D2 = if the number on column A2 >400 then the result on D2 will be 600 x $ 15 = $9000


did any one can help me and resolve this problem? tq

 

[Aria Bima]

the formula result was not fit with my expectation.


here are my further explanation with example, i hope i can explain more for you.
example : if the column A2 change into 400 then the number will be :
200 x $ 5 = $1000
200 x $ 10 = $2000
0 x x $ 15 = $ 0


or if i change the number in column A2 into 100, the the figure will be :
100 x $ 5 = $ 500
0 x $ 10 = $ 0
0 x $ 15 = $ 0

 

[gaz_chops]

Try

B2
=MIN(200,$A2)*5


C2
=MAX(0,MIN(200,$A2-200)*10)


D2
=MAX(0,MIN(400,$A2-400)*15)

 

[Aria Bima]

Try

B2
=MIN(200,$A2)*5


C2
=MAX(0,MIN(200,$A2-200)*10)


D2
=MAX(0,MIN(400,$A2-400)*15)

Hi thanks for your help, however the last formula on D2, doesnt work correctly since the result of that calculation was $6000 if the column A1 was 1000.
the correct result should be $9000 (1000-400=600 x $15).

 

[gaz_chops]

Change the last one to -

=MAX(0,$A2-400)*15