Formula for Daily Average when the Columns are Monthly Totals

GSNexcel

GSNexcel

New member
Joined
Oct 20, 2015
Messages
8
Reaction score
0
Points
0
Hi,
I need a formula to find a daily average but my columns are monthly totals. I also need it to ignore the months that are zero, therefore I can't sum and divide by the number of days in the year. For example the monthly totals are Jan 10, Feb 12, March 0, April 41, May 0, June 15, July 62, August 5, September 0, October 24 - I need a daily average ignoring the monthly totals that are zero.

Thanks
 
Show us how your data are laid out (columns/rows). Are the dates real dates or just 3 character strings (you can find out by selecting a cell, say with Jan in it, and on the keyboard pressing Ctrl+1, choose General and click OK. Do you see a number now? You can undo all that by Ctrl+z.
Asuming 2015, you're looking for an average of 0.797, being 169/212 where 212 is 31+28+30+30+31+31+31 (non-zero months)?
 
Meter totals Jan
Meter totals Feb
Meter Totals Mar
Meter Totals April
26
0
15
24
15
21
0
3


 
Sorry, I'm having trouble posting. There are approximately 5,000 meters, some meters run all the time and some don't.
 
I think I was able to attach a snip of the file.....I hope
 

Attachments

  • meters.JPG
    meters.JPG
    78.4 KB · Views: 12
You' have a post count of 4 at the moment, one more post, and after that you should be able to attach a file - just one sheet, anonymise it if the data is sensitive.
By the way, is this a pivot table thingy in the picture? If so, where's it getting its data from? It may be easier to work out the averages from that.
Assuming not, you're after some 5k averages at the right of the table?
 
It is a table, the volumes are exported out of another program into excel and then I use a vlookup formula to match the volumes by the meter numbers. There is also a monthly average. Most everything in the spreadsheet is a formula. I'll see if I can attach a file after this post. I will have to delete some info and there are hidden sheets that it pulls info from.
 
The file is to large. I deleted a bunch and it's still to large
 
Put it in the cloud (a file sharing site such as box.net and many more) and link to it here?
 
I was able to attach a compress zip file with some of the info deleted
 

Attachments

  • 610 months - experiment -2.zip
    2.3 MB · Views: 11
I've replaced formulae like this:
=IF(ISNA(VLOOKUP(A:A,January!$A$2:$C$4355,3,FALSE)),"0",(VLOOKUP(A:A,January!$A$2:$C$4355,3,FALSE)))
with this:
=IF(ISNA(VLOOKUP(A:A,January!$A$2:$C$4355,3,FALSE)),0,(VLOOKUP(A:A,January!$A$2:$C$4355,3,FALSE)))
throughout the table because column K monthly averages were wrong (eg. see meter no. 89272) and column L wouldn't have worked.

likewise:
=IFERROR(AVERAGEIF(Table1[@[Meter Volumes January]:[Meter Volumes September]],">0"),"0")
to:
=IFERROR(AVERAGEIF(Table1[@[Meter Volumes January]:[Meter Volumes September]],">0"),0)

I added a row 2 containing number of days in each month (assumes 2015 for Feb).
Added a formula to column L which seems to be giving the right answers.
 

Attachments

  • ExcelGuru5041_610 months - experiment -2v2.zip
    2.2 MB · Views: 7
You must log in or register to reply here.
Back
Top