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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!