Thread: Averaging and ignoring non-numbers when not in a range

Hi everyone,

Thanks in advance for all help on this, because these forums have never let me down before, and I've always found the posters to be first rate.

My spreadsheet lists numbers in every row, but I need a formula that will average only every 5th number. That's not a problem on its own, since I can manually select the cells and it will average them.

The problem comes when some of those cells do not contain a number. Some are blank, and some have an IF formula that causes them to say "N/A". I tried to put in an IF(ISNUMBER) formula but Excel didn't like me selecting individual cells instead of a range.

I'm looking for a formula that will allow me to average every 5th number, while excluding empty cells and cells that say "N/A", and, if there are no cells containing numbers to average, for it to say "N/A".

Can anyone help me with this? I can provide example spreadsheets if further clarification of what I mean is necessary.

Perhaps

=IFERROR(AVERAGE(IF((MOD(ROW($A$1:$A$100)-ROW($A$1),4)=0)*ISNUMBER($A$1:$A$100),$A$1:$A$100)),"N/A")
confirmed with CTRL + SHIFT + ENTER

where A1:A100 contain data points and average is of A1, A5, A9 etc...) - adjust as nec.

(If the above is not what you want I would suggest posting a sample file outlining setup and desired results based on sample data)

here an alternative

HTML Code:

=AVERAGE(IF(--(ISNUMBER(A1:A25))*--(MOD(ROW(A1:A25),5)=0),A1:A25))

Given use of XL2007 and having now seen your file - perhaps:

C23: =IF(SUMIF($B$3:$B$21,"Offer",C$2:C$20),AVERAGEIF($B$3:$B$21,"Offer",C$2:C$20),"N/A")
copied across to E23 (use of 3:21/2:20 is deliberate)

C24:
=IF(SUMIF($B$2:$B$21,"Variance",C$2:C$21),AVERAGEIF($B$2:$B$21,"Variance",C$2:C$21),"N/A")
copied across to E24

C24:E24 copied to C25:E25 but change "Variance" to "Difference due to quote"

Originally Posted by rachelar

I do have one question though: when I enter the formula into D25 (making the necessary alteration) I get a different total to the one I get when I manually average the relevant cells.

Did you change both references to "Variance" ?
The correct formula would generate £19.50


On aside please don't quote posts in full - clutters the board - use the New Post / Quick Reply or quote only those parts necessary for your subsequent post to make sense

Originally Posted by DonkeyOte

Did you change both references to "Variance" ?
The correct formula would generate £19.50

No I didn't, thanks for spotting that. The formula seems to work fine, then, I'll pop it into the full spreadsheet. Thanks for all your work.

On aside please don't quote posts in full - clutters the board - use the New Post / Quick Reply or quote only those parts necessary for your subsequent post to make sense

Sorry

Just to check, is there a shortcut to average every other/5th/etc row in a range, not worrying about ignoring errors and blank cells?

If you can, use AVERAGEIF(S) ... this is viable here because the items to Average have common denotation and you're running XL2007.

Pre XL2007 then if the denotation holds true use SUMIF/COUNTIF ... if that's not the case then the "simplest" single cell solution is to use a MOD based Array as previously outlined.