Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel: Simple Average Month Data Formula Needed

Posted on 2015-02-08
9
Medium Priority
?
109 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
[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 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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: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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.

722 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