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
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
Highlighted

Hi @cmdo04

 

use averageif formula.

 

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

 

regards,

Naveen

Related Conversations
SharePoint 2013 LIst Filters (by latest date)
dmphil in SharePoint on
0 Replies
Pivot table
gabriellerocha in Excel on
5 Replies
Leap year formula
Hattsoff in Excel on
4 Replies