Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel: Simple Average Month Data Formula Needed

Posted on 2015-02-08
9
Medium Priority
?
114 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:Tim Jackoboice
  • 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 31

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:Tim Jackoboice
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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
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
 

Author Comment

by:Tim Jackoboice
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 31

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:Tim Jackoboice
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 31

Expert Comment

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

Accepted Solution

by:
gowflow earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Viewers will learn various types of data validation for different data types in Excel 2013.
Video by: Zack
Viewers will learn about various customizable options in Excel 2013.

927 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