Solved

Excel: Simple Average Month Data Formula Needed

Posted on 2015-02-08
9
98 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 30

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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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: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 30

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 30

Expert Comment

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

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
google sheets, auto insert date into cell when data is moved 11 89
Automatic sort formula 8 69
macro to compare 2 Excel columns. 14 42
Excel Index/Match issue 4 22
: 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…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Video by: Zack
Viewers will learn about using Excel in a browser with Excel Online.
Viewers will learn the basics of using filtering and sorting in Excel 2013.

710 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