Calculating Dynamicly Average

Automatic Mail

Hi there ,

İ need a formula for a dynamic calculation , i added a sample as below , lets explain what i need ;

At below table yellow field area contains August 2023 datas, white field area contains September 2023 datas , imagine that every each month , i amm adding new lines to this table also , So i have a dynamic tabe infact , i need to calculate per month'a average under the average column

how is it calculating ? I calculate total of price column all each month's and divide price with related line.

For example ;

for first line ( Material A ) average calculating = 100 / 100+200+300 = 0,17

for second line ( Material B) average calculating = 200 / 100+200+300 = 0,33

for fourth line ( Material D ) average calculating = 40 / 40+50+60 = 0,27 (above two line was belong to August Data now for this line i need to divide September's total)

etc.

every month i have many lines that i add to my sheet , i want that calculating will be related for each month and it's year , i dont want to do this calculating manually every month , is there any way to put under Average cell a formula to make this calculation automaicly ?

Thanks in advance for your kind supports .

sample.xlsx

Nick Korna

No problem, it's a fairly simple adjustment:

=Price@row / SUMIF(Date:Date, AND(YEAR(@cell) = YEAR(Date@row), MONTH(@cell) = MONTH(Date@row)), Price:Price)

Example with some data for August last year added on:

Nick Korna

Hi @Automatic Mail,

This formula in your Average column should do the trick:

=Price@row / SUMIF(Date:Date, MONTH(@cell) = MONTH(Date@row), Price:Price)

This should do what you're after, if you wanted an average by material in a given month (for example, material A was used multiple times), then you could do a SUMIFS for the Price with a similar MONTH criteria as well as the Material.

Hope this helps, but if you've any problems/questions then just post! 🙂

Automatic Mail

Dear @Nick Korna ;

Thanks for your kind reply , i forgor to say about year.The average will be related about year too , i think when i wll use your formula , if we will pass next year , there will be same months , so could you pls revise formula based on years too.every month of year must be calculated specificly i mean

Nick Korna

No problem, it's a fairly simple adjustment:

=Price@row / SUMIF(Date:Date, AND(YEAR(@cell) = YEAR(Date@row), MONTH(@cell) = MONTH(Date@row)), Price:Price)

Example with some data for August last year added on:

Automatic Mail

@Nick Korna you are perfect ! this is the exactly one that i was searching , thank you very much!

Nick Korna

Glad to have helped! ☺️