Help with IF formula

[bggrad95]

I'm a newbie on this board so sorry if I posted this in the wrong area.
I am trying to create a formula at work that will calculate the cost of
an item depending on the quantity purchased. For example, if someone
purchased 1-249 parts it would cost, lets say $2.00, if they purchased
250-999 it would be $1.92, if 1000-4999 it would be $1.82 and lastly
if 5000+ it would be $1.62. Is there any way to do this? I read up on
the IF command but they don't go into that much detail. Any help would
be greatly appreciated. Brian

 

[Peo Sjoblom]

Brian,

you can construct a lookup table with

1 2
250 1.92
1000 1.82
5000 1.62

assume it is in F3:G6,then use

=IF(A1="","",A1*VLOOKUP(A1,F3:G6,2))

or hardcoded

=IF(A1="","",A1*VLOOKUP(A1,{1,2;250,1.92;1000,1.82;5000,1.62},2))


where A1 holds the qty purchased

 

[Norman Harker]

Hi Brian!

Using an IF function approach:

=IF(A1<250,2,IF(A1<1000,1.92,IF(A1<5000,1.82,1.62)))

But if you want to return a blank whilst the quantity purchased cell
is empty you might vary it using:

=IF(A1="","",IF(A1<250,2,IF(A1<1000,1.92,IF(A1<5000,1.82,1.62))))

However, you are getting to the stage where a VLOOKUP function might
be better but in this case you might find it good practice to master
IF function approaches.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.