Cost Pricing Formula

[bobzy20]

Hi

I’m looking for a formula that can be used with my costing table tab to produce the correct prices in the sales tab when different quantities are added based on their prices.

I have an example excel sheet but don’t know how to add it?

Thanks

Bob

 

[Saurabhj]

Hi, use XL2BB tool to share sheet data.

XL2BB

 

[bobzy20]

This is my Sales Tab > Costing tab below.

When entering a higher quantity, i would like to unit prices to change based on the costing tab format.

Examples below
5x 501 with a quantity of 2 should show a unit price of £24.30
501 with a quantity of 1 should show a unit price of £6.99
501 with a quantity of 2 should show a price of £6.29

Test.xlsx
	A	B	C	D
1	Description	Quantity	Unit	Total
2	5x 501	1	£27.00	£27.00
3	10x 501	1	£40.00	£40.00
4	20x 501	1	£70.00	£70.00
5	30x 501	1	£90.00	£90.00
6	50x 501	1	£150.00	£150.00
7
8	5x 503	1	£27.00	£27.00
9	10x 503	1	£40.00	£40.00
10	20x 503	1	£70.00	£70.00
11	30x 503	1	£90.00	£90.00
12	50x 503	1	£150.00	£150.00
13
14	5x 507	1	£27.00	£27.00
15	10x 507	1	£40.00	£40.00
16	20x 507	1	£70.00	£70.00
17	30x 507	1	£90.00	£90.00
18	50x 507	1	£150.00	£150.00
19
20	501	1	£6.99	£6.99
21	501	2	£6.99	£13.98
22	501	3	£6.99	£20.97
23	501	4	£6.99	£27.96
Sales

Cell Formulas
Range	Formula
D20:D23,D14:D18,D8:D12,D2:D6	D2	=SUM(C2*B2)

Costing Tab Example - Reduced as it was too long but not needed to get formula.

Test.xlsx
	A	B	C	D
1	Description	Qty	Unit	Total
2	5x 501	1	£27.00	£27.00
3	5x 501	2	£24.30	£48.60
4	5x 501	3	£22.95	£68.85
5	5x 501	4	£21.60	£86.40
6
7	5x 503	1	£27.00	£27.00
8	5x 503	2	£24.30	£48.60
9	5x 503	3	£22.95	£68.85
10	5x 503	4	£21.60	£86.40
11
12	5x 507	1	£27.00	£27.00
13	5x 507	2	£24.30	£48.60
14	5x 507	3	£22.95	£68.85
15	5x 507	4	£21.60	£86.40
16
17	501	1	£6.99	£6.99
18	501	2	£6.29	£12.58
19	501	3	£5.94	£17.82
20	501	4	£5.59	£22.36
21
22	503	1	£6.99	£6.99
23	503	2	£6.29	£12.58
24	503	3	£5.94	£17.82
25	503	4	£5.59	£22.36
26
27	507	1	£6.99	£6.99
28	507	2	£6.29	£12.58
29	507	3	£5.94	£17.82
30	507	4	£5.59	£22.36
Costing Table (Sent)

Cell Formulas
Range	Formula
D27:D30,D22:D25,D17:D20,D12:D15,D7:D10,D2:D5	D2	=SUM(C2*B2)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[bobzy20]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

2010 - Also updated.

Thanks

 

[Fluff]

Thanks for that, how about

=INDEX('Costing Table (sent)'!$C$2:$C$30,MATCH(A2&"|"&B2,'Costing Table (sent)'!$A$2:$A$30&"|"&'Costing Table (sent)'!$B$2:$B$30,0))

This is an array formula & needs to be confirmed with Ctrl Shift Enter, not just Enter.

 

[bobzy20]

Thanks for that, how about

=INDEX('Costing Table (sent)'!$C$2:$C$30,MATCH(A2&"|"&B2,'Costing Table (sent)'!$A$2:$A$30&"|"&'Costing Table (sent)'!$B$2:$B$30,0))

This is an array formula & needs to be confirmed with Ctrl Shift Enter, not just Enter.

Hi

Thanks very much. It works for the first row but doesn't work on the rest.

Test.xlsx
	A	B	C	D
1	Description	Quantity	Unit	Total
2	5x 501	10	£21.60	£216.00
3	10x 501	1	#N/A	#N/A
4	20x 501	1	#N/A	#N/A
5	30x 501	1	#N/A	#N/A
6	50x 501	1	#N/A	#N/A
7
8	5x 503	1	£27.00	£27.00
9	10x 503	1	#N/A	#N/A
10	20x 503	1	#N/A	#N/A
11	30x 503	1	#N/A	#N/A
12	50x 503	1	#N/A	#N/A
13
14	5x 507	1	£27.00	£27.00
15	10x 507	1	#N/A	#N/A
16	20x 507	1	#N/A	#N/A
17	30x 507	1	#N/A	#N/A
18	50x 507	1	#N/A	#N/A
19
20	501	1	#N/A	#N/A
21	501	2	#N/A	#N/A
22	501	3	#N/A	#N/A
23	501	4	#N/A	#N/A
Sales

Cell Formulas
Range	Formula
C20:C23,C14:C18,C8:C12,C2:C6	C2	=INDEX('[Costing Table (sent)]Costing Table'!$C$2:$C$30,MATCH(A2&"|"&B2,'[Costing Table (sent)]Costing Table'!$A$2:$A$30&"|"&'[Costing Table (sent)]Costing Table'!$B$2:$B$30,0))
D20:D23,D14:D18,D8:D12,D2:D6	D2	=SUM(C2*B2)
Press CTRL+SHIFT+ENTER to enter array formulas.

Will this formula work with my extended costing table listed below to pick up all prices?

Test.xlsx
	A	B	C	D
1	Description	Qty	Unit	Total
2	5x 501	1	£27.00	£27.00
3	5x 501	2	£24.30	£48.60
4	5x 501	3	£22.95	£68.85
5	5x 501	4	£21.60	£86.40
6	5x 501	5	£21.60	£108.00
7	5x 501	6	£21.60	£129.60
8	5x 501	7	£21.60	£151.20
9	5x 501	8	£21.60	£172.80
10	5x 501	9	£21.60	£194.40
11	5x 501	10	£21.60	£216.00
12
13	5x 503	1	£27.00	£27.00
14	5x 503	2	£24.30	£48.60
15	5x 503	3	£22.95	£68.85
16	5x 503	4	£21.60	£86.40
17	5x 503	5	£21.60	£108.00
18	5x 503	6	£21.60	£129.60
19	5x 503	7	£21.60	£151.20
20	5x 503	8	£21.60	£172.80
21	5x 503	9	£21.60	£194.40
22	5x 503	10	£21.60	£216.00
23
24	5x 507	1	£27.00	£27.00
25	5x 507	2	£24.30	£48.60
26	5x 507	3	£22.95	£68.85
27	5x 507	4	£21.60	£86.40
28	5x 507	5	£21.60	£108.00
29	5x 507	6	£21.60	£129.60
30	5x 507	7	£21.60	£151.20
31	5x 507	8	£21.60	£172.80
32	5x 507	9	£21.60	£194.40
33	5x 507	10	£21.60	£216.00
34
35	10x 501	1	£40.00	£40.00
36	10x 501	2	£38.00	£76.00
37	10x 501	3	£36.00	£108.00
38	10x 501	4	£34.00	£136.00
39	10x 501	5	£34.00	£170.00
40	10x 501	6	£34.00	£204.00
41	10x 501	7	£34.00	£238.00
42	10x 501	8	£34.00	£272.00
43	10x 501	9	£34.00	£306.00
44	10x 501	10	£34.00	£340.00
45
46	10x 503	1	£40.00	£40.00
47	10x 503	2	£38.00	£76.00
48	10x 503	3	£36.00	£108.00
49	10x 503	4	£34.00	£136.00
50	10x 503	5	£34.00	£170.00
51	10x 503	6	£34.00	£204.00
52	10x 503	7	£34.00	£238.00
53	10x 503	8	£34.00	£272.00
54	10x 503	9	£34.00	£306.00
55	10x 503	10	£34.00	£340.00
56
57	10x 507	1	£40.00	£40.00
58	10x 507	2	£38.00	£76.00
59	10x 507	3	£36.00	£108.00
60	10x 507	4	£34.00	£136.00
61	10x 507	5	£34.00	£170.00
62	10x 507	6	£34.00	£204.00
63	10x 507	7	£34.00	£238.00
64	10x 507	8	£34.00	£272.00
65	10x 507	9	£34.00	£306.00
66	10x 507	10	£34.00	£340.00
67
68	20x 501	1	£70.00	£70.00
69	20x 501	2	£66.50	£133.00
70	20x 501	3	£63.00	£189.00
71	20x 501	4	£59.50	£238.00
72	20x 501	5	£59.50	£297.50
73	20x 501	6	£59.50	£357.00
74	20x 501	7	£59.50	£416.50
75	20x 501	8	£59.50	£476.00
76	20x 501	9	£59.50	£535.50
77	20x 501	10	£59.50	£595.00
78
79	20x 503	1	£70.00	£70.00
80	20x 503	2	£66.50	£133.00
81	20x 503	3	£63.00	£189.00
82	20x 503	4	£59.50	£238.00
83	20x 503	5	£59.50	£297.50
84	20x 503	6	£59.50	£357.00
85	20x 503	7	£59.50	£416.50
86	20x 503	8	£59.50	£476.00
87	20x 503	9	£59.50	£535.50
88	20x 503	10	£59.50	£595.00
89
90	20x 507	1	£70.00	£70.00
91	20x 507	2	£66.50	£133.00
92	20x 507	3	£63.00	£189.00
93	20x 507	4	£59.50	£238.00
94	20x 507	5	£59.50	£297.50
95	20x 507	6	£59.50	£357.00
96	20x 507	7	£59.50	£416.50
97	20x 507	8	£59.50	£476.00
98	20x 507	9	£59.50	£535.50
99	20x 507	10	£59.50	£595.00
100
101	30x 501	1	£90.00	£90.00
102	30x 501	2	£85.50	£171.00
103	30x 501	3	£81.00	£243.00
104	30x 501	4	£76.50	£306.00
105	30x 501	5	£76.50	£382.50
106	30x 501	6	£76.50	£459.00
107	30x 501	7	£76.50	£535.50
108	30x 501	8	£76.50	£612.00
109	30x 501	9	£76.50	£688.50
110	30x 501	10	£76.50	£765.00
111
112	30x 503	1	£90.00	£90.00
113	30x 503	2	£85.50	£171.00
114	30x 503	3	£81.00	£243.00
115	30x 503	4	£76.50	£306.00
116	30x 503	5	£76.50	£382.50
117	30x 503	6	£76.50	£459.00
118	30x 503	7	£76.50	£535.50
119	30x 503	8	£76.50	£612.00
120	30x 503	9	£76.50	£688.50
121	30x 503	10	£76.50	£765.00
122
123	30x 507	1	£90.00	£90.00
124	30x 507	2	£85.50	£171.00
125	30x 507	3	£81.00	£243.00
126	30x 507	4	£76.50	£306.00
127	30x 507	5	£76.50	£382.50
128	30x 507	6	£76.50	£459.00
129	30x 507	7	£76.50	£535.50
130	30x 507	8	£76.50	£612.00
131	30x 507	9	£76.50	£688.50
132	30x 507	10	£76.50	£765.00
133
134	40x 501	1	£120.00	£120.00
135	40x 501	2	£114.00	£228.00
136	40x 501	3	£108.00	£324.00
137	40x 501	4	£102.00	£408.00
138	40x 501	5	£102.00	£510.00
139	40x 501	6	£102.00	£612.00
140	40x 501	7	£102.00	£714.00
141	40x 501	8	£102.00	£816.00
142	40x 501	9	£102.00	£918.00
143	40x 501	10	£102.00	£1,020.00
144
145	40x 503	1	£120.00	£120.00
146	40x 503	2	£114.00	£228.00
147	40x 503	3	£108.00	£324.00
148	40x 503	4	£102.00	£408.00
149	40x 503	5	£102.00	£510.00
150	40x 503	6	£102.00	£612.00
151	40x 503	7	£102.00	£714.00
152	40x 503	8	£102.00	£816.00
153	40x 503	9	£102.00	£918.00
154	40x 503	10	£102.00	£1,020.00
155
156	40x 507	1	£120.00	£120.00
157	40x 507	2	£114.00	£228.00
158	40x 507	3	£108.00	£324.00
159	40x 507	4	£102.00	£408.00
160	40x 507	5	£102.00	£510.00
161	40x 507	6	£102.00	£612.00
162	40x 507	7	£102.00	£714.00
163	40x 507	8	£102.00	£816.00
164	40x 507	9	£102.00	£918.00
165	40x 507	10	£102.00	£1,020.00
166
167	50x 501	1	£150.00	£150.00
168	50x 501	2	£142.50	£285.00
169	50x 501	3	£135.00	£405.00
170	50x 501	4	£127.50	£510.00
171	50x 501	5	£127.50	£637.50
172	50x 501	6	£127.50	£765.00
173	50x 501	7	£127.50	£892.50
174	50x 501	8	£127.50	£1,020.00
175	50x 501	9	£127.50	£1,147.50
176	50x 501	10	£127.50	£1,275.00
177
178	50x 503	1	£150.00	£150.00
179	50x 503	2	£142.50	£285.00
180	50x 503	3	£135.00	£405.00
181	50x 503	4	£127.50	£510.00
182	50x 503	5	£127.50	£637.50
183	50x 503	6	£127.50	£765.00
184	50x 503	7	£127.50	£892.50
185	50x 503	8	£127.50	£1,020.00
186	50x 503	9	£127.50	£1,147.50
187	50x 503	10	£127.50	£1,275.00
188
189	50x 507	1	£150.00	£150.00
190	50x 507	2	£142.50	£285.00
191	50x 507	3	£135.00	£405.00
192	50x 507	4	£127.50	£510.00
193	50x 507	5	£127.50	£637.50
194	50x 507	6	£127.50	£765.00
195	50x 507	7	£127.50	£892.50
196	50x 507	8	£127.50	£1,020.00
197	50x 507	9	£127.50	£1,147.50
198	50x 507	10	£127.50	£1,275.00
199
200	501	1	£6.99	£6.99
201	501	2	£6.29	£12.58
202	501	3	£5.94	£17.82
203	501	4	£5.59	£22.36
204	501	5	£5.59	£27.95
205	501	6	£5.59	£33.54
206	501	7	£5.59	£39.13
207	501	8	£5.59	£44.72
208	501	9	£5.59	£50.31
209	501	10	£5.59	£55.90
210	501	11	£5.59	£61.49
211	501	12	£5.59	£67.08
212	501	13	£5.59	£72.67
213	501	14	£5.59	£78.26
214	501	15	£5.59	£83.85
215	501	16	£5.59	£89.44
216	501	17	£5.59	£95.03
217	501	18	£5.59	£100.62
218	501	19	£5.59	£106.21
219	501	20	£5.59	£111.80
220	501	21	£5.59	£117.39
221	501	22	£5.59	£122.98
222	501	23	£5.59	£128.57
223	501	24	£5.59	£134.16
224	501	25	£5.59	£139.75
225	501	26	£5.59	£145.34
226	501	27	£5.59	£150.93
227	501	28	£5.59	£156.52
228	501	29	£5.59	£162.11
229	501	30	£5.59	£167.70
230	501	31	£5.59	£173.29
231	501	32	£5.59	£178.88
232	501	33	£5.59	£184.47
233	501	34	£5.59	£190.06
234	501	35	£5.59	£195.65
235	501	36	£5.59	£201.24
236	501	37	£5.59	£206.83
237	501	38	£5.59	£212.42
238	501	39	£5.59	£218.01
239	501	40	£5.59	£223.60
240	501	41	£5.59	£229.19
241	501	42	£5.59	£234.78
242	501	43	£5.59	£240.37
243	501	44	£5.59	£245.96
244	501	45	£5.59	£251.55
245	501	46	£5.59	£257.14
246	501	47	£5.59	£262.73
247	501	48	£5.59	£268.32
248	501	49	£5.59	£273.91
249	501	50	£5.59	£279.50
250
251	503	1	£6.99	£6.99
252	503	2	£6.29	£12.58
253	503	3	£5.94	£17.82
254	503	4	£5.59	£22.36
255	503	5	£5.59	£27.95
256	503	6	£5.59	£33.54
257	503	7	£5.59	£39.13
258	503	8	£5.59	£44.72
259	503	9	£5.59	£50.31
260	503	10	£5.59	£55.90
261	503	11	£5.59	£61.49
262	503	12	£5.59	£67.08
263	503	13	£5.59	£72.67
264	503	14	£5.59	£78.26
265	503	15	£5.59	£83.85
266	503	16	£5.59	£89.44
267	503	17	£5.59	£95.03
268	503	18	£5.59	£100.62
269	503	19	£5.59	£106.21
270	503	20	£5.59	£111.80
271	503	21	£5.59	£117.39
272	503	22	£5.59	£122.98
273	503	23	£5.59	£128.57
274	503	24	£5.59	£134.16
275	503	25	£5.59	£139.75
276	503	26	£5.59	£145.34
277	503	27	£5.59	£150.93
278	503	28	£5.59	£156.52
279	503	29	£5.59	£162.11
280	503	30	£5.59	£167.70
281	503	31	£5.59	£173.29
282	503	32	£5.59	£178.88
283	503	33	£5.59	£184.47
284	503	34	£5.59	£190.06
285	503	35	£5.59	£195.65
286	503	36	£5.59	£201.24
287	503	37	£5.59	£206.83
288	503	38	£5.59	£212.42
289	503	39	£5.59	£218.01
290	503	40	£5.59	£223.60
291	503	41	£5.59	£229.19
292	503	42	£5.59	£234.78
293	503	43	£5.59	£240.37
294	503	44	£5.59	£245.96
295	503	45	£5.59	£251.55
296	503	46	£5.59	£257.14
297	503	47	£5.59	£262.73
298	503	48	£5.59	£268.32
299	503	49	£5.59	£273.91
300	503	50	£5.59	£279.50
301
Costing Table

Cell Formulas
Range	Formula
D251:D300,D200:D249,D189:D198,D178:D187,D167:D176,D156:D165,D145:D154,D134:D143,D123:D132,D112:D121,D101:D110,D90:D99,D79:D88,D68:D77,D57:D66,D46:D55,D35:D44,D24:D33,D13:D22,D2:D11	D2	=SUM(C2*B2)

It was too long so i need to split the table.

Test.xlsx
	A	B	C	D
301
302	507	1	£6.99	£6.99
303	507	2	£6.29	£12.58
304	507	3	£5.94	£17.82
305	507	4	£5.59	£22.36
306	507	5	£5.59	£27.95
307	507	6	£5.59	£33.54
308	507	7	£5.59	£39.13
309	507	8	£5.59	£44.72
310	507	9	£5.59	£50.31
311	507	10	£5.59	£55.90
312	507	11	£5.59	£61.49
313	507	12	£5.59	£67.08
314	507	13	£5.59	£72.67
315	507	14	£5.59	£78.26
316	507	15	£5.59	£83.85
317	507	16	£5.59	£89.44
318	507	17	£5.59	£95.03
319	507	18	£5.59	£100.62
320	507	19	£5.59	£106.21
321	507	20	£5.59	£111.80
322	507	21	£5.59	£117.39
323	507	22	£5.59	£122.98
324	507	23	£5.59	£128.57
325	507	24	£5.59	£134.16
326	507	25	£5.59	£139.75
327	507	26	£5.59	£145.34
328	507	27	£5.59	£150.93
329	507	28	£5.59	£156.52
330	507	29	£5.59	£162.11
331	507	30	£5.59	£167.70
332	507	31	£5.59	£173.29
333	507	32	£5.59	£178.88
334	507	33	£5.59	£184.47
335	507	34	£5.59	£190.06
336	507	35	£5.59	£195.65
337	507	36	£5.59	£201.24
338	507	37	£5.59	£206.83
339	507	38	£5.59	£212.42
340	507	39	£5.59	£218.01
341	507	40	£5.59	£223.60
342	507	41	£5.59	£229.19
343	507	42	£5.59	£234.78
344	507	43	£5.59	£240.37
345	507	44	£5.59	£245.96
346	507	45	£5.59	£251.55
347	507	46	£5.59	£257.14
348	507	47	£5.59	£262.73
349	507	48	£5.59	£268.32
350	507	49	£5.59	£273.91
351	507	50	£5.59	£279.50
Costing Table

Cell Formulas
Range	Formula
D302:D351	D302	=SUM(C302*B302)

 

[Fluff]

You need to change the ranges to cover all the data in the costing table.

 

[bobzy20]

You need to change the ranges to cover all the data in the costing table.Of course

Of Course!

Sorry now it works great, thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback.