Calculating Average Price, Based on Multiple Cells

[greatscott1]

Hi All,

I wonder if anyone could help with one of the formulas I'm trying to create? Once I understand the one, I'll be able to adapt for the others I'm trying to do.

I have a separate sheet ("Statistics") where I specify a 'from' ('Statistics'!B3) and 'to' ('Statistics'!B4) date. I'd like to have a formula that will give me an average price paid for PRODUCT A across all suppliers, by multiplying column F by column J for all instances that fall between the date ranges specified (column A).

Many thanks in advance!

Timber CallOffs (WIP).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
2						VOLUMES (m³)				RATES (£/m³)
3	Invoice Date	Invoice Number	PO Number (TFM/*****)	Ordered By	Supplier	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D
4	02/12/2022	121231	11372	Employee 1	Supplier 3	29.244	19.447			£295	£15
5	06/12/2022	61002212	11379	Employee 1	Supplier 1	34.178	14.133			£295	£15
6	06/12/2022	61002213	11379	Employee 1	Supplier 1				1.512				£736
7	07/12/2022	121265	11380	Employee 1	Supplier 3	33.511	14.418			£295	£15
8	08/12/2022	334093	11374	Employee 1	Supplier 4	33.563	5.774		2.474	£295	£15		£700
9	12/12/2022	334121	11384	Employee 1	Supplier 4	44.991				£295
10	14/12/2022	121461	11386	Employee 1	Supplier 3	30.403	6.772	10.686		£295	£15	£220
11	16/12/2022	61002716	11389	Employee 1	Supplier 1	31.442	23.569			£295	£15
12	16/12/2022	121535	11387	Employee 1	Supplier 3	46.181		6.171		£295		£220
13	16/12/2022	121566	11392	Employee 1	Supplier 3	27.615	5.124	12.342		£295	£15	£220
14
15
Timber Invoices

 

[etaf]

are you still using version 2010 of excel ?
Also - as the price seems to be in most cases the same - then the average will come back to the same number

i have added the average - using sumproduct and SUMIFS = But using dates in celss O1 and P1
just to show on 1 sheet

=SUMPRODUCT((F3:F12)*(J3:J12)*($A$3:$A$12>=$O$1)*($A$3:$A$12<=$P$1))/SUMIFS(F$3:F$12,$A$3:$A$12,">="&$O$1,$A$3:$A$12,"<="&$P$1)
just change the O1 and P1 to 'Statistics'!$B$3 and 'Statistics'!$B$4

=SUMPRODUCT((F3:F12)*(J3:J12)*($A$3:$A$12>='Statistics'!$B$3)*($A$3:$A$12<='Statistics'!$B$4))/SUMIFS(F$3:F$12,$A$3:$A$12,">="&'Statistics'!$B$3,$A$3:$A$12,"<="&'Statistics'!$B$4)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1						VOLUMES (m³)				RATES (£/m³)					12/8/22	12/16/22
2	Invoice Date	Invoice Number	PO Number (TFM/*****)	Ordered By	Supplier	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D		Product A - Average	Product b - Average	Product C - Average	Product D - Average
3	12/2/22	121231	11372	Employee 1	Supplier 3	29.244	19.447			295	15				295	15	220	700
4	12/6/22	61002212	11379	Employee 1	Supplier 1	34.178	14.133			295	15
5	12/6/22	61002213	11379	Employee 1	Supplier 1				1.512				736
6	12/7/22	121265	11380	Employee 1	Supplier 3	33.511	14.418			295	15
7	12/8/22	334093	11374	Employee 1	Supplier 4	33.563	5.774		2.474	295	15		700
8	12/12/22	334121	11384	Employee 1	Supplier 4	44.991				295
9	12/14/22	121461	11386	Employee 1	Supplier 3	30.403	6.772	10.686		295	15	220
10	12/16/22	61002716	11389	Employee 1	Supplier 1	31.442	23.569			295	15
11	12/16/22	121535	11387	Employee 1	Supplier 3	46.181		6.171		295		220
12	12/16/22	121566	11392	Employee 1	Supplier 3	27.615	5.124	12.342		295	15	220
13
Sheet1

Cell Formulas
Range	Formula
F3	F3	=SUM(6.613+16.768+5.863)
G3	G3	=SUM(6.926+7.128+5.393)
F4	F4	=SUM(6.613+4.01+4.374+6.415+6.124+2.28+2.094+2.268)
G4	G4	=SUM(7.329+6.804)
O3:R3	O3	=SUMPRODUCT((F3:F12)*(J3:J12)*($A$3:$A$12>=$O$1)*($A$3:$A$12<=$P$1))/SUMIFS(F$3:F$12,$A$3:$A$12,">="&$O$1,$A$3:$A$12,"<="&$P$1)
F6	F6	=SUM(5.702+9.842+3.272+3.835+8.09+2.77)
G6	G6	=SUM(7.128+7.29)
F7	F7	=SUM(5.367+5.141+4.738+2.397+2.538+8.588+4.794)
G7	G7	=SUM(2.961+2.813)
I7	I7	=SUM(1.21+0.68+0.584)
G10	G10	=SUM(7.128+3.257+6.204+6.98)
F9	F9	=SUM(6.613+11.866+3.645+3.493+4.786)
F10	F10	=SUM(4.031+6.415+1.919+2.559+2.769+2.931+2.268+2.443+2.966+3.141)
F11	F11	=SUM(3.762+25.152+8.91+4.027+4.33)
F12	F12	=SUM(8.87+6.613+3.742+8.39)

 

[Skybluekid]

Hi

Here is another solution

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Invoice Date	Invoice Number	PO Number (TFM/*****)	Ordered By	Supplier	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D
3
4	02/12/2022	121231	11372	Employee 1	Supplier 3	29.244	19.447			£295	£15			Start Date	End Date
5	06/12/2022	61002212	11379	Employee 1	Supplier 1	34.178	14.133			£295	£15			01/12/2022	08/12/2022
6	06/12/2022	61002213	11379	Employee 1	Supplier 1				1.512				£736
7	07/12/2022	121265	11380	Employee 1	Supplier 3	33.511	14.418			£295	£15
8	08/12/2022	334093	11374	Employee 1	Supplier 4	33.563	5.774		2.474	£295	£15		£700
9	12/12/2022	334121	11384	Employee 1	Supplier 4	44.991				£295
10	14/12/2022	121461	11386	Employee 1	Supplier 3	30.403	6.772	10.686		£295	£15	£220
11	16/12/2022	61002716	11389	Employee 1	Supplier 1	31.442	23.569			£295	£15
12	16/12/2022	121535	11387	Employee 1	Supplier 3	46.181		6.171		£295		£220
13	16/12/2022	121566	11392	Employee 1	Supplier 3	27.615	5.124	12.342		£295	£15	£220
14
15						£7,699.26
Sheet1

Cell Formulas
Range	Formula
F15	F15	=SUMPRODUCT((F4:F13)*(J4:J13)*($A$4:$A$13>=N5)*($A$4:$A$13<=O5))/COUNTIFS($A$4:$A$13,">="&N5,$A$4:$A$13,"<="&O5)

 

[greatscott1]

are you still using version 2010 of excel ?
Also - as the price seems to be in most cases the same - then the average will come back to the same number

i have added the average - using sumproduct and SUMIFS = But using dates in celss O1 and P1
just to show on 1 sheet

=SUMPRODUCT((F3:F12)*(J3:J12)*($A$3:$A$12>=$O$1)*($A$3:$A$12<=$P$1))/SUMIFS(F$3:F$12,$A$3:$A$12,">="&$O$1,$A$3:$A$12,"<="&$P$1)
just change the O1 and P1 to 'Statistics'!$B$3 and 'Statistics'!$B$4

=SUMPRODUCT((F3:F12)*(J3:J12)*($A$3:$A$12>='Statistics'!$B$3)*($A$3:$A$12<='Statistics'!$B$4))/SUMIFS(F$3:F$12,$A$3:$A$12,">="&'Statistics'!$B$3,$A$3:$A$12,"<="&'Statistics'!$B$4)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1						VOLUMES (m³)				RATES (£/m³)					12/8/22	12/16/22
2	Invoice Date	Invoice Number	PO Number (TFM/*****)	Ordered By	Supplier	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D		Product A - Average	Product b - Average	Product C - Average	Product D - Average
3	12/2/22	121231	11372	Employee 1	Supplier 3	29.244	19.447			295	15				295	15	220	700
4	12/6/22	61002212	11379	Employee 1	Supplier 1	34.178	14.133			295	15
5	12/6/22	61002213	11379	Employee 1	Supplier 1				1.512				736
6	12/7/22	121265	11380	Employee 1	Supplier 3	33.511	14.418			295	15
7	12/8/22	334093	11374	Employee 1	Supplier 4	33.563	5.774		2.474	295	15		700
8	12/12/22	334121	11384	Employee 1	Supplier 4	44.991				295
9	12/14/22	121461	11386	Employee 1	Supplier 3	30.403	6.772	10.686		295	15	220
10	12/16/22	61002716	11389	Employee 1	Supplier 1	31.442	23.569			295	15
11	12/16/22	121535	11387	Employee 1	Supplier 3	46.181		6.171		295		220
12	12/16/22	121566	11392	Employee 1	Supplier 3	27.615	5.124	12.342		295	15	220
13
Sheet1

Cell Formulas
Range	Formula
F3	F3	=SUM(6.613+16.768+5.863)
G3	G3	=SUM(6.926+7.128+5.393)
F4	F4	=SUM(6.613+4.01+4.374+6.415+6.124+2.28+2.094+2.268)
G4	G4	=SUM(7.329+6.804)
O3:R3	O3	=SUMPRODUCT((F3:F12)*(J3:J12)*($A$3:$A$12>=$O$1)*($A$3:$A$12<=$P$1))/SUMIFS(F$3:F$12,$A$3:$A$12,">="&$O$1,$A$3:$A$12,"<="&$P$1)
F6	F6	=SUM(5.702+9.842+3.272+3.835+8.09+2.77)
G6	G6	=SUM(7.128+7.29)
F7	F7	=SUM(5.367+5.141+4.738+2.397+2.538+8.588+4.794)
G7	G7	=SUM(2.961+2.813)
I7	I7	=SUM(1.21+0.68+0.584)
G10	G10	=SUM(7.128+3.257+6.204+6.98)
F9	F9	=SUM(6.613+11.866+3.645+3.493+4.786)
F10	F10	=SUM(4.031+6.415+1.919+2.559+2.769+2.931+2.268+2.443+2.966+3.141)
F11	F11	=SUM(3.762+25.152+8.91+4.027+4.33)
F12	F12	=SUM(8.87+6.613+3.742+8.39)

Yep, still on 2010! Not sure what benefit I'd get from upgrading?

The sample data is poor I know - it's not normally this straight-forward!

Couple of comments...

- Your range is specified as F3:F12 - I assume this won't grow as I add more enteries over time?

- Product D has more than 1 rate, but your calculation as given a value of 700? My explination was a little poor, but I'd manually calculate this as ((1.512*736)+(2.474*700))/(1.512+2.474)=£713.6557953

 

[greatscott1]

Hi

Here is another solution

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Invoice Date	Invoice Number	PO Number (TFM/*****)	Ordered By	Supplier	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D
3
4	02/12/2022	121231	11372	Employee 1	Supplier 3	29.244	19.447			£295	£15			Start Date	End Date
5	06/12/2022	61002212	11379	Employee 1	Supplier 1	34.178	14.133			£295	£15			01/12/2022	08/12/2022
6	06/12/2022	61002213	11379	Employee 1	Supplier 1				1.512				£736
7	07/12/2022	121265	11380	Employee 1	Supplier 3	33.511	14.418			£295	£15
8	08/12/2022	334093	11374	Employee 1	Supplier 4	33.563	5.774		2.474	£295	£15		£700
9	12/12/2022	334121	11384	Employee 1	Supplier 4	44.991				£295
10	14/12/2022	121461	11386	Employee 1	Supplier 3	30.403	6.772	10.686		£295	£15	£220
11	16/12/2022	61002716	11389	Employee 1	Supplier 1	31.442	23.569			£295	£15
12	16/12/2022	121535	11387	Employee 1	Supplier 3	46.181		6.171		£295		£220
13	16/12/2022	121566	11392	Employee 1	Supplier 3	27.615	5.124	12.342		£295	£15	£220
14
15						£7,699.26
Sheet1

Cell Formulas
Range	Formula
F15	F15	=SUMPRODUCT((F4:F13)*(J4:J13)*($A$4:$A$13>=N5)*($A$4:$A$13<=O5))/COUNTIFS($A$4:$A$13,">="&N5,$A$4:$A$13,"<="&O5)

Thank you for the reply! The answer to the query is £295 as all suppliers were the same price for this sample data - maybe I should adjust the sample data?

 

[Skybluekid]

Misunderstood see below

Book1
	F
15	£32.62
Sheet1

Cell Formulas
Range	Formula
F15	F15	=SUMPRODUCT((F4:F13)*(J4:J13)*($A$4:$A$13>=N5)*($A$4:$A$13<=O5))/SUMIFS(J4:J13,A$4:$A$13,">="&N5,$A$4:$A$13,"<="&O5)

If you were to put the information into an Excel Table, you can add more rows and the average cost will automatically update.

 

[etaf]

Yep, still on 2010! Not sure what benefit I'd get from upgrading?

possibly not - depending on how complex some of your requirements get - later versions have had a lot more functions added .... and simplified a lot of complex functions

- Product D has more than 1 rate, but your calculation as given a value of 700? My explination was a little poor, but I'd manually calculate this as ((1.512*736)+(2.474*700))/(1.512+2.474)=£713.6557953

But look at the date range I entered into O1 and P1 - so 700 is correct - i did that deliberately to show the dates are working - BUTR i should have said that
change the date in O1 and it will calculate correctly

- Your range is specified as F3:F12 - I assume this won't grow as I add more enteries over time?

You can just increase that to a maximum likely
F3:F100000

date change in O1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1						VOLUMES (m³)				RATES (£/m³)					12/2/22	12/16/22
2	Invoice Date	Invoice Number	PO Number (TFM/*****)	Ordered By	Supplier	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D		Product A - Average	Product b - Average	Product C - Average	Product D - Average
3	12/2/22	121231	11372	Employee 1	Supplier 3	29.244	19.447			295	15				295	15	220	713.655795
4	12/6/22	61002212	11379	Employee 1	Supplier 1	34.178	14.133			295	15
5	12/6/22	61002213	11379	Employee 1	Supplier 1				1.512				736
6	12/7/22	121265	11380	Employee 1	Supplier 3	33.511	14.418			295	15
7	12/8/22	334093	11374	Employee 1	Supplier 4	33.563	5.774		2.474	295	15		700
8	12/12/22	334121	11384	Employee 1	Supplier 4	44.991				295
9	12/14/22	121461	11386	Employee 1	Supplier 3	30.403	6.772	10.686		295	15	220
10	12/16/22	61002716	11389	Employee 1	Supplier 1	31.442	23.569			295	15
11	12/16/22	121535	11387	Employee 1	Supplier 3	46.181		6.171		295		220
12	12/16/22	121566	11392	Employee 1	Supplier 3	27.615	5.124	12.342		295	15	220
Sheet1

Cell Formulas
Range	Formula
F3	F3	=SUM(6.613+16.768+5.863)
G3	G3	=SUM(6.926+7.128+5.393)
F4	F4	=SUM(6.613+4.01+4.374+6.415+6.124+2.28+2.094+2.268)
G4	G4	=SUM(7.329+6.804)
O3:R3	O3	=SUMPRODUCT((F3:F12)*(J3:J12)*($A$3:$A$12>=$O$1)*($A$3:$A$12<=$P$1))/SUMIFS(F$3:F$12,$A$3:$A$12,">="&$O$1,$A$3:$A$12,"<="&$P$1)
F6	F6	=SUM(5.702+9.842+3.272+3.835+8.09+2.77)
G6	G6	=SUM(7.128+7.29)
F7	F7	=SUM(5.367+5.141+4.738+2.397+2.538+8.588+4.794)
G7	G7	=SUM(2.961+2.813)
I7	I7	=SUM(1.21+0.68+0.584)
G10	G10	=SUM(7.128+3.257+6.204+6.98)
F9	F9	=SUM(6.613+11.866+3.645+3.493+4.786)
F10	F10	=SUM(4.031+6.415+1.919+2.559+2.769+2.931+2.268+2.443+2.966+3.141)
F11	F11	=SUM(3.762+25.152+8.91+4.027+4.33)
F12	F12	=SUM(8.87+6.613+3.742+8.39)

 

[Skybluekid]

See below, I have put this is in a Table, so that it is expandable and added the line in yellow

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Start Date	01/12/2022
2			End Date	08/12/2022		Average Cost
3						£33.12
4	Invoice Date	Invoice Number	PO Number (TFM/*****)	Ordered By	Supplier	PRODUCT A	PRODUCT B	PRODUCT C	PRODUCT D	PRODUCT A2	PRODUCT B3	PRODUCT C4	PRODUCT D5
5	02/12/2022	121231	11372	Employee 1	Supplier 3	29.244	19.447			£295	£15
6	05/12/2022	61002211	11379	Employee 1	Supplier 1	35.12				£295
7	06/12/2022	61002212	11379	Employee 1	Supplier 1	34.178	14.133			£295	£15
8	06/12/2022	61002213	11379	Employee 1	Supplier 1				1.512				£736
9	07/12/2022	121265	11380	Employee 1	Supplier 3	33.511	14.418			£295	£15
10	08/12/2022	334093	11374	Employee 1	Supplier 4	33.563	5.774		2.474	£295	£15		£700
11	12/12/2022	334121	11384	Employee 1	Supplier 4	44.991				£295
12	14/12/2022	121461	11386	Employee 1	Supplier 3	30.403	6.772	10.686		£295	£15	£220
13	16/12/2022	61002716	11389	Employee 1	Supplier 1	31.442	23.569			£295	£15
14	16/12/2022	121535	11387	Employee 1	Supplier 3	46.181		6.171		£295		£220
15	16/12/2022	121566	11392	Employee 1	Supplier 3	27.615	5.124	12.342		£295	£15	£220
Sheet1

Cell Formulas
Range	Formula
F3	F3	=SUMPRODUCT((F5:F15)*(J5:J15)*($A$5:$A$15>=D1)*($A$5:$A$15<=D2))/SUMIFS(J5:J15,A$5:$A$15,">="&D1,$A$5:$A$15,"<="&D2)

 

[greatscott1]

Thank you all for your help - using a bit of both of your solutions I've managed to put one together that's working as needed

 

[etaf]

you are welcome