• 470K Members
• 4,742 Online
• 568K Conversations

## Not sure of what formula to use or the best way to do this

Occasional Visitor

# Not sure of what formula to use or the best way to do this

Hi;

I am trying to automatically give an average over several work sheets (tabs) but only if there is a value in the cell.  So if I have 10 sheets and I have a value in a specific cell on 4 sheets then I want to get an average for those 4 sheets only.  If I add a value to another sheet then it will be the average over 5 sheets.  But it won't be the average over all 10 sheets unless there is a value in all 10.

An example would be that in a cell on the sheets I have values of 2, 4, 6, 0, 0, 0, 0, 0, 0, 0.  I want to see the average be 4 ((2+4+6)/3) and not 1.2 ((2+4+6)/10).  And if I added the value of 8 onto another sheet the average would automatically become 5 and not 2.

Is there a formula for this type of thing and how would I apply it.

Thanks

2 Replies

# Re: Not sure of what formula to use or the best way to do this

Check out:  =averageif()  & averageifs()

# Re: Not sure of what formula to use or the best way to do this

Let’s assume that the 10 sheets are named Jan to Oct. I haven’t tested, but you can try, this formula:
=SUM(‘???’!A4)/
SUMPRODUCT(—(‘???’!A4<>0))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies