Ignoring specific values while selecting multiple cells

**HelpThisPoorChap** · 12-20-2012, 10:08 AM

So I'm doing some work that's not in my area and it involves excel, something I don't know quite know how to use. I have to correct a spreadsheet that takes a bunch of values and creates an average.

=average(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357)*100%

Yeah I didn't create this. Anyway, the problem is that most of these cells will be empty unless a whole bunch of other stuff is filled. These N cells take several values, put them together and create a new one. As time goes by they are filled. While they aren't filled, they'll return the DIV/0 error.

So when that formula I pasted there uses all of these values, it also returns a DIV/0 error because it is getting info from cells that have this error.

I managed to correct that with this
=averageif(N15:N357;"<>#DIV/0!")*100%

It works like a charm save for one problem. When I use averageif I'm forced to use an interval. Along this interval other values show up (it is always a value ranging from 0 to 2). So when the formula works its magic the results are slightly skewed because of this other value that I don't want.

So I figure there must be two ways around this. The first one would be using something like averageif that lets me use several handpicked cells instead of an interval. If I try

=averageif(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357;"<>#DIV/0!")*100%

It doesn't work, it says I have too many values. So if I could the exact same thing as I did with averageif but keeping all of these values it'd be super nice.

The other solution, less nice but equally effective I guess, is using this same interval but having more than one criteria. The first criteria would still be the one telling it to ignore DIV/0 error, the second criteria would be the one to ignore any values equal or lower than 2.

Any clues?

**Ron Coderre** · 12-20-2012, 10:59 AM

I was hoping the gaps between relevant cells would be constant (14 cells), but that is not the case.
Consequently, I came up with this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)

Please Login or Register  to view this content.

Is that something you can work with?

**HelpThisPoorChap** · 12-20-2012, 11:38 AM

I dunno why it is not working, it tells me the formula is incorrect and then it highlights this coma (ROW(N15:N357),
{15,2

**Ron Coderre** · 12-20-2012, 11:41 AM

I suspect it's a regional delimiter issue (comma vs semicolon).
Try this ARRAY FORMULA:

Please Login or Register  to view this content.

Does that work for you?

**HelpThisPoorChap** · 12-20-2012, 03:26 PM

Hmm, it might be. I'm using Excel in Portuguese. I made sure to change all function names to their respective Portuguese name, using this MS table as reference, but I didn't figure regional differences would extend to comas and semicolons.

But yeah, this one works now, thanks a bunch.