Excel AVERAGEIF function in combination with MOD to average over every nth row not behaving as expected

0 votes

With Excel, I'm attempting to average every fourth row.

Given that all of the formula's components were computed correctly, the following formula should produce the desired outcome (if I do everything step by step).

=AVERAGEIF(MOD(ROW(D20:D35)-ROW(D20),4),"=0",D20:D35)    # does not work

=MOD(ROW(D20:D35)-ROW(D20),4)   # returns correct values

=AVERAGEIF(N19#,"=0",D20:D35) # N19# is the return of =MOD(ROW(D20:D35)-ROW(D20),4), and the result is the correct average, as it should be in the top function when everything is nested in one function.

I am not sure why the nested function is not working. Can anyone help?

Mar 27, 2023 in Others by Kithuzzz
• 38,010 points
276 views

1 answer to this question.

0 votes

Average Every Nth Row

  • All three finish in under a second for 1M rows but it seems like INDEX is faster than FILTER and FILTER is faster than Ron Rosenfeld's modernized version.
=LET(Data,D20:D35,nth,4,
AVERAGE(INDEX(Data,SEQUENCE((ROWS(Data)-1)/nth+1,,,nth))))
=LET(Data,D20:D35,nth,4,
AVERAGE(FILTER(Data,MOD(SEQUENCE(ROWS(Data)),nth)=1)))
=LET(Data,D20:D35,nth,4,
AVERAGE(IFERROR(Data*1/(MOD(SEQUENCE(ROWS(Data)),nth)=1),"")))
answered Mar 27, 2023 by narikkadan
• 63,700 points

Related Questions In Others

–2 votes
0 answers
0 votes
1 answer

Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,700 points
947 views
0 votes
1 answer

How to increment row value in an index function in excel?

Try this- =INDEX($F$27:$F$40,COLUMN(A$1)) It will automatically increase the row ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,700 points
1,390 views
0 votes
1 answer

How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,700 points
1,100 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,034 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,615 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
786 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,787 views
0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,700 points
2,769 views
0 votes
1 answer

How to Freeze Top Row and Apply Filter in Excel Automation with C#

Try this: // Fix first row workSheet.Activate(); workSheet.Application.ActiveWindow.SplitRow = 1; workSheet.Application.ActiveWindow.FreezePanes ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,700 points
2,273 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP