+ Reply to Thread
Results 1 to 3 of 3

Excel puzzle - how to solve?

  1. #1
    Registered User
    Join Date
    02-01-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Excel puzzle - how to solve?

    Hi all,

    I've been breaking my head on creating a formula that calculates the green text in the attached image. I have a reference table that contains data ranges as shown in the orange column in the left table. I also have a database where I would like a formula to show me the category number from the reference table based on the values for Jane (red column) in the database. For example, Jane has value 50 in the database. I then want the formula to go to the reference table and check based on the range (orange column; 0-69.1 / 69.1-160.5 / >160.5) in what category value 50 falls (in this case 1 as the range data specifies the upper limit). Same for Iris where value 0.1 matches reference range 0.1 and then for the formula to show me category 1. Does anyone know how to do this using an excel formula?!

    Thanks a lot!
    Last edited by Excellation86; 01-12-2018 at 10:12 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Excel puzzle - how to solve?

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Excel puzzle - how to solve?

    Pl see file. I have made changes in matrix table
    ARRAY Formula
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ 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. Excel Puzzle
    By aruni_batra in forum Excel General
    Replies: 3
    Last Post: 07-29-2017, 01:52 AM
  2. Solve the puzzle???
    By myatix in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-18-2016, 06:45 PM
  3. [SOLVED] Excel Challenging Puzzle
    By AaronL1990 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2015, 07:59 AM
  4. Need a formula to solve a puzzle
    By amitkr3855 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2013, 02:27 AM
  5. Excel code required to solve puzzle
    By vasant in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2011, 04:25 AM
  6. [SOLVED] Excel Puzzle
    By dwalesb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2006, 02:30 PM
  7. Excel Versions Puzzle
    By David Gee in forum Excel General
    Replies: 4
    Last Post: 01-05-2005, 07:06 PM

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