Home

Help with changing monthly average

%3CLINGO-SUB%20id%3D%22lingo-sub-440318%22%20slang%3D%22en-US%22%3EHelp%20with%20changing%20monthly%20average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-440318%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20really%20rusty%20on%20this%20after%20some%20years%20away%20from%20Excel.%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20attached%20sheet%20for%20example%2C%20Jan-Mar%20is%20complete%2C%20and%20able%20to%20average%20at%20bottom.%3C%2FP%3E%3CP%3EOnce%20I%20insert%20total%20for%20April%20and%20each%20month%20going%20forward%2C%20how%20do%20I%20get%20that%20average%20cell%20at%20bottom%20to%20auto%20recalc%20the%20new%20average%20using%20correct%20number%20of%20months%3F%26nbsp%3B%20%26nbsp%3BHopefully%20something%20automatic%2C%20but%20simple%3F%26nbsp%3B%20I%20don't%20know%20the%20exact%20terminology%20for%20this%20calculation%20type.%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-440318%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-440442%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20changing%20monthly%20average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-440442%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319623%22%20target%3D%22_blank%22%3E%40msnormand%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReplace%20the%20formula%20in%20cell%20B16%20with%20this%20one%3A%3C%2FP%3E%3CPRE%3E%3DB15%2FCOUNTA(B3%3AB14)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20using%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fcounta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ECOUNTA%3C%2FA%3E%20function%2C%20you%20can%20now%20calculate%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Eonly%20the%20average%20for%20months%20filled%20with%20values.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHope%20that%20helps%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
msnormand
Occasional Visitor

I'm really rusty on this after some years away from Excel. 

On attached sheet for example, Jan-Mar is complete, and able to average at bottom.

Once I insert total for April and each month going forward, how do I get that average cell at bottom to auto recalc the new average using correct number of months?   Hopefully something automatic, but simple?  I don't know the exact terminology for this calculation type.  Thanks!

1 Reply
Highlighted

Hi @msnormand,

 

Replace the formula in cell B16 with this one:

=B15/COUNTA(B3:B14)

 

By using the COUNTA function, you can now calculate only the average for months filled with values.

 

Hope that helps

 

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies