Cactus1993
asked on
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
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
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$3 70)
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
Then we compare the formula from Col O to average K4:K370 which gives this in N12
=AVERAGEIF($O$4:$O$370,M12
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
ASKER
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?
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?
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..
http://www.techonthenet.com/excel/formulas/iserror.php
=IFERROR( AVERAGEIF(O$4:O$370,"3",K$
If there is an error will put blank. You'd better use 0..
ASKER
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.)
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.)
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$3 70)
and in M12 you put 1 and in M13 you put =M12+1 and drag down.
that's it.
gowflow
Nothing to insert here is the formula: in N12 you drag down
=AVERAGEIF($O$4:$O$370,M12
and in M12 you put 1 and in M13 you put =M12+1 and drag down.
that's it.
gowflow
ASKER
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.
ok ic.
gowflow
gowflow
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
N13 =AVERAGEIF(O$4:O$370,"2",K
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(
when you'll drag it by the right-down corner down will have the right formula on all cells