How can I fix the range of my Average formula within a row?
Hi there!
Normally, I am using formulas to summary something like countifs, sum,average by column and it is very easyto have a fixed column range. Now, It's my first time to have a horizontally tabulated data and I want to get the average TAT within a row and make a fix range so that It will get the new columns being added.
I've tried to use the @row but it doen't work. Can anyone help me with the formula?
@Paul Newcome , I saw you helped a lot of people dealing with formulas. Maybe you can help.
Best Answers

I'll give it a try.
If I'm understanding you correctly, you want to return the AVG value of the next 4 days in your summary column.
Rather than going if AVG, I would go with a SUMIF of the whole range here. Since you have dates on your first row it'll be of great help. Then divide the SUMIF by 4.
=SUMIF([Day 1]@row:[Day 31]@row, [Day 1]$1:[Day 31]$1, AND(@cell>=TODAY(), @cell<=TODAY(4)))/4
That should do the trick.
Hope it helped!

I feel like this may not be the most efficient because you are dividing by a fixed number instead of accounting for the new days. I suggest...
=AVERAGEIF([Day 1]@row:[Day 31]@row, @cell <> "")
This will only pull cells across the row that are not blank and will average them together.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

I'll give it a try.
If I'm understanding you correctly, you want to return the AVG value of the next 4 days in your summary column.
Rather than going if AVG, I would go with a SUMIF of the whole range here. Since you have dates on your first row it'll be of great help. Then divide the SUMIF by 4.
=SUMIF([Day 1]@row:[Day 31]@row, [Day 1]$1:[Day 31]$1, AND(@cell>=TODAY(), @cell<=TODAY(4)))/4
That should do the trick.
Hope it helped!

Seems I mistaken on my formula.
Should be:
=SUMIF([Day 1]@row:[Day 31]@row, [Day 1]$1:[Day 31]$1, AND(@cell>=TODAY(), @cell<TODAY(4)))/4
The formula on the previous post will count 5 days instead of 4.
Sorry about that.
Hope it helped!

I feel like this may not be the most efficient because you are dividing by a fixed number instead of accounting for the new days. I suggest...
=AVERAGEIF([Day 1]@row:[Day 31]@row, @cell <> "")
This will only pull cells across the row that are not blank and will average them together.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!