Average Non Contigious Cells

[Adam Schaefer]

I am trying to find the average of the non contigoius cells of column H. H4:H10, H12:H18, H20:H26, H28:H34, and H36:42 (Not the weekly averages). The formula should appear in H44. In addition, the formula needs to contain a stipulation for any cell equaling 0 to not be included. I've gotten close several times, but just cant seem to nail it down.

[benishiryo]

hi Adam. maybe:
=AVERAGEIFS(H4:H42,H4:H42,"<>0",B4:B42,"<>WEEK*")

row 44 doesn't seem like it needs merging of cells. you could just expand the row width for it. there is no issue for this scenario, but try to avoid merged cells if possible. lots of functions don't go well with it

[newdoverman]

This problem was already solved for you.

http://www.excelforum.com/excel-formulas-and-functions/969309-need-help-finishing-my-sheet.html#post3480228

[Adam Schaefer]

Thanks, I appreciate it. Additionally, is there a way to calculate the current % of sales against the budget everyday? Again, not just something that is a formula being used through each week, but everyday. B44 would be where the formula would go. Thanks in advance.

Adam

[benishiryo]

regarding the link to newdoverman's link, i couldn't access it. judging from the title of the link & by viewing forum post, i think it is:
http://www.excelforum.com/excel-form...ml#post3480228
seems to be the same address, so i'm not sure why it couldn't work for me. anyways, if it is indeed the same problem, please ask admins or moderators to combine the thread.

and like what i told you in PM, a new topic should be started in a new thread in future. try:
=SUMIFS(E4:E43,E4:E43,"<>0",B4:B43,"<>WEEK*")/SUMIFS(F4:F43,E4:E43,">0",B4:B43,"<>WEEK*")

[newdoverman]

For some reason, the link that I provided actually had http://http// as the start of the URL instead of just http:// and that caused the link to fail.