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.

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.