0

Good afternoon,

I am using the following formula to calculate the average of a dynamic range. The range should be the range that contains the next 3 values in a list > 0 (Range could be higher than 3 if there are 0s).

=AVERAGEIF(OFFSET(ED75:ED75,0,0,AGGREGATE(15,6,((ROW(ED75:$ED$171)-ROW()+1-48)/ED75:$ED$171)*(ED75:$ED$171),3)),"<>0")

The formula is properly working in majority of the cells but it doesn't in some random cells. The problem is that it doesn't take the proper height, but height-1.

When I evaluate it, the result of the aggregate function which is the height is ok (image 1.) but the next evaluate doesn't dimension the range to 3 but to 2.

First evaluate

Second evaluate

Thanks in advance, Almudena

Ike
  • 9,580
  • 4
  • 13
  • 29
Almudena
  • 1
  • Are you certain that the end result is incorrect? If you look at the message at the foot of the *Evaluate Formula* box, it states that "*A function in this formula causes the result to change each time the spreadsheet is calculated. The final evaluation step will match the result in the cell, but interim steps may not*." Hence the incorrect evaluation at the step which you post is to be expected. The final result, however, should nevertheless be correct. – Jos Woolley Feb 09 '23 at 15:17
  • This message is because the offset is a volatile formula. However in this evaluation the height is 3, so the range should be ED27:ED29, and it is ED27:ED28. For other cells the result is good but there are some cells whit this same error, height different to range size. – Almudena Feb 09 '23 at 16:04
  • It might be difficult to help you without seeing an actual workbook. – Jos Woolley Feb 09 '23 at 16:21
  • Sorry I am new in this forum, is there a way to share the workbook? – Almudena Feb 10 '23 at 11:33
  • Only via some other site, I'm afraid. – Jos Woolley Feb 10 '23 at 11:42

0 Answers0