So I recently switched back to Excel after getting fed up with the speed of Libre Calc. In opening one of the files, it did not copy the formula over and instead just basically did a paste values (all I get is the numbers and not the formulas). When I try the formula, I get an error (There's a problem with this formula...). The following is the formula:
=ROUND(AGGREGATE(1,6,HLOOKUP($Q$2,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$3,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$4,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$5,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$6,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$7,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$8,Ratings!$B$10:$AE$4010,A11,0))+IF(B11="SO",Ratings!AH11,IF(B11="JR",(Ratings!AH11+Ratings!AI11),IF(B11="SR",(Ratings!AH11+Ratings!AI11+Ratings!AJ11),0))),0)
Basically I want the "Pace" rating for every player (L11-L16 in the test file) to be an average of the traits listed in L2-L8 for that specific player, which are referenced on the Ratings Sheet.
So for example, if a player has a Speed of 39, an Agility of 48 and Endurance of 56 on the Ratings sheet, his "Pace" on the Soccer D sheet should be the average of those three.
And then if I were to decide that "Pace" should include a fourth trait, for example "Flexibility", I want to be able to type that into L5 and have the formulas in L11 and following now equal the average of the player's Speed, Agility, Endurance and Flexibility.
Does that make sense? I had this working in Libre Calc. When I was figuring it out at the time, I seem to recall that there may have been an Excel formula not available in Calc that I thought may have been a better option but the name of the formula escapes me at this time.
=ROUND(AGGREGATE(1,6,HLOOKUP($Q$2,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$3,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$4,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$5,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$6,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$7,Ratings!$B$10:$AE$4010,A11,0),HLOOKUP($Q$8,Ratings!$B$10:$AE$4010,A11,0))+IF(B11="SO",Ratings!AH11,IF(B11="JR",(Ratings!AH11+Ratings!AI11),IF(B11="SR",(Ratings!AH11+Ratings!AI11+Ratings!AJ11),0))),0)
Basically I want the "Pace" rating for every player (L11-L16 in the test file) to be an average of the traits listed in L2-L8 for that specific player, which are referenced on the Ratings Sheet.
So for example, if a player has a Speed of 39, an Agility of 48 and Endurance of 56 on the Ratings sheet, his "Pace" on the Soccer D sheet should be the average of those three.
And then if I were to decide that "Pace" should include a fourth trait, for example "Flexibility", I want to be able to type that into L5 and have the formulas in L11 and following now equal the average of the player's Speed, Agility, Endurance and Flexibility.
Does that make sense? I had this working in Libre Calc. When I was figuring it out at the time, I seem to recall that there may have been an Excel formula not available in Calc that I thought may have been a better option but the name of the formula escapes me at this time.
