+ Reply to Thread
Results 1 to 2 of 2

Help with formula searching for 2nd lowest value, then return value in next column

  1. #1
    Registered User
    Join Date
    10-29-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Help with formula searching for 2nd lowest value, then return value in next column

    I've attached a screen shot of what I'm trying to do:

    As part of a larger spreadsheet, I have 7 columns of data. I want to choose the 3 smallest values in A7:A13 and average them. I can do this no problem, and the formula has been put in A2 with the correct result
    [=AVERAGE((SMALL(A7:A13,1)),(SMALL(A7:A13,2)),(SMALL(A7:A13,3))]

    I then want a formula to select the three lowest in ColA, and then give me the average of the corresponding values in ColB. Then I need to do the same for the rest of the columns.

    For example, A9, A11 & A13 have the smallest values, so I want the average of B9, B11, & B13 (but with a formula so I don't have to chose the smallest values)

    Thanks for your help!!!
    Attached Files Attached Files

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Help with formula searching for 2nd lowest value, then return value in next colum

    =average(vlookup(small(a7:a13,1),a7:b13,2,false),vlookup(small(a7:a13,2),a7:b13,2,false),vlookup(small(a7:a13,3),a7:b13,2,false))
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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