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

 

[gaz_chops]

Welcome to the forum.

Try

=$A$2*(LOOKUP($A$2,{0,200,400},{5,10,15}))

Copy across.

 

[Aria Bima]

Welcome to the forum.

Try

=$A$2*(LOOKUP($A$2,{0,200,400},{5,10,15}))

Copy across.

Hi,


thanks, however the result was not correct.

when i try the formula, then the result was

[TABLE="width: 266"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Total
[/TD]
[TD]<200 [/TD]
[TD]200>x>400 [/TD]
[TD]<400[/TD]
[/TR]
[TR]
[TD="align: right"]1000
[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]15000[/TD]
[/TR]
</tbody>[/TABLE]

is there other formula to solve this problem?

tq

 

[gaz_chops]

I don't understand the logic?

So what ever is entered in A2 it will always return either
200 x $ 5 = $1000
200 x $ 10 = $2000
600 x $ 15 = $9000

 

[RCBricker]

I wrote this two ways...

one the formula does exactly the same in each column.

=IF(AND($A2>200,$A2<400),SUM(200*10),IF($A2>400,SUM(600*15),SUM(200*5)))

this set of codes follows the logic for each column

insert in B2

=IF($A4>200,0,SUM(200*5))

insert in C2

=IF(OR($A4<200,$A4>400),0,SUM(200*10))

insert in D2

=IF($A4>400,SUM(600*15),0)

 

[RCBricker]

here it is following the logic but all in one formula

=IF(COLUMN()=2,IF($A4>200,0,SUM(200*5)),IF(COLUMN()=3,IF(OR($A4<200,$A4>400),0,SUM(200*10)),IF($A4>400,SUM(600*15),0)))

 

[gaz_chops]

Unless you explain more than similar to above

B2
=IF(A2<200,1000,"")
C2
=IF(AND(A2>199,A2<400),2000,"")
D2
=IF(A2>=400,9000,"")

 

[Aria Bima]

I don't understand the logic?

So what ever is entered in A2 it will always return either
200 x $ 5 = $1000
200 x $ 10 = $2000
600 x $ 15 = $9000

The number that will change will be the 200, 200 and 600.

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

 

[chris_t]

in these formulas i assumed you wanted to multiply the value in A2 by your multiplier ($5,$10,$15) insert these into your spreadsheet and drag down then what ever is entered into A column will be calculated on that row by the formulas. note these will result in FALSE and display false on the spreadsheet if the condition is not met this can be simply fixed in the if statement.

in cell b2
=IF(A2<=200,PRODUCT(A2*5))

in cell c2
=IF(AND(IF(A2>200,TRUE),IF(A2<400,TRUE)),PRODUCT(A2*10))

in cell d2
=IF(A2>400,PRODUCT(A2*15))

 

[RCBricker]

formula accounts for greater than or equal to and less than or equal to

=IF(COLUMN()=2,IF($A4<=200,SUM($A4*5),0),IF(COLUMN()=3,IF(AND($A4>=200,$A4<=400),SUM($A4*10),0),IF($A4>=400,SUM($A4*15),0)))

rich