Average Every Second Line of an Array

[Bronson84]

I am trying to make a function to average every second line of an area. I can get the sum function to work, but the average function assumes every empty cell is a 0, and it messes up the average. I have also tired AverageIF() but i only get errors. Any help with this would be greatly appreciated.

This is what i have right now:

{ =AVERAGE(((IF(MOD(ROW(I$13:I$313),2)=0,I$13:I$313,"")))) }


Thanks

Bronson

[JBeaucaire]

Try this array alternate:

=AVERAGE(IF((MOD($I$13:$I$313,2)=0)*($I$13:$I$313<>""), $I$13:$I$313))

[clabulis]

Try this (non-array formula)

=SUMPRODUCT(I$13:I$313*(MOD(ROW(I$13:I$313),2)=0))/INT(ROWS(I$13:I$313)/2)

[Bronson84]

Thanks for the help, I am still getting the Error "Value" but i will play round with these alternative equations to see if i can fix it.

Thanks again for the help

[Jonmo1]

If the issue is really about ignoring blanks, and you're saying every second line because every other row is blank due to a design choice...

Then how about

=SUM(I$13:I$313)/COUNT(I$13:I$313)

[Jonmo1]

but the average function assumes every empty cell is a 0, and it messes up the average.

That's actually not true, the AVERAGE function ignores Text and Blanks.
Maybe your blanks are not really blank.
They might actually be 0, but you have 0's hidden through either custom formatting or Excel Options.

If that's the case, you could do

=AVERAGEIF(I$13:I$313,"<>0")

[Bronson84]

Every second line does an average of the last 4 lines. so it is not blank. however i want an average of all of the values, (not including the running 4 average on every second line. Yes i believe there are hidden 0's, from when i run my array formula.

[Jonmo1]

Can you post a sample book?

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Bronson84]

Here is a copy of the workbook I am working on. Any addition help would be greatly appreciated.

Thank you

Bronson.

[FDibbins]

This will work for row 23 onwards....
=IF(C22="","",AVERAGEIF(A16:A22,"<>Ave",C16:C22))

You can adjust that range slightly for 21, 19 etc

[Bronson84]

Awesome

Thank you very much