+ Reply to Thread
Results 1 to 12 of 12

Return Multiple Cell Addresses, that contain MAX value

  1. #1
    Registered User
    Join Date
    05-09-2013
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Return Multiple Cell Addresses, that contain MAX value

    I am creating a spreadsheet for my work. It is setup as a sales league table. An example is the are 7 teams. Cells G13 to G19 are used for sales, so each sale equals 5 points. What i am trying to do is find the MAX value of the cell range above and return a value of 8 (points) in another cell which will be added to that teams overall total. I have created a formula which returns the cell adress of the the cell that contains the max value and then used the IF function in another cell to allocate the 8 points.

    eg.

    Cell T20 contains the formula: CELL("address",OFFSET(G13,MATCH(MAX(G13:G19),G13:G19,0)-1,0))

    and

    Cell T13 to T19 contains the formula: =IF(T20="$G$13","8","0") (so if the above formula returns the cell address of G13 then 8 points are added to the cell T13 and added to the overall teams points.

    The above might not be the best way as im very rusty with excel, but it does work!!! UNTIL two teams have the same number of sales, so say G13 contains 5 sales (which is the highest value in the range) but G14 also has 5 sales. The 1st formula only returns the 1st cell with the MAX value and ignores and cells which have the same value. So it returns the cell address of $G$13 and allocates the 8 points but nothing happens the points for T14.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Return Multiple Cell Addresses, that contain MAX value

    Hi and welcome to the forum

    i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-09-2013
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Return Multiple Cell Addresses, that contain MAX value

    added file so view example and formulas. and help or changes welcome
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Return Multiple Cell Addresses, that contain MAX value

    How about this instead. In T13, copied down...
    =IF(G13=MAX($G$13:$G$19),8,0)

    Then you dont need anything else

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Return Multiple Cell Addresses, that contain MAX value

    Also, looking at the rest of your formulas, you dont need to use =sum() in N and O, you can just use =G13*5 etc
    if you put values in "", it makes then text, and you cant do cals with them. remove the ""...
    =IF(I13="","0",IF(I13<=800,"3",IF(I13<=900,"2",IF(I13<=1000,"0",IF(I13>1000,"-1")))))
    =IF(I13="",0,IF(I13<=800,3,IF(I13<=900,2,IF(I13<=1000,0,IF(I13>1000,-1)))))

  6. #6
    Registered User
    Join Date
    05-09-2013
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Return Multiple Cell Addresses, that contain MAX value

    Amazing, thanks alot, ive implimented this formula and the changes you advised, and it all works amazing. Just one last question...how would i change the =IF(G13=MAX($G$13:$G$19),8,0) formula you advised, to give a 0 value if the cells are blank. at the moment if all cells are blank everyone gets 8 points.

    Thanks Again.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Return Multiple Cell Addresses, that contain MAX value

    Try this,,,
    =IF(COUNT($G$13:$G$19)=0,0,IF(G13=MAX($G$13:$G$19),8,0))

  8. #8
    Registered User
    Join Date
    05-09-2013
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Return Multiple Cell Addresses, that contain MAX value

    Thanks alot! Very last thing, I promise. My Sales coloum is now working off a percentage instead of number of sales. I've to allocate different values depending on a range of percentages.
    * 0 points if the cell is empty
    * I've to give -2 points if the percentage is between 0.1% and 2.99%
    * If the percentage is between 3% and 3.99% i've to allocate 1 point
    * 2 points if between 4% and 4.99%
    * Finally, i've to give 5 points if the percentage is 5% or over.

    I have used the below formula, but it hasnt reurned the desired results.

    =IF(G13="",0,IF(AND(G13>=0.1,G13<=2.99),-2,IF(AND(G13>=3,G13<=3.99),1,IF(AND(G13>=4,G13<=4.99),2,IF(G13>=5,5)))))

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Return Multiple Cell Addresses, that contain MAX value

    Are we still working off the same sample file here? I dont see any "sales" column

  10. #10
    Registered User
    Join Date
    05-09-2013
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Return Multiple Cell Addresses, that contain MAX value

    I am using the same. On the example spreadsheet attached to this thread, it was column G "WIN (Sales):" the formula is in column N.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Return Multiple Cell Addresses, that contain MAX value

    Your latest pm said you fixed this? what did you do?

  12. #12
    Registered User
    Join Date
    05-09-2013
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Return Multiple Cell Addresses, that contain MAX value

    my head was pickled, trying to do my normal work and this at same time.
    used...
    =IF(G13="",0,IF(G13<=2.99%,-2,IF(G13<=3.99%,1,IF(G13<=4.99%,2,IF(G13>=5%,5)))))

    seems to do what i want it to, think i was over thinking the problem.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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