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 in Others 159 views

## 1 answer to this question.

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),"")))```
• 63,180 points

–1 vote

## Company will be able to quickly issue SIM to user and expected gain in volume is approximately 10 times as the manual process of verification is replaced with secure automated system

LifeTel Telecom is the latest entrant in the ...READ MORE

## 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

## 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

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

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

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

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

## Convert Rows to Columns with values in Excel using custom format

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

## IF - ELSE IF - ELSE Structure in Excel

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