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
got it,,
=B21*
LOOKUP(
B21,
{0,201,401,601,801,1001,1201},
{67,38,27,23,24,22,27}*C21)