Help with progressive pricing formula

[erodjob]

Greeting to all. I am using excel 2007 and I need help with a formula please.

I have tried a vlookup, but I think there has to be something better.

I have columns D2-D250 with various quantities then I have column F2-F250 blank. In column F2-F250 i want to populate pricing that corresponds to the quantities in D2-D250 based on the following guide lines.

If quantity in column D is between 1-10 the price that should populate in column F is 7.50.
If quantity in column D is between 11-100 the price that should populate in column F is 15.00.
If quantity in column D is 101 or greather the price that should populate in column F is 75.00.

So if D2 is 50, F2 should display a price of 15.00.
If D3 is is 102, F3 should display a price of 75.00 and so on.

help would be greatly appreciated.

 

[Richard Schollar]

Hi

=VLOOKUP(D2,{1,7.5;11,15;101,75},2,TRUE)

will return those values - note that if you have a value below 1 an error will be returned.

 

[sheetspread]

=IF(AND(D2>=1,D2<=10),7.5,IF(AND(D2>=11,D2<=100),15,IF(D2>=101,75))) if there are only those conditions.

Otherwise sumproduct or a max/min formula

 

[AhoyNC]

This should also work if you only have the 3 conditions.

<TABLE style="WIDTH: 167pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=222><COLGROUP><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 8118" width=222><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 167pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=222><TABLE style="WIDTH: 189pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=252><COLGROUP><COL style="WIDTH: 189pt; mso-width-source: userset; mso-width-alt: 9216" width=252><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 189pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=252>=IF(D2<=10,7.5,IF(D2<=100,15,75))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[erodjob]

Thanks folks they all worked.