Placing Unit Price into Pricing Brackets - Use IFSTATEMENTS?

Hello

I would like a formula which will display certain price bands depending on what the value of a cell is.

I have column A titled "Average Unit Price" displaying the average unit price.

Column B is titled "Price band category", and depending on what value is in Column A, i would like column B to display the following price bands -

$0 - $99
$100 - $299
$300 - $499
$500+.

I have not achieved much despite spending some time on this and i am a little puzzled as to the best function to actually use?

I can only work out a small part of the formula which will only display the first price band category using a basic IF Statement as follows:

=IF(A1<=99,"$0 - $99","")

The above formula displays $0 - $99 if the average unit price in Column A is between the two numbers which is what i want, but how would i add the further price bands? every time I attempt this Excel shouts at me saying there are too many arguments.

Can anyone please offer assistance? what would be the best function to use? How can i add further arguments to an IF statement without making Excel angry? is it possible for 4 different outcomes to happen using IF statements?

Any help is massively appreciated.

Regards

Lewis

VLOOKUP is your friend - see if the attached does what you want. The table used for the VLOOKUP query could be hidden or placed on a separate sheet.

Etaf and Martindwilson, both your formulas work 100% the way i want them too, thank you so much for your help.

Thank you AliGW for your assistance, its not exactly what i was looking for but you have provided a different point of view and i am very grateful for your input. It looks like I have some learning to do!

Done,

Sorry to be a pain but I just want to get this right in my head, in your formula have you made Excel look at the next argument if the previous statement was false? so it checks the cell and =if(A1>=500, it will display the price band "$500+", if not it goes to the next argument =IF( A1>=300 , "$300 - $499" and so on?

I hope that makes sense?

brilliant,

I forgot to mention on my last post that if all possibilities have been checked then "$0 - $99" will be displayed, so the fact you have said that backs up what i was thinking! very much appreciate the confirmation, Excel can be a right madam when she wants to be.

Thanks for your efforts everyone