0

I want to return the highest value in a set of cells with a representative code. A picture is worth a thousand words, so I've added one for you below:

enter image description here

What you see in the image (column T, where the letter relates to one of the headers and shows what the highest value is out of the three preceding columns) was done by hand. I don't want to do that for the remainder because it would take a really long time.

What is the simplest, easiest formula to accomplish this for someone using Excel 2007? (If you could add a bit of explanation with your formula, that would be even better, but not necessary.)

closed as unclear what you're asking by Gary's Student, DavidPostill, mdpc, kenorb, Simon Sheehan Oct 23 '16 at 4:50

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?. – DavidPostill Sep 27 '16 at 8:09
  • Thank you for the comment, @DavidPostill. I haven't asked a lot of questions in this forum and since my one or two other questions about formulas were answered so quickly and without any such admonishment about using this for a "free script/code writing service," I was a bit surprised to see this type of comment, especially when I have attempted to help others by offering a solution of my own on at least one occasion. Nevertheless, the page you referred me to was good and I enjoyed reading it. However, I don't think my question here is really at odds with any of the advice it provides. – Lisa Beck Sep 27 '16 at 8:56
  • As for the first point in the page you referred me to -- search, and research -- I always do some amount of that before I even bother coming to this stackexchange, and then I try to make sure my question hasn't already been addressed through this forum. The amount I do depends on how difficult I think my question is to answer relative to the depth and breadth of pooled knowledge I think might exist at superuser.com. I often don't attempt to "muddy the waters" with my sometimes clumsy attempts to cobble together a formula that will work. – Lisa Beck Sep 27 '16 at 9:22
  • But just for you, @DavidPostill, I will share some of the "behind the scenes" work that went on before I decided to post this question. After visiting several web pages with some really complicated formulas, I finally decided to throw in the towel and just try something off the top of my head. So I threw down this: =IF(Q2>R2, "N"). – Lisa Beck Sep 27 '16 at 9:41
  • That worked, but when I tried to add a third column of information to that conditional formula, I just kept getting error message after error message. Despite my failure to find a formula that would work, I have a feeling this just wouldn't be that hard to a seasoned super user, so I didn't spend gobs of time trying to figure this one out on my own. I've been rather impressed with the expertise of users in this forum, so it would really surprise me if this was something that stumped this community. – Lisa Beck Sep 27 '16 at 9:41
2

Since you did not specify what happens in a tie, I am going to assume the result should be NBT if there is a tie across all three columns.

  1. Find the maximum value:

    Use this formula:

    =MAX(Q2:S2)
    

    This gives the maximum value. (This assumes it is for use in T2)

    Use the name manager to give this formula the name MaxV or just use the formula itself.

  2. First IF:

    =IF(Q2=MaxV,"N","")
    

    This IF() prints a N or nothing

  3. Make a formula for other columns and combine into a single equation:

    =IF(Q2=MaxV,"N","") & IF(R2=MaxV,"B","") & IF(S2=MaxV,"T","")
    

If you do not want to use the name manager then the complete formula is:

=IF(Q2=MAX(Q2:S2),"N","") & IF(R2=MAX(Q2:S2),"B","") & IF(S2=MAX(Q2:S2),"T","")

The & is used to join two letters together similar to how a + is used to add two numbers.

  • I haven't tried your formula yet, but I'm impressed with your answer already for its simplicity and use of Excel features a bit more common to the average user. I am familiar with the MIN/MAX functions to some degree, but for some reason I didn't think of them at all when trying to solve this. Perhaps I was too focused on the writing of the conditional formula and why my various attempts weren't working rather than already existing features of Excel that might help me do more with less. – Lisa Beck Sep 27 '16 at 16:17
  • As for any "ties," I took measures to ensure there wouldn't be any, but I like your proposed solution in such an event. I’m going to try your formula now to see if it works. I’ll let you know what happens. Many thanks to you for even having made the attempt. – Lisa Beck Sep 27 '16 at 16:18
  • You are brilliant, @bvaughn. Your formula works like a charm. – Lisa Beck Sep 27 '16 at 16:25
2

bvaughn's answer is nice and clean and simple, especially if you have only a few columns. It's also intuitively scalable if you add more columns. If you have a lot of columns, though, that approach can start to get unwieldy.

Here's a different approach that scales better if you ever have to expand the problem with more columns.

=CHOOSE(MATCH(MAX(Q2:S2),Q2:S2,0),"N","B","T")

Explanation, working from the inside out:

  • MAX(Q2:S2) identifies the maximum value in the range.

  • MATCH(MAX(Q2:S2),Q2:S2,0) locates that value in the range, giving you a position number.

  • CHOOSE(position,"N","B","T") selects from the list of output values using that same relative position.

This type of formula can handle a large number of columns by just adding the outputs to the list (and adjusting the range, of course). The workhorse portion doesn't change when the columns grow. The formula stays compact because the only thing that grows as you add columns is the list of output values.

Alternate solution

The CHOOSE function is needed only because you are using labels different from the column headers. If you use the column headers ("News", "Books", "Tweets" in this case), you can use a generic formula that just references the ranges:

=INDEX(Q$1:S$1,1,MATCH(MAX(Q2:S2),Q2:S2,0))

Locating the position of the highest number is the same as before, but it is used to select from the headers in the first row using INDEX. This formula would remain the same for any number of columns, just specifying the range.

  • 1
    A nice elegant alternative to my method. However, ii handles ties differently. If there is a tie mine outputs (NT) where this would output (N). Would depend on where this is used if ties was something to be bothered with. – bvaughn Oct 3 '16 at 16:11
  • @bvaughn, you're right. If there can be ties, your answer handles that nicely and mine doesn't. Guess that also explains why your answer got the "You are brilliant" comment; obviously that's true. :-) – fixer1234 Oct 3 '16 at 17:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.