+ Reply to Thread
Results 1 to 15 of 15

comparing cell values to determine which is highest, then returning the name assocaited

  1. #1
    Registered User
    Join Date
    10-09-2019
    Location
    Luton, England
    MS-Off Ver
    365
    Posts
    13

    Angry comparing cell values to determine which is highest, then returning the name assocaited

    Hi All,

    I'm new to the forum, mainly because I have searched high and low across the interwebs and nothing is coming close to the problem I'm trying to work out

    This is the skinny:

    In essence, I have a ratio between different items (apples oranges bananas etc in the attached example to keep it simple) with a ratio set against them for each month of the year going back. This number is based upon 1000 being the benchmark. Each month it can either be above or below that number depending on its strength.

    What I’m looking for is the equation (I’m guessing some kind of complicated index formula) whereby it spits out which is the strongest and weakest fruit looking back on both 1, 4, and 10 years, for its particular month.

    Capture.PNG
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    Given that we are now in October, are you saying that for the 1-year section you are looking for the strongest in the row for October 2018 and the weakest for that row? For the 4-year section do you want to know the strongest and weakest for October 2018, 2017, 2016 and 2015 combined? You don't have enough data in your example file to look back 10 years.

    Please confirm that I have understood your requirements, or clarify them further.

    Pete

  3. #3
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    You can get the results you're showing by using the following formulas:

    Please Login or Register  to view this content.
    Copy across and down but I suspect that isn't quite what you're after given your 4 year / 10 year headings - what do they mean?

    WBD
    Office 365 on Windows 11, looking for rep!

  4. #4
    Registered User
    Join Date
    10-09-2019
    Location
    Luton, England
    MS-Off Ver
    365
    Posts
    13

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    Hi Pete,

    Yes, well sort of. It would actually look at Oct last year. The idea being is that on Nov 1st, I can see statistically, which 'fruit' has historically performed the best and worst during those periods (last year, Last 4 years, and last decade)

    As for the data set, that is simply an example I cobbled together; feel free to expand on it to be able to generate 10 years worth of past history.

    I hope that makes sense! Let me know if you need me to go into additional detail

  5. #5
    Registered User
    Join Date
    10-09-2019
    Location
    Luton, England
    MS-Off Ver
    365
    Posts
    13

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    Hey,

    For the 4 and 10 year, it would be the mean average of that month going back 4 or 10 years. That number would then be formulated against the others to determine which is the strongest/weakest over that period

  6. #6
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    OK. I added some helpers columns to calculate the mean for that month for 4/10 years which then helped with the other calculations. I've left them in the sheet for now but you could hide them as necessary. See attached.

    WBD
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-09-2019
    Location
    Luton, England
    MS-Off Ver
    365
    Posts
    13

    Smile Re: comparing cell values to determine which is highest, then returning the name assocaite

    Hi WideBoyDixon,

    I think you're pretty much on the nose with it! Thanks!

    I'm trying to copy it over to the actual sheet I'll be using and I keep getting errors (Attached).

    Can you see what I'm doing wrong? I think it is to do with the cells not being next to each other possibly, as its the Rat column for each entity which I'm looking

    Any ideas, and thanks again
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-09-2019
    Location
    Luton, England
    MS-Off Ver
    365
    Posts
    13

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    From what I can see, it has something to do with the range being non continuous, which is causing the headache... if that makes sense to you?

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    I presume that you are referring to cell K7, which is showing #N/A - it contains this formula:

    =INDEX($C$3:$F$3,MATCH(MAX($C5:$F5),$C5:$F5,0))

    Looking at the inner part of this, you are trying to find the maximum value of the cells in the range C5:F5. Those all contain text values (one of them is blank), so it doesn't really make sense, and it returns zero. You are then trying to find a match between that value and what is in cell C5:F5, and of course there is no match. Consequently, the INDEX function can not return a value and the error is returned instead. I don't know what this formula is trying to do.

    What I think you should be doing is taking the MONTH and YEAR values for TODAY and subtracting 1, and then finding the row where that date appears (i.e. for 1st of last month last year) and using the values in that row for your MAX. In the 4-year columns, you would need to repeat that 4 times (once for each year), and 10 times for the 10-year columns.

    I don't have time right now to delve into this, but hope this helps.

    Pete

  10. #10
    Registered User
    Join Date
    10-09-2019
    Location
    Luton, England
    MS-Off Ver
    365
    Posts
    13

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    Hi,

    Yes, instead of a range of cells all connected, for the formula in K7, it is looking at D7, and G7. How do you express that in a formula as putting $D7:$G7 gives you everything 'inbetween' which isn't needed and causing the issue!

  11. #11
    Registered User
    Join Date
    10-09-2019
    Location
    Luton, England
    MS-Off Ver
    365
    Posts
    13

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    Hi,

    I think I have solved it by copying the ratios into a new batch of columns later on and then referencing them!

    If you get the chance, if you could take a glance at it to ensure I've done it right, that would be great.

    Aside from that, thanks for you all for the help getting this over the line for me, I couldn't have done it without your help!
    Attached Files Attached Files

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    In the MAX term you can change it to this:

    MAX($D7,$G7)

    which will find the largest number of those two cells. It is okay to use a range in the MATCH function around it, although you probably want to change the range to $C7:$G7 if you are trying to find the column where that maximum value occurs. I have no idea why your INDEX function has a range of cells on row 3, and as I said before I don't know what you are trying to do with this formula.

    As there is no actual data in the file that you attached to Post #7, it is difficult to know if the formula is working or not.

    I don't see how this will help you to get the data from one year ago.

    Pete

  13. #13
    Registered User
    Join Date
    10-09-2019
    Location
    Luton, England
    MS-Off Ver
    365
    Posts
    13

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    Hi Pete, have a look at the attachment in post 11, I think the work around solves it. Let me know your thoughts.

    As for why I'm looking for it, I'm trying to find a predictor tool, so say in Nov this year, I can look at Nov last year to see which historically has been the strongest or weakest, and thus make a prediction as to which will be the best option for this november, based on historical performance

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    You don't need this formula in AD7:

    =SUM(D7)

    You can just have:

    =D7

    Similarly for the next two columns. The rest of it seems okay.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    EDIT: thanks for the rep.

    Pete

  15. #15
    Registered User
    Join Date
    10-09-2019
    Location
    Luton, England
    MS-Off Ver
    365
    Posts
    13

    Re: comparing cell values to determine which is highest, then returning the name assocaite

    done, done, and done. Thanks all

+ 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. Replies: 7
    Last Post: 08-27-2015, 03:37 PM
  2. Returning highest ranking values in a range in succession
    By Saeber4777 in forum Excel General
    Replies: 11
    Last Post: 06-07-2014, 09:36 PM
  3. Replies: 4
    Last Post: 05-29-2013, 04:18 AM
  4. [SOLVED] Returning the 5 highest values in a row.
    By LucG in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-24-2012, 02:36 PM
  5. Determine highest cell value in column
    By agni452 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2012, 06:49 AM
  6. Returning the Highest Count of a row of Values
    By hammer2 in forum Excel General
    Replies: 2
    Last Post: 12-06-2011, 09:25 PM
  7. Replies: 5
    Last Post: 06-20-2008, 12:12 PM

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