Solved

Excel: Simple Average Month Data Formula Needed

Posted on 2015-02-08
9
90 Views
Last Modified: 2015-02-09
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
0
Comment
Question by:Cactus1994
  • 4
  • 3
  • 2
9 Comments
 
LVL 3

Expert Comment

by:ovi_mihai
ID: 40596887
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40596912
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
 

Author Comment

by:Cactus1994
ID: 40597089
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
 
LVL 3

Expert Comment

by:ovi_mihai
ID: 40597123
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Cactus1994
ID: 40597146
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40597295
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
 

Author Comment

by:Cactus1994
ID: 40597560
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40597778
ok ic.
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40598045
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we talk about DevOps toolchains, I sometimes wonder how many people really get what we’re talking about. I don’t know if it’s just semantics or tone or something else, but sometimes I think it just sounds like buzzword sausage. So it’s always …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn what comprises a theme in Excel 2013, as well as how to customize them.
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now