Excel: Simple Average Month Data Formula Needed

For some reason, I can't build a formula (no arrays, please) in the bright yellow highlighted cells that calculate the average "Day Eff%" -- as listed in column K of the attached spreadsheet.

For instance, in cell N12, the return calculation should be 57.4%. In cell N13, it should be 65.6%.

Any help would be appreciated. Thanks!
Workbook3.xlsx
Tim JackoboiceOwnerAsked:
Who is Participating?
 
gowflowCommented:
for the DIVO error also on mine there is this error replace in my formula in N12 by this
=IFERROR(AVERAGEIF($O$4:$O$370,M12,$K$4:$K$370),"")

and drag it down.
gowlfow
0
 
ovi_mihaiCommented:
N12 =AVERAGEIF(O$4:O$370,"1",K$4:K$370)
N13 =AVERAGEIF(O$4:O$370,"2",K$4:K$370) ...

To be able just to drag it down you could replace the numbers 1,2, .. with  - CELL("row", N12)-11   (CELL("row", A1) would work too, the ideea is just to create an ascending number)
So
N12 =AVERAGEIF(O$4:O$370,CELL("row", N12)-11,K$4:K$370)
when you'll drag it by the right-down corner down will have the right formula on all cells
0
 
gowflowCommented:
or simply if you need the month the I have converted your Jan,Feb etc .. to Month(1) then the rest is that cell +1 so you get numerics.

Then we compare the formula from Col O to average K4:K370 which gives this in N12
=AVERAGEIF($O$4:$O$370,M12,$K$4:$K$370)
and you drag it down till DEC or Cell N23

For sure the references in M12 to M23 are now 1,2,3 ... 12 and no more Jan, Feb, Mar etc.

Look at the attached workbook.
gowflow
Workbook3-V01.xlsx
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Tim JackoboiceOwnerAuthor Commented:
You guys are good!

One quick followup question ... how do I get the cells that show #DIV/0! right now, to appear blank instead? It looks kind of bad. I'm assuming it's a simple number format that eliminates returning a division by zero error?
0
 
ovi_mihaiCommented:
You can use IFERROR()
http://www.techonthenet.com/excel/formulas/iserror.php

=IFERROR( AVERAGEIF(O$4:O$370,"3",K$4:K$370), "" )
If there is an error will put blank. You'd better use 0..
0
 
Tim JackoboiceOwnerAuthor Commented:
ovi mihai:

That works, too. Thanks!!

btw, that's not the "drag down" formula, though, correct? (meaning I will have to manually insert the numbers 1, 2, 3 ... etc. for each of the cells, for the individual month's formulas.)
0
 
gowflowCommented:
Did you check my solution ???
Nothing to insert here is the formula: in N12 you drag down
=AVERAGEIF($O$4:$O$370,M12,$K$4:$K$370)

and in M12 you put 1 and in M13 you put =M12+1 and drag down.

that's it.
gowflow
0
 
Tim JackoboiceOwnerAuthor Commented:
gowflow: Yes. I did check your solution, and it worked. I was instead referring to ovi mihai's inclusion of the IFERROR condition, as a part of his solution, which did not provide for a drag-down ability.
0
 
gowflowCommented:
ok ic.
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.