How can I fix the range of my Average formula within a row?

Julius Benn Sabeniano

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.

David Joyeuse

Hi @Julius Benn Sabeniano

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!

Paul Newcome

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.

David Joyeuse

Hi @Julius Benn Sabeniano

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!

David Joyeuse

@Julius Benn Sabeniano

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!

Paul Newcome

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.