# Calculating Average Price, Based on Multiple Cells



## greatscott1 (Jan 4, 2023)

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).xlsxABCDEFGHIJKLM2VOLUMES (m³)RATES (£/m³)3Invoice DateInvoice NumberPO Number (TFM/*****)Ordered BySupplierPRODUCT APRODUCT BPRODUCT CPRODUCT DPRODUCT APRODUCT BPRODUCT CPRODUCT D402/12/202212123111372Employee 1Supplier 329.24419.447£295£15506/12/20226100221211379Employee 1Supplier 134.17814.133£295£15606/12/20226100221311379Employee 1Supplier 11.512£736707/12/202212126511380Employee 1Supplier 333.51114.418£295£15808/12/202233409311374Employee 1Supplier 433.5635.7742.474£295£15£700912/12/202233412111384Employee 1Supplier 444.991£2951014/12/202212146111386Employee 1Supplier 330.4036.77210.686£295£15£2201116/12/20226100271611389Employee 1Supplier 131.44223.569£295£151216/12/202212153511387Employee 1Supplier 346.1816.171£295£2201316/12/202212156611392Employee 1Supplier 327.6155.12412.342£295£15£2201415Timber Invoices


----------



## etaf (Jan 4, 2023)

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)

Book1ABCDEFGHIJKLMNOPQR1VOLUMES (m³)RATES (£/m³)12/8/2212/16/222Invoice DateInvoice NumberPO Number (TFM/*****)Ordered BySupplierPRODUCT APRODUCT BPRODUCT CPRODUCT DPRODUCT APRODUCT BPRODUCT CPRODUCT DProduct A - AverageProduct b - AverageProduct C - AverageProduct D - Average312/2/2212123111372Employee 1Supplier 329.24419.4472951529515220700412/6/226100221211379Employee 1Supplier 134.17814.13329515512/6/226100221311379Employee 1Supplier 11.512736612/7/2212126511380Employee 1Supplier 333.51114.41829515712/8/2233409311374Employee 1Supplier 433.5635.7742.47429515700812/12/2233412111384Employee 1Supplier 444.991295912/14/2212146111386Employee 1Supplier 330.4036.77210.686295152201012/16/226100271611389Employee 1Supplier 131.44223.569295151112/16/2212153511387Employee 1Supplier 346.1816.1712952201212/16/2212156611392Employee 1Supplier 327.6155.12412.3422951522013Sheet1Cell FormulasRangeFormulaF3F3=SUM(6.613+16.768+5.863)G3G3=SUM(6.926+7.128+5.393)F4F4=SUM(6.613+4.01+4.374+6.415+6.124+2.28+2.094+2.268)G4G4=SUM(7.329+6.804)O3:R3O3=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)F6F6=SUM(5.702+9.842+3.272+3.835+8.09+2.77)G6G6=SUM(7.128+7.29)F7F7=SUM(5.367+5.141+4.738+2.397+2.538+8.588+4.794)G7G7=SUM(2.961+2.813)I7I7=SUM(1.21+0.68+0.584)G10G10=SUM(7.128+3.257+6.204+6.98)F9F9=SUM(6.613+11.866+3.645+3.493+4.786)F10F10=SUM(4.031+6.415+1.919+2.559+2.769+2.931+2.268+2.443+2.966+3.141)F11F11=SUM(3.762+25.152+8.91+4.027+4.33)F12F12=SUM(8.87+6.613+3.742+8.39)


----------



## Skybluekid (Jan 4, 2023)

Hi

Here is another solution
Book1ABCDEFGHIJKLMNO2Invoice DateInvoice NumberPO Number (TFM/*****)Ordered BySupplierPRODUCT APRODUCT BPRODUCT CPRODUCT DPRODUCT APRODUCT BPRODUCT CPRODUCT D3402/12/202212123111372Employee 1Supplier 329.24419.447£295£15Start DateEnd Date506/12/20226100221211379Employee 1Supplier 134.17814.133£295£1501/12/202208/12/2022606/12/20226100221311379Employee 1Supplier 11.512£736707/12/202212126511380Employee 1Supplier 333.51114.418£295£15808/12/202233409311374Employee 1Supplier 433.5635.7742.474£295£15£700912/12/202233412111384Employee 1Supplier 444.991£2951014/12/202212146111386Employee 1Supplier 330.4036.77210.686£295£15£2201116/12/20226100271611389Employee 1Supplier 131.44223.569£295£151216/12/202212153511387Employee 1Supplier 346.1816.171£295£2201316/12/202212156611392Employee 1Supplier 327.6155.12412.342£295£15£2201415£7,699.26Sheet1Cell FormulasRangeFormulaF15F15=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 (Jan 4, 2023)

etaf said:


> 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
> ...


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 (Jan 4, 2023)

Skybluekid said:


> Hi
> 
> Here is another solution
> Book1ABCDEFGHIJKLMNO2Invoice DateInvoice NumberPO Number (TFM/*****)Ordered BySupplierPRODUCT APRODUCT BPRODUCT CPRODUCT DPRODUCT APRODUCT BPRODUCT CPRODUCT D3402/12/202212123111372Employee 1Supplier 329.24419.447£295£15Start DateEnd Date506/12/20226100221211379Employee 1Supplier 134.17814.133£295£1501/12/202208/12/2022606/12/20226100221311379Employee 1Supplier 11.512£736707/12/202212126511380Employee 1Supplier 333.51114.418£295£15808/12/202233409311374Employee 1Supplier 433.5635.7742.474£295£15£700912/12/202233412111384Employee 1Supplier 444.991£2951014/12/202212146111386Employee 1Supplier 330.4036.77210.686£295£15£2201116/12/20226100271611389Employee 1Supplier 131.44223.569£295£151216/12/202212153511387Employee 1Supplier 346.1816.171£295£2201316/12/202212156611392Employee 1Supplier 327.6155.12412.342£295£15£2201415£7,699.26Sheet1Cell FormulasRangeFormulaF15F15=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 (Jan 4, 2023)

Misunderstood see below
Book1F15£32.62Sheet1Cell FormulasRangeFormulaF15F15=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 (Jan 4, 2023)

> 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

Book1ABCDEFGHIJKLMNOPQR1VOLUMES (m³)RATES (£/m³)12/2/2212/16/222Invoice DateInvoice NumberPO Number (TFM/*****)Ordered BySupplierPRODUCT APRODUCT BPRODUCT CPRODUCT DPRODUCT APRODUCT BPRODUCT CPRODUCT DProduct A - AverageProduct b - AverageProduct C - AverageProduct D - Average312/2/2212123111372Employee 1Supplier 329.24419.4472951529515220713.655795412/6/226100221211379Employee 1Supplier 134.17814.13329515512/6/226100221311379Employee 1Supplier 11.512736612/7/2212126511380Employee 1Supplier 333.51114.41829515712/8/2233409311374Employee 1Supplier 433.5635.7742.47429515700812/12/2233412111384Employee 1Supplier 444.991295912/14/2212146111386Employee 1Supplier 330.4036.77210.686295152201012/16/226100271611389Employee 1Supplier 131.44223.569295151112/16/2212153511387Employee 1Supplier 346.1816.1712952201212/16/2212156611392Employee 1Supplier 327.6155.12412.34229515220Sheet1Cell FormulasRangeFormulaF3F3=SUM(6.613+16.768+5.863)G3G3=SUM(6.926+7.128+5.393)F4F4=SUM(6.613+4.01+4.374+6.415+6.124+2.28+2.094+2.268)G4G4=SUM(7.329+6.804)O3:R3O3=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)F6F6=SUM(5.702+9.842+3.272+3.835+8.09+2.77)G6G6=SUM(7.128+7.29)F7F7=SUM(5.367+5.141+4.738+2.397+2.538+8.588+4.794)G7G7=SUM(2.961+2.813)I7I7=SUM(1.21+0.68+0.584)G10G10=SUM(7.128+3.257+6.204+6.98)F9F9=SUM(6.613+11.866+3.645+3.493+4.786)F10F10=SUM(4.031+6.415+1.919+2.559+2.769+2.931+2.268+2.443+2.966+3.141)F11F11=SUM(3.762+25.152+8.91+4.027+4.33)F12F12=SUM(8.87+6.613+3.742+8.39)


----------



## Skybluekid (Jan 4, 2023)

See below, I have put this is in a Table, so that it is expandable and added the line in yellow
Book1ABCDEFGHIJKLM1Start Date01/12/20222End Date08/12/2022Average Cost3£33.124Invoice DateInvoice NumberPO Number (TFM/*****)Ordered BySupplierPRODUCT APRODUCT BPRODUCT CPRODUCT DPRODUCT A2PRODUCT B3PRODUCT C4PRODUCT D5502/12/202212123111372Employee 1Supplier 329.24419.447£295£15605/12/20226100221111379Employee 1Supplier 135.12£295706/12/20226100221211379Employee 1Supplier 134.17814.133£295£15806/12/20226100221311379Employee 1Supplier 11.512£736907/12/202212126511380Employee 1Supplier 333.51114.418£295£151008/12/202233409311374Employee 1Supplier 433.5635.7742.474£295£15£7001112/12/202233412111384Employee 1Supplier 444.991£2951214/12/202212146111386Employee 1Supplier 330.4036.77210.686£295£15£2201316/12/20226100271611389Employee 1Supplier 131.44223.569£295£151416/12/202212153511387Employee 1Supplier 346.1816.171£295£2201516/12/202212156611392Employee 1Supplier 327.6155.12412.342£295£15£220Sheet1Cell FormulasRangeFormulaF3F3=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 (Jan 4, 2023)

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 (Jan 4, 2023)

you are welcome


----------



## greatscott1 (Jan 4, 2023)

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).xlsxABCDEFGHIJKLM2VOLUMES (m³)RATES (£/m³)3Invoice DateInvoice NumberPO Number (TFM/*****)Ordered BySupplierPRODUCT APRODUCT BPRODUCT CPRODUCT DPRODUCT APRODUCT BPRODUCT CPRODUCT D402/12/202212123111372Employee 1Supplier 329.24419.447£295£15506/12/20226100221211379Employee 1Supplier 134.17814.133£295£15606/12/20226100221311379Employee 1Supplier 11.512£736707/12/202212126511380Employee 1Supplier 333.51114.418£295£15808/12/202233409311374Employee 1Supplier 433.5635.7742.474£295£15£700912/12/202233412111384Employee 1Supplier 444.991£2951014/12/202212146111386Employee 1Supplier 330.4036.77210.686£295£15£2201116/12/20226100271611389Employee 1Supplier 131.44223.569£295£151216/12/202212153511387Employee 1Supplier 346.1816.171£295£2201316/12/202212156611392Employee 1Supplier 327.6155.12412.342£295£15£2201415Timber Invoices


----------



## Skybluekid (Jan 4, 2023)

Glad that both myself and @etaf have helped


----------

