SOLVED
Home

Data Averaging Question

%3CLINGO-SUB%20id%3D%22lingo-sub-819322%22%20slang%3D%22en-US%22%3EData%20Averaging%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819322%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%20new%20forum%20user%20here%20with%20basic%20excel%20knowledge%20seeking%20some%20help%20regarding%20data%20averaging.%3C%2FP%3E%3CP%3EI%20have%20two%20columns%20with%20systolic%20blood%20pressure%20values%20and%20I%20want%20averages%20for%20each%20individual%20column.%20However%2C%20I%20want%20to%20exclude%20rows%20that%20don't%20have%20values%20in%20both%20columns.%20The%20list%20will%20be%20very%20long%2C%20so%20ideally%20I%20wouldn't%20have%20to%20click%20individual%20cells.%20Can%20someone%20please%20recommend%20a%20method%3F%20Thanks%20in%20advance!%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%20156px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128135i662388FCC46F75FD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Data%20Averaging%20Question.png%22%20title%3D%22Data%20Averaging%20Question.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-819322%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-819341%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Averaging%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819341%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397152%22%20target%3D%22_blank%22%3E%40eseay212%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyou%20can%20use%20the%20AverageIFS%20function%20for%20that.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAVERAGEIFS(B%3AB%2CB%3AB%2C%22%26gt%3B0%22%2CC%3AC%2C%22%26gt%3B0%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20plain%20words%3A%20average%20the%20values%20in%20column%20B%20for%20all%20cells%20where%20column%20B%20is%20greater%20than%200%20AND%20column%20C%20is%20greater%20than%200.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20post%20average%2C%20the%20formula%20would%20then%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAVERAGEIFS(C%3AC%2CB%3AB%2C%22%26gt%3B0%22%2CC%3AC%2C%22%26gt%3B0%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819361%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Averaging%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819361%22%20slang%3D%22en-US%22%3EThis%20was%20spot%20on%20--%20thank%20you%20for%20the%20quick%2C%20concise%20and%20helpful%20answer%20to%20my%20question.%3C%2FLINGO-BODY%3E
eseay212
New Contributor

Hello everyone, new forum user here with basic excel knowledge seeking some help regarding data averaging.

I have two columns with systolic blood pressure values and I want averages for each individual column. However, I want to exclude rows that don't have values in both columns. The list will be very long, so ideally I wouldn't have to click individual cells. Can someone please recommend a method? Thanks in advance!

 

Data Averaging Question.png

 

 

2 Replies
Solution

Hello @eseay212 ,

 

you can use the AverageIFS function for that.

 

=AVERAGEIFS(B:B,B:B,">0",C:C,">0")

 

In plain words: average the values in column B for all cells where column B is greater than 0 AND column C is greater than 0.

 

For the post average, the formula would then be

 

=AVERAGEIFS(C:C,B:B,">0",C:C,">0")

This was spot on -- thank you for the quick, concise and helpful answer to my question.
Related Conversations