Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-467845%22%20slang%3D%22en-US%22%3ENot%20sure%20of%20what%20formula%20to%20use%20or%20the%20best%20way%20to%20do%20this%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467845%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20automatically%20give%20an%20average%20over%20several%20work%20sheets%20(tabs)%20but%20only%20if%20there%20is%20a%20value%20in%20the%20cell.%26nbsp%3B%20So%20if%20I%20have%2010%20sheets%20and%20I%20have%20a%20value%20in%20a%20specific%20cell%20on%204%20sheets%20then%20I%20want%20to%20get%20an%20average%20for%20those%204%20sheets%20only.%26nbsp%3B%20If%20I%20add%20a%20value%20to%20another%20sheet%20then%20it%20will%20be%20the%20average%20over%205%20sheets.%26nbsp%3B%20But%20it%20won't%20be%20the%20average%20over%20all%2010%20sheets%20unless%20there%20is%20a%20value%20in%20all%2010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20example%20would%20be%20that%20in%20a%20cell%20on%20the%20sheets%20I%20have%20values%20of%202%2C%204%2C%206%2C%200%2C%200%2C%200%2C%200%2C%200%2C%200%2C%200.%26nbsp%3B%20I%20want%20to%20see%20the%20average%20be%204%20((2%2B4%2B6)%2F3)%20and%20not%201.2%20((2%2B4%2B6)%2F10).%26nbsp%3B%20And%20if%20I%20added%20the%20value%20of%208%20onto%20another%20sheet%20the%20average%20would%20automatically%20become%205%20and%20not%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20for%20this%20type%20of%20thing%20and%20how%20would%20I%20apply%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-467845%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-467994%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20sure%20of%20what%20formula%20to%20use%20or%20the%20best%20way%20to%20do%20this%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322898%22%20target%3D%22_blank%22%3E%40slapic%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20out%3A%26nbsp%3B%20%3Daverageif()%26nbsp%3B%20%26amp%3B%20averageifs()%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-468085%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20sure%20of%20what%20formula%20to%20use%20or%20the%20best%20way%20to%20do%20this%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-468085%22%20slang%3D%22en-US%22%3ELet%E2%80%99s%20assume%20that%20the%2010%20sheets%20are%20named%20Jan%20to%20Oct.%20I%20haven%E2%80%99t%20tested%2C%20but%20you%20can%20try%2C%20this%20formula%3A%3CBR%20%2F%3E%3DSUM(%E2%80%98%3F%3F%3F%E2%80%99!A4)%2F%3CBR%20%2F%3ESUMPRODUCT(%E2%80%94(%E2%80%98%3F%3F%3F%E2%80%99!A4%26lt%3B%26gt%3B0))%3C%2FLINGO-BODY%3E
slapic
Occasional Visitor

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

@slapic 

Check out:  =averageif()  & averageifs()

Highlighted
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
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
12 Replies