+ Reply to Thread
Results 1 to 9 of 9

Averaging ''Names'' to columns in a range.

  1. 04-11-2020, 12:08 PM #1
    tkred123
    tkred123 is offline
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    9

    Averaging ''Names'' to columns in a range.

    Afternoon All

    I've got a formula that needs solving(ps the one i'm going to post is totally hypothetical)

    Basically I want to find a way of averaging each persons number, I know some names are in different columns but i want a way where i can get the name of the person and link it up to the column(the number)

    I cannot find a formula that can do this.

    Please can anyway help me.

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-11-2020, 12:43 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Averaging ''Names'' to columns in a range.

    Welcome to the forum.

    Please fill in the version of Excel that you are using in your forum user profile. Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 04-11-2020, 12:46 PM #3
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Averaging ''Names'' to columns in a range.

    If your numbers are all 1 then

    Formula for E17

    =COUNTIF($D$5:$K$12,D17)


    Otherwise formula for E17 entered using Ctrl Shift Enter

    =SUM(IF($D$5:$K$12 =D17,$E$5:$L$12,0))
    Last edited by mehmetcik; 04-11-2020 at 12:50 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  4. 04-11-2020, 01:09 PM #4
    tkred123
    tkred123 is offline
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    9

    Re: Averaging ''Names'' to columns in a range.

    Quote Originally Posted by mehmetcik View Post
    If your numbers are all 1 then

    Formula for E17

    =COUNTIF($D$5:$K$12,D17)


    Otherwise formula for E17 entered using Ctrl Shift Enter

    =SUM(IF($D$5:$K$12 =D17,$E$5:$L$12,0))
    Hi Mate,

    thank you but it doesn't work pal.

    I'm looking for the average not the sum

    Basically the ones I included are more of a test run than an actual document, the document I will be using will potentially have different numbers to represent the names.

    I'm looking for the average amount for the names mentioned on the sheet.
    Last edited by tkred123; 04-11-2020 at 01:20 PM.
    Reply With QuoteRegister To Reply
  5. 04-11-2020, 01:21 PM #5
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Averaging ''Names'' to columns in a range.

    Formula for E17 Entered using Ctrl Shift Enter

    =SUM(IF($D$5:$K$12 =D17,$E$5:$L$12,0))/COUNTIF($D$5:$J$12,D17)
    Last edited by mehmetcik; 04-11-2020 at 01:23 PM.
    Reply With QuoteRegister To Reply
  6. 04-11-2020, 02:57 PM #6
    tkred123
    tkred123 is offline
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    9

    Re: Averaging ''Names'' to columns in a range.

    Quote Originally Posted by mehmetcik View Post
    Formula for E17 Entered using Ctrl Shift Enter

    =SUM(IF($D$5:$K$12 =D17,$E$5:$L$12,0))/COUNTIF($D$5:$J$12,D17)
    Hi mate

    Ive tried this and it doesn't work at all.

    Just comes up with #value
    Reply With QuoteRegister To Reply
  7. 04-11-2020, 03:14 PM #7
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Averaging ''Names'' to columns in a range.

    you did not use Ctrl Shift Enter
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 04-11-2020, 03:49 PM #8
    tkred123
    tkred123 is offline
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    9

    Re: Averaging ''Names'' to columns in a range.

    done it, sorted.


    Thank you so very much!
    Reply With QuoteRegister To Reply
  9. 04-11-2020, 05:53 PM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Averaging ''Names'' to columns in a range.

    Please see post #2.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Averaging Columns
    By kenwall1965 in forum Excel General
    Replies: 5
    Last Post: 04-15-2015, 04:43 PM
  2. [SOLVED] Averaging a user input defined range based on values in columns
    By smit.etha in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-14-2014, 02:21 AM
  3. Macro needed: match names in different columns; then copy and paste range
    By msmith7113 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2013, 11:56 AM
  4. Macro to automatically give names to a range of columns in a repetitive pattern
    By shinobi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-13-2011, 06:30 AM
  5. Averaging columns using lookups
    By Dave_G in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2010, 02:30 AM
  6. Excel 2007 : Averaging multiple columns row by row
    By UtahDude in forum Excel General
    Replies: 1
    Last Post: 03-24-2010, 05:31 PM
  7. Averaging the Difference of Two Columns
    By mgaworecki in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-16-2008, 09:12 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1