Skip to main content ↑ ↓
Topic: looking for Excel knowlege (Read 759 times) previous topic - next topic

looking for Excel knowlege

I am trying to build a formula but think I am using the wrong function.
I am tinkering with IF, SUM or SUMIF functions only..... that's not working so I think.

here is what I want to do working with two cells only.

if some enters the number 200 in cell B21, I want cell F21 to perform a calculation and display a numeric $ value.
if some enters the number 400 in cell B21, I want cell F21 to perform a different calculation and display a numeric $ value.
if some enters the number 600 in cell B21, I want cell F21 to perform again a different calculation and display a numeric $ value.

and so on..............

here is the formula I have now which is not working
=IF(B21="0:200",B21*67), IF(B21="201:400",B21*38), IF(B21="401:600",B21*27), IF(B21="601:800",B21*23), IF(B21="801:1000",B21*24), IF(B21="1001:1200",B21*22), IF(B21="1201:2000",B21*27)))))))

the result would be:
when my sales guy enters a 400A automatic transfer switch such as "400" in cell B21, cell F21 automatically populates  $15,200.00
 

looking for Excel knowlege

Reply #1 –
got it,,
=B21*
  LOOKUP(
    B21,
    {0,201,401,601,801,1001,1201},
    {67,38,27,23,24,22,27}*C21)