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