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.
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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!