Home

Average computation that ignores cells with formula and less than values

%3CLINGO-SUB%20id%3D%22lingo-sub-321516%22%20slang%3D%22en-US%22%3EAverage%20computation%20that%20ignores%20cells%20with%20formula%20and%20less%20than%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321516%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI'm%20currently%20working%20on%20a%20score%20tracker%20where%20I%20input%20a%20number%2Fscore%20per%20week.%20At%20the%20end%20of%20the%20month%2C%20I%20need%20to%20get%20the%20weekly%20average%20but%20valid%20scores%20than%20can%20be%20included%20in%20this%20average%20are%20only%20those%20values%20from%2010%20pts%20and%20above.%20(Please%20note%20that%20I%20would%20still%20need%20to%20reflect%20values%20less%20than%2010%20in%20the%20report).%20The%20same%20is%20done%20on%20the%20following%20month.%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20at%20the%20end%20of%20the%20year%2C%20I%20would%20also%20need%20to%20reflect%20a%20weekly%20average%20taking%20into%20account%20all%20weeks%20with%20scores%20above%2010%20pts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20compute%20for%20the%20weekly%20average%20of%20the%20entire%20row%20but%20excluding%201)%20all%20cells%20with%20formulas%20(monthly%20ave.)%20and%202)%20all%20cells%20with%20values%20less%20than%2010%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69150i41359037D1320FCF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-321516%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-321532%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20computation%20that%20ignores%20cells%20with%20formula%20and%20less%20than%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321532%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F269248%22%20target%3D%22_blank%22%3E%40cmdo04%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Euse%20averageif%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EW1%3C%2FTD%3E%3CTD%3EW2%3C%2FTD%3E%3CTD%3EW3%3C%2FTD%3E%3CTD%3EW4%3C%2FTD%3E%3CTD%3Eavg.%3C%2FTD%3E%3CTD%3EW1%3C%2FTD%3E%3CTD%3EW2%3C%2FTD%3E%3CTD%3EW3%3C%2FTD%3E%3CTD%3EW4%3C%2FTD%3E%3CTD%3EW5%3C%2FTD%3E%3CTD%3Eavg.%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E50%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E35.67%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E21%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E25.50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAVERAGEIF(A2%3AD2%2C%22%26gt%3B10%22)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAVERAGEIF(F2%3AJ2%2C%22%26gt%3B10%22)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eregards%2C%3C%2FP%3E%3CP%3ENaveen%3C%2FP%3E%3C%2FLINGO-BODY%3E
cmdo04
Occasional Visitor

Hi!

I'm currently working on a score tracker where I input a number/score per week. At the end of the month, I need to get the weekly average but valid scores than can be included in this average are only those values from 10 pts and above. (Please note that I would still need to reflect values less than 10 in the report). The same is done on the following month. 

Now at the end of the year, I would also need to reflect a weekly average taking into account all weeks with scores above 10 pts.

 

How do compute for the weekly average of the entire row but excluding 1) all cells with formulas (monthly ave.) and 2) all cells with values less than 10?

 

Capture.PNG

1 Reply

Hi @cmdo04

 

use averageif formula.

 

W1W2W3W4avg.W1W2W3W4W5avg.
502334635.673021910325.50
    AVERAGEIF(A2:D2,">10")     AVERAGEIF(F2:J2,">10")

 

regards,

Naveen

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 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
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies