Price Calculation

jedilefty

Board Regular
Joined
Nov 14, 2017
Messages
55
Office Version
  1. 365
Platform
  1. Windows
Good morning. Can somebody please help me with a calculation? I have created a price sheet and would like the Estimated Price column (M) to reflect the proper quoted price based on the total qty on order (cell L19)? The quoted price is cells A1:B3.

Any help would be appreciated!

Capture.PNG
 

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.
what the price for 1-19 pieces ??
if i'm reading correctly

then a lookuo
if you change the PCS to just a number

0
20
30
50
then you can use
index(B1:B3,match(l19,A1:A3,1))
multiply by the QTY
L19*index(B1:B3,match(l19,A1:A3,1))
 
Upvote 0
So the price breakdown goes as follows:

1-20 = $26
21-49 = $24
50+ = $22

I tried using your suggested formula and it didn't work out.
 
Upvote 0
oh OK
then change the list to

1 =26
21 = 24
50 = 22

heres just an example of it picking the correct value

=INDEX($B$1:$B$3,MATCH(L19,$A$1:$A$3,1))

so then * L19

=L19*INDEX($B$1:$B$3,MATCH(L19,$A$1:$A$3,1))

Book1
ABCDEFGHIJKLMN
11261-20 = $26
2212421-49 = $24
3502250+ = $22
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19126
20526
21926
221326
231726
242124
252524
262924
273324
283724
294124
304524
314924
325322
335722
Sheet1
Cell Formulas
RangeFormula
M19:M33M19=INDEX($B$1:$B$3,MATCH(L19,$A$1:$A$3,1))
 
Upvote 0
Solution
Thank you!! This really helped out!
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,136
Members
453,642
Latest member
jefals

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
Back
Top