Results 1 to 7 of 7

Thread: Need a formula to return text instead of a number

  1. #1

    Need a formula to return text instead of a number



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys, hope everyone is having a good day!

    I am putting a simple spreadsheet together for the tanning company I work at (not getting paid for it, just doing it in my spare time). I need this spreadsheet to find the 5 highest selling lotions for a month. Instead of just returning the 5 highest values though, I would like for a formula to return the name of the top 5 highest selling lotions. One of my concerns is that the range of data is separated into 3 different columns. (because we sell 3 different types of lotion)

    This is what I have so far:
    Click image for larger version. 

Name:	Lotion Sales.jpg 
Views:	25 
Size:	93.9 KB 
ID:	1842

    The data for units sold is in columns C, G, and K. I need the 5 highest values from all 3 columns combined (not 5 from each column). I would like a formula to calculate and display the names of the lotion, which are in columns B, F, and J, instead of just returning the 5 values in column C, G, and K.

    Thanks!

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Hi
    Looking at the products they all appear to be different, so is there no "adding together" to be done ?
    or
    Should Snooki be the highest with 24, as they have sales in all three categories ?

  3. #3
    No, none of the products should be added together, they are all separate, even if they share the same name.
    Also, no, Snooki should not be combined. There are 3 different types of snooki lotion. I see where that could be confusing.

    I want to find the 5 highest selling lotions of all the three columns. In the pic I posted, White 2 Black Extreme has 11 units sold and then Go Dark, Totally Baked, Girlfriend Sexy, and SWAG all have 10 units sold. Those are the 5 highest selling lotions. I want a formula that will associate the name of the lotion with the number of units sold. This is so that I do not have to manually look through the data and determine the 5 highest values myself.

    I can use the LARGE function to return the 5 highest values from columns C, G, and K. However, instead of the numerical values, I want it to return the names of the lotions that are next to the numerical values.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    Excel 2016
    If you can place the table below each other instead to line up the items in the same column, then it would be much easier....


  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NBVC View Post
    If you can place the table below each other instead to line up the items in the same column, then it would be much easier....
    Yes


    This works if you change the table:

    The formulae are array ones so after entering you need to press CTRL+SHIFT+ENTER instead of just ENTER.
    Attached Files Attached Files

  6. #6
    You guys are awesome!

    Hercules, you are my hero Thank you so much for all your help.

    There is no way I would have every figured out that super long formula on my own lol.

    Hope you guys have a great day!

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Your Welcome
    Just one point - The formula is good for about 4000 rows of data. If you need more than that, you will see the figure 10000 mentioned in two places. Amend these to 100000 in H21 and I21 (CTRL+SHIFT+ENTER)
    Then copy the formulae down to H25 and I25.
    Adjust the above to suit your actual cell references, of course.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •