AVERAGEIF / SUMIF with multiple ranges

**xandermacleod** · 09-13-2012, 12:12 PM

Hi there. I realise this topic has been brought up before but the solution and question was slightly different from the problem im having.

I need a formula to average out various 'non-error' cells dotted around my worksheet.

My data is appearing in cells G8 G57 G106 G155 G204 & G253
The cells either have values of between 1and10, or -more commonly- have #N/A values. (purposefully used to avoid graph interpolation issues)

Is there any way I can do use an AVERAGEIF formula or some equivalent thereof to get the average of just the values that appear between 1 and 10 (i.e. ignore N/A values)?
Something like:
=AVERAGEIF((G8,G57,G106,G155,G204,G253), "<> NA()", (G8,G57,G106,G155,G204,G253))

I tried to get around the problem using a normal average, but the problem is persisting:

Please Login or Register  to view this content.

Any help is appreciated.

**NBVC** · 09-13-2012, 12:19 PM

Try:

=AVERAGE(IF(ISNUMBER(G8:G253),IF((MOD(ROW(G8:G253)-ROW(G8),49)=0)*(G8:G253),G8:G253)))

confirmed with CTRL+SHIFT+ENTER not just ENTER

**xandermacleod** · 09-13-2012, 12:25 PM

Part of the problem is that I need to use a formula that can then do a separate average for - G9 G58 G107 G156 G205 & G254
Similarly again I will need a separate one for - G10 G59 G108 G157 G206 & G255
and again for - G11 G60 G109 G158 G206 & G256
etc
all the way up to - G54 G103 G152 G201 G250 & G299

Hence I cant use a G8:G253 style range.

**NBVC** · 09-13-2012, 01:08 PM

This part n the formula:

MOD(ROW(G8:G253)-ROW(G8),49)=0

grabs and checks only every 49th cell starting from G89.. so if you copy down, then it will do the same but start at G9.

Try it and test it...