Hello. I have a budget spreadsheet that I am working on, and I would like to average certain values from every month, but only if they are greater than zero (to exclude months we haven't reached yet), but there are also cells with data that are in between the cells that I need to average. For example, I need to average cells D8,D11,D14,D17,D20,D23,D26,D29,D32,D35,D38,D41, and only these cells as long as each cell is greater than zero.
Thanks a lot for the help,
Andrew
it can be done with an pivot table.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I actually haven't used a pivot table before. Will that require me to devote additional space/cells to accomplish this? If so, I don't really have a good place to put it. If not, could you tell me how I would set that up?
Thanks,
Andrew
Have you got any consistent "row labels" in an adjacent column? If so that might make it easier to average, otherwise try this formula
=SUM(D8,D11,D14,D17,D20,D23,D26,D29,D32,D35,D38,D41)/MAX(1,INDEX(FREQUENCY((D8,D11,D14,D17,D20,D23,D26,D29,D32,D35,D38,D41),0),2))
Audere est facere
Here is what I have. I do have row labels for the months, but obviously each month has a different label.
example.xlsx
Try using AVERAGEIFS like this
=AVERAGEIFS(D8:D41,$B8:$B41,"*2013",D8:D41,">0")
Ok, that worked great, thanks a lot for the help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks