LinkBack URL
About LinkBacks
I want to get the average of ratings of each teacher from "Summary" sheet to be put in the "Efficiency" sheet.
Please Login or Register to view this content.Please Login or Register to view this content.
I want to get the average of ratings of each teacher from "Summary" sheet to be put in the "Efficiency" sheet.
Please Login or Register to view this content.Please Login or Register to view this content.
Last edited by Simply_Me; 03-27-2019 at 10:36 AM. Reason: Change of Thread Title
That formula is too messy to figure out what you want. Please explain what it should be doing.
Last edited by jason.b75; 03-27-2019 at 10:47 AM. Reason: title change request removed
To clarify what I want to achieve...I need to link the ratings (average of the ratings) from "Summary" sheet and to "Efficiency" sheet of each of the teachers.
I am aware that I need to use Match(), Offset(), Average(), INT() but I can't put them all together.
Last edited by Simply_Me; 03-27-2019 at 10:39 AM.
How do I put together the two formulas below to compute for the average?
Please Login or Register to view this content.Please Login or Register to view this content.
I am looking at doing something like the one below but that is not working correctly.
Please Login or Register to view this content.
Last edited by Simply_Me; 03-27-2019 at 11:24 AM.
I think that you're ovecomplicating it with all of those functions.
Your formula in G23 is giving the average for Alice, Bernice, Camille, Divine and Erick, but the layout of your summary sheet suggests that it should only be for Alice.
If your sheets were better planned, (i.e. if the descriptions in column B of the efficiency sheet were the same as those in column B of the summary sheet) you could probably do it with less functions.
I redesigned my sheets to have the same number of columns and had it attached in my previous post.Originally Posted by jason.b75
Last edited by Simply_Me; 03-27-2019 at 11:32 AM.
I cannot change the descriptions on both sheets because they are designed that way.
That looks worse
Take a look at this.
On the summary sheet, I've deleted column C, you don't need 2 columns merged there, just make column B wider. Merged cells are the root of all evil and should be avoided at all costs.
I've also inserted another row at row 14, which contains the average for the 2 rows above, along with a heading in column B which is identical to the one in B23 of the efficiency sheet.
Then I've entered this formula into F23:J23, which pulls the average that you want.
=IFERROR(--TEXT(INDEX(Summary!$C$3:$L$18,MATCH($B23,Summary!$B$3:$B$18,0),MATCH($A$1,Summary!$C$2:$L$2,0)),"[>="&(F16+1)&"] ;[<"&F16&"] ;0.00;"),"")
Looking at the formula above, the section in Bold Black text covers the whole of the numeric part of the table in the summary sheet.
The bit in Bold Red text finds the column that matches to the name "Alice" in A1.
The bit in Normal Red text finds the row that matches to "Attendance and Punctuality" in B23.
The bit at the beginning and end in Normal Black text serves the same purpose as your INT COLUMN calculations, which were the failing point of your formula.
In addition, the actaul ranges used are in the formula, so you don't have to guess where the results are coming from.
I've been trying a couple of things in your original layout. This appears to work when entered into F23, then filled to J23 and copied to P23:T23.As there is no consistent factor to identify which rows should be used, you will need to edit that part for the other categories if you inted to use it for other rows as well.Formula:
Please Login or Register to view this content.
Thank you for your time and effort sir jason.b75. Problem solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks