+ Reply to Thread
Results 1 to 11 of 11

Computing average to be linked in another sheet

  1. #1
    Forum Contributor
    Join Date
    07-26-2016
    Location
    Philippines
    MS-Off Ver
    Google Sheets
    Posts
    124

    Computing average to be linked in another sheet

    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.
    Attached Files Attached Files
    Last edited by Simply_Me; 03-27-2019 at 10:36 AM. Reason: Change of Thread Title

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Average(Offset) problem

    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

  3. #3
    Forum Contributor
    Join Date
    07-26-2016
    Location
    Philippines
    MS-Off Ver
    Google Sheets
    Posts
    124

    Re: Computing average to be linked in another sheet

    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.

  4. #4
    Forum Contributor
    Join Date
    07-26-2016
    Location
    Philippines
    MS-Off Ver
    Google Sheets
    Posts
    124

    Re: Computing average to be linked in another sheet

    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.

  5. #5
    Forum Contributor
    Join Date
    07-26-2016
    Location
    Philippines
    MS-Off Ver
    Google Sheets
    Posts
    124

    Re: Computing average to be linked in another sheet

    I am looking at doing something like the one below but that is not working correctly.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Simply_Me; 03-27-2019 at 11:24 AM.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Computing average to be linked in another sheet

    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.

  7. #7
    Forum Contributor
    Join Date
    07-26-2016
    Location
    Philippines
    MS-Off Ver
    Google Sheets
    Posts
    124

    Re: Computing average to be linked in another sheet

    Quote Originally Posted by jason.b75 View Post
    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.
    Last edited by Simply_Me; 03-27-2019 at 11:32 AM.

  8. #8
    Forum Contributor
    Join Date
    07-26-2016
    Location
    Philippines
    MS-Off Ver
    Google Sheets
    Posts
    124

    Re: Computing average to be linked in another sheet

    Quote Originally Posted by jason.b75 View Post
    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 cannot change the descriptions on both sheets because they are designed that way.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Computing average to be linked in another sheet

    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.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Computing average to be linked in another sheet

    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.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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.

  11. #11
    Forum Contributor
    Join Date
    07-26-2016
    Location
    Philippines
    MS-Off Ver
    Google Sheets
    Posts
    124

    Re: Computing average to be linked in another sheet

    Thank you for your time and effort sir jason.b75. Problem solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-17-2018, 09:45 PM
  2. Average offset
    By Jack11 in forum Excel General
    Replies: 5
    Last Post: 06-16-2017, 02:24 AM
  3. Offset moving average
    By Ksenia in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-27-2013, 09:40 AM
  4. [SOLVED] Average values using offset
    By NeoFlex in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2013, 09:54 AM
  5. AVERAGE with OFFSET
    By 9:46 AM in forum Excel General
    Replies: 2
    Last Post: 05-11-2012, 05:35 PM
  6. Average offset help.
    By gaikee in forum Excel General
    Replies: 11
    Last Post: 04-29-2012, 08:58 PM
  7. Average + Offset?
    By 4am in forum Excel General
    Replies: 2
    Last Post: 12-15-2010, 02:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1