SOLVED
Home

Average set of values if 2 criteria are met

%3CLINGO-SUB%20id%3D%22lingo-sub-1192377%22%20slang%3D%22en-US%22%3EAverage%20set%20of%20values%20if%202%20criteria%20are%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1192377%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20find%20a%20way%20to%20average%20all%20values%20into%20a%20single%20cell%20if%20the%20column%20contains%20a%20specific%20text%20for%20each%20one%20of%20the%20students.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EStudent%20A%20has%20-%206%20values%20for%20SCI.5.1.A%2C%20that%20need%20to%20be%20averaged%20into%20a%20single%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-%26nbsp%3B4%20values%20for%20SCI.5.1.B%2C%20that%20need%20to%20be%20averaged%20into%20another%20single%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B-%26nbsp%3B4%20values%20for%20SCI.5.2.A%2C%20that%20need%20to%20be%20averaged%20into%20another%20single%20cell.%3C%2FP%3E%3CP%3Eand%20so%20on...%3C%2FP%3E%3CP%3EI%20need%20to%20do%20this%20for%20each%20one%20of%20the%20students.%3C%2FP%3E%3CP%3EBTW%2C%20the%20number%20of%20values%20for%20each%20column%20title%20will%20increase%20as%20the%20year%20progresses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20trying%20to%20use%20AVERAGEIFS%2C%20with%20the%20column%20name%20(i.e.%20SCI.5.1.A)%20as%20criteria1%2C%20and%20student%20name%20as%20criteria2%20but%20I'm%20not%20sure%20how%20I%20can%20define%20the%20range%20to%20be%20averaged%2C%20as%20the%20ranges%20to%20meet%20criteria%20are%20different...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20made%202%20sheets%2C%20%22Turn%20this%22%20is%20where%20all%20the%20data%20is%20located%2C%20and%20%22Into%20this%22%20is%20how%20I%20would%20like%20to%20see%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20help%20and%20time!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1192377%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-1192571%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20set%20of%20values%20if%202%20criteria%20are%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1192571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F567177%22%20target%3D%22_blank%22%3E%40jtorrens%3C%2FA%3E%26nbsp%3BA%20possible%20solution%20is%20demonstrated%20in%20the%20attached%20workbook.%20It%20dynamically%20determines%20the%20first%20and%20last%20column%20in%20your%20score%20listing.%20All%20you%20need%20to%20do%20is%20expand%20your%20summary%20as%20more%20courses%20are%20added.%20I've%20used%20a%20helper%20column%20in%20D%20to%20achieve%20this.%20Hide%20the%20column%20by%20pressing%20the%20group%20button%20at%20the%20top%20(and%20vice%20versa).%20Note%20that%20the%20blue%20shaded%20cells%20should%20remain%20empty%20to%20guarantee%20correct%20calculations.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197518%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20set%20of%20values%20if%202%20criteria%20are%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much%20Riny!!%3C%2FP%3E%3CP%3EI%20finally%20finished%20playing%20around%20with%20it%20and%20your%20solution%20was%20exactly%20what%20I%20was%20looking%20for!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'm trying to find a way to average all values into a single cell if the column contains a specific text for each one of the students.

 

For example:

Student A has - 6 values for SCI.5.1.A, that need to be averaged into a single cell.

                       - 4 values for SCI.5.1.B, that need to be averaged into another single cell.

                       - 4 values for SCI.5.2.A, that need to be averaged into another single cell.

and so on...

I need to do this for each one of the students.

BTW, the number of values for each column title will increase as the year progresses.

 

I've been trying to use AVERAGEIFS, with the column name (i.e. SCI.5.1.A) as criteria1, and student name as criteria2 but I'm not sure how I can define the range to be averaged, as the ranges to meet criteria are different...

 

I've made 2 sheets, "Turn this" is where all the data is located, and "Into this" is how I would like to see it.

 

Thank you so much for help and time!!!

2 Replies
Highlighted
Solution

@jtorrens A possible solution is demonstrated in the attached workbook. It dynamically determines the first and last column in your score listing. All you need to do is expand your summary as more courses are added. I've used a helper column in D to achieve this. Hide the column by pressing the group button at the top (and vice versa). Note that the blue shaded cells should remain empty to guarantee correct calculations.

Highlighted

@Riny_van_Eekelen Thank you so much Riny!!

I finally finished playing around with it and your solution was exactly what I was looking for!!

Related Conversations
Pivot table
gabriellerocha in Excel on
5 Replies
Excel If Functions
Mfouad2255 in Excel on
10 Replies
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
2 lists, 1 if
Bartosz Heller in Excel on
4 Replies