+ Reply to Thread
Results 1 to 7 of 7

Find specific value in array which appears multiple times and average all relative cells

  1. #1
    Forum Contributor
    Join Date
    05-19-2012
    Location
    Croatia
    MS-Off Ver
    Excel 2007
    Posts
    200

    Find specific value in array which appears multiple times and average all relative cells

    Hi!

    I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.

    Example below: (Find value = 1 in A:A) ... 1 is found 4 times.
    A | B
    1 | 295
    3 | 123
    1 | 400
    5 | 425
    1 | 354
    1 | 400

    In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370


    Thank you!
    Last edited by nenadmail; 01-24-2014 at 03:59 PM.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find specific value in array which appears multiple times and average all relative cel

    With
    A1:B7 containing your posted data
    and
    D1: ...a number to find...1

    Since you have Excel 2007...try this:
    C1: =AVERAGEIF(A1:A10,D1,B1:B10)

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    05-19-2012
    Location
    Croatia
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: Find specific value in array which appears multiple times and average all relative cel

    I guess that's it, except for the round up to the nearest 10. yeah.

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find specific value in array which appears multiple times and average all relative cel

    Ooops! Forgot about the round-up part.
    Try this:
    Please Login or Register  to view this content.
    Better?

  5. #5
    Forum Contributor
    Join Date
    05-19-2012
    Location
    Croatia
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: Find specific value in array which appears multiple times and average all relative cel

    Perfect. Thanks!

  6. #6
    Forum Contributor
    Join Date
    05-19-2012
    Location
    Croatia
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: Find specific value in array which appears multiple times and average all relative cel

    I forgot... how do I mark this as "SOLVED"??

  7. #7
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Find specific value in array which appears multiple times and average all relative cel

    Thread_Tools (above)
    ...Select: 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. [SOLVED] Find how many times a value appears with a particular value within same row
    By SG56001235 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-20-2013, 12:12 PM
  2. [SOLVED] highlight when specific text appears multiple times in a range of cells - excel 2013
    By Onefynebarraza in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 10-14-2013, 09:06 AM
  3. Replies: 5
    Last Post: 02-06-2012, 10:40 AM
  4. Macro Loop – Find Specific Times, Extract Date, Average Values of Times
    By ExcelQuestFL in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-25-2010, 02:50 PM
  5. [SOLVED] Count names-how many times a specific name appears
    By Farrel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-14-2005, 04:05 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