Help with progressive pricing formula

erodjob

Active Member
Joined
Feb 11, 2003
Messages
253
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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
=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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,497
Members
414,072
Latest member
2020914

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top