Solved: PRICING GRID

[GaryB]

Hi,

I am trying to create a pricing grid that a customer can enter a quantity and see what the pricing will be. The per each price would be hid. I created the worksheet attached and the problem I have is if, lets say, I put a 199 quantity in the 100+ range the cost is extremely higher that 200 in the 200+ range. Obviously one of the problems per each price remains constant in each range and go lower as the quantity increases. From what I can figure out is I need to incorporate a sliding scale to have this accurately price and I am at a compete loss on how to do this. I have attached an Excel sheet so you can see what I have done and hopefully someone can steer me in the right direction. Any help will be appreciated.

Thanks

Gary

[GTO]

Greetings Gary,

I may be misunderstanding, but it sounds to me that you want the user to be able to enter a quantity in just one cell and have the correct total return. There may well be better, but I think this works:

Let us say that the quantity desired is entered into B3

[VBA]
=ROUND(INDEX(C1236,MATCH(FLOOR(B3,100),C12:C36,0),2)*B3,2)
[/VBA]
Hope that helps,

Mark

[parttime_guy]

Hi GaryB

Try the amended sheet
Hope it helps

Best regards

[CatDaddy]

do you want a sliding scale at 100, 200, 300 etc or do you want a sliding scale for 1,2,3,4,5,6,7...etc? what i mean is are you trying to incorporate a price that changes between the benchmarks you set or just using those?

[CatDaddy]

using the modified worksheet(2):
[VBA]Sub Pricer()
Dim amount As Integer, bracket As Integer
Dim price As Double, total As Double
Dim cell As Range
ActiveWorkbook.Sheets(2).Activate
amount = InputBox("Amount to Order?")
For Each cell In Range("A12:A37")
If amount > cell.Value And amount < cell.Offset(1, 0) Then
bracket = cell.Value
price = cell.Offset(0, 1).Value
total = price * amount
End If
Next cell
Range("G13").Value = bracket
Range("H13").Value = price
Range("I13").Value = amount
Range("J13").Value = total


End Sub[/VBA]

[GaryB]

Wow! What a great bunch of people!!!!! GTO I used your formula and it worked great. CatDaddy I didn't see your code until I had already finished the project, but, to everyone who responded: Thank You Thank You Thank You.