I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:
Sheet 1
A B C D E F G
1 Pack Qty Qty 1 Prc 1 Qty 2 Prc 2 Qty 3 Prc 3
2 100 1 0.9 100 0.74 400 0.6
Sheet 2
A B C D E F G
1 Pack Qty Qty 1 Prc 1 Qty 2 Prc 2 Qty 3 Prc 3
2 100 1 0.9 100 0.74 400 0.6
Sheet 1 formulas
Each Price Field Rules
If Quantity (one cell left) > than 24,999 = Corresponding price field (sheet 2)
If Quantity (one cell left) > 10,000 and < 25,000 = Corrisponding price field (sheet 2) *.9
If Quantity (one cell left) > 5,000 and < 10,000 = Corrisponding price field (sheet 2) *.85
If Quantity (one cell left) > 2,500 and < 5,000 = Corrisponding price field (sheet 2) *.8
If Quantity (one cell left) > 1,00 and < 2,500 = Corrisponding price field (sheet 2) *.75
If Quantity (one cell left) < or equal to package quantity = Corrisponding price field (sheet 2) *.70
Thanks!
Can I do a lookup that will first search for Item, then date (nearest date) and return qty?
Item Date Qty
xyz 3-Jan 452
xyz 7-Jan 225
zzy 1-Jan 120
abc 6-Jan 352
So if Item = xyz, and date = 4-Jan, then 452 should be displayed.
Thanks in advance,
Assume Product is Part A and Qty is 4 for the following fields. What is the formula I need to enter to return the correct price for the given quantity?
(I expect the formula to output 25.87)
Field values are as follows:
A1:Product B1:Price C1:Qty Start D1: Qty End
A2:PartA B2:10.35 C2:50 D2:500
A3:PartA B3:12.94 C3:25 D3:49
A4:PartA B4:12.94 C4:10 D4:24
A5:PartA B5:25.87 C5:1 D5:9
A6:PartA B6:10.35 C6:0 D6:0
A7:PartB B7:8.34 C7:50 D7:500
A8:PartB B8:10.43 C8:25 D8:49
A9:PartB B9:10.43 C9:10 D9:24
A10:PartB B10:20.86 C10:1 D10:9
A11:PartB B11:8.34 C11:0 D11:0
Additional Details
I tried a previous solution which was similar, except Quantity was 10 (which is in the Qty Start List). This question will need the answerer to allow for the fact that the quantity is BETWEEN QTY START and QTY END columns (in this case 1 and 9) and not exactly any number in the Qty start or Qty End list)
The previous correct solution was as follows:
=SUMPRODUCT((A2:A11=A2) * (C2:C11=1) * B2:B11)
HI
I have a spreadsheet of data containing customer orders
The data is laid out with an Item Name and the Quantity ordered.
I want to see a total ordered for each item for instance
CustomerA Apples 10
CustomerB Oranges 10
CustomerA Apples 5
CustomerB oranges 5
I want to see in the pivot table
Customer A - Apples 15 - Oranges 15
Customer B - Apples 15 - Oranges 15
I cant see how to do this, I can easily make a count of the item names, but that only tells me how many orders contain an item not the total number of items ordered.
Any ideas?
Thanks
Sam
I want to be able to calculate a price based on a given quantity. But here is the trick.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 36 widgets, then the cost is 36 x $1.00.
If item A is ordered in quantities less than "12" than there is a 15% upcharge.
Example: Widget comes in cases of 12 at $1.00 per case. If the customer orders 4 widgets then the price is 4 x $1.15
But, if the customer orders 35. Then the first 24 are calculated at 24 x $1.00 and the remaining 11 are calculated at 11 x $1.15.
How do I make this formula work?
Okay,
Forgive me ahead of time, I really don't know much about Excel. I can work with templates and create simple formulas but alot of what I read here comes across as "Excelspeak" to me.
Here is what I want to do:
I have a price list sheet created. This price list has cells labled by name and then "duration", basically for every item there are four prices, based on duration of a rental or sale. I want to create a quote program that will pull data from the price list based on the quantities entered in the quote program.
For example
Price list is "System A, description, $1, $7, $14, $100"
Each of these items divided by commas is a different cell so my plan is to basically duplicate the price list on another sheet with no prices displayed but when I ented a number into the respective column for each duration the program pulls the base data and multiplies it by the number entered and only displays this number. Then of course there are sums and sales taxes etc but I think I can do that already.
Sorry, I know I'm not decribing this accurately but I hope you get the gist of what I am trying to do. I have tried to get "help" within Excel but, frankly I just don;t follow most of what is said.
Basically what I am trying to do is this: =('Price List'!$F$4)*F4
I know this is circular....I just don't know what to do about it.
Help
Hi I'm very new to this & hoping you can help ? I have supplier price lists which have 1310 rows of stocklines & 4 columns. Column A is Part numbers, Column B is part description, & column C is list price exclusive of gst & column D is price including gst.
How do we add a prefix to to each part no. in each cell from A1 thru to A1310 eg. pool0001,pool0002,pool0003.
How do I in column c apply a trade discount to each cell individually eg -62% for c1 thru to c1310 to the end results is my net cost per part no.
thanks hope you can help me.
Mark
I have a worksheet called Benefit data, with the age of employees in Col N and their years of service in Col P starting at row 24.
I have another worksheet called matrix, with the years of service from 10 to 42 in col A, and the age in row 1 from 45 to 72. See small sample below. Is there a formula that I could put into EB24 and copy down that could find the corresponding match in the matrix?
For example, if N24 is 49 and P24 is 20, EB24 should return a value of .41. Can this be done oh mighty gurus of the Excel world?
Matrix
A
B
C
D
E
F
G
H
I
1
YOS/Age
45
46
47
48
49
50
51
52
2
10
0.32
0.33
0.34
0.34
0.35
0.35
0.36
0.36
3
11
0.33
0.34
0.34
0.35
0.35
0.36
0.36
0.37
4
12
0.34
0.34
0.35
0.35
0.36
0.36
0.37
0.38
5
13
0.34
0.35
0.35
0.36
0.36
0.37
0.38
0.38
6
14
0.35
0.35
0.36
0.36
0.37
0.38
0.38
0.39
7
15
0.35
0.36
0.36
0.37
0.38
0.38
0.39
0.39
8
16
0.36
0.36
0.37
0.38
0.38
0.39
0.39
0.4
9
17
0.36
0.37
0.38
0.38
0.39
0.39
0.4
0.41
10
18
0.37
0.38
0.38
0.39
0.39
0.4
0.41
0.41
11
19
0.38
0.38
0.39
0.39
0.4
0.41
0.41
0.42
12
20
0.38
0.39
0.39
0.4
0.41
0.41
0.42
0.42
13
21
0.39
0.39
0.4
0.41
0.41
0.42
0.42
0.43
14
22
0.39
0.4
0.41
0.41
0.42
0.42
0.43
0.44
15
23
0.4
0.41
0.41
0.42
0.42
0.43
0.44
0.44
16
24
0.41
0.41
0.42
0.42
0.43
0.44
0.44
0.45
17
25
0.41
0.42
0.42
0.43
0.44
0.44
0.45
0.45
18
26
0.42
0.42
0.43
0.44
0.44
0.45
0.45
0.46
19
27
0.42
0.43
0.44
0.44
0.45
0.45
0.46
0.46
20
28
0.43
0.44
0.44
0.45
0.45
0.46
0.46
0.47
21
29
0.44
0.44
0.45
0.45
0.46
0.46
0.47
0.48
22
30
0.44
0.45
0.45
0.46
0.46
0.47
0.48
0.48
23
31
0.45
0.45
0.46
0.46
0.47
0.48
0.48
0.49
Excel 2003
VLOOKUP() function in Excel.