+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    6

    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

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,595

    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.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  4. #4
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    6

    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.

  5. #5
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,595

    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.

  6. #6
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    6

    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

  7. #7
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,595

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

    you did not use Ctrl Shift Enter
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-11-2020
    Location
    stoke
    MS-Off Ver
    -
    Posts
    6

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

    done it, sorted.


    Thank you so very much!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,044

    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 to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1