Calculating Dynamicly Average
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 .
Best Answer

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:
Answers

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! 🙂

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

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 you are perfect ! this is the exactly one that i was searching , thank you very much!

Glad to have helped! ☺️
Help Article Resources
Categories
Check out the Formula Handbook template!