+ Reply to Thread
Results 1 to 11 of 11

Calculating Whether a Range of Values falls inside or outside of a min and max range

  1. 07-07-2022, 07:09 AM #1
    MD72
    MD72 is offline
    Registered User
    Join Date
    07-07-2022
    Location
    New Haw, Addlestone
    MS-Off Ver
    Office 356 Family
    Posts
    5

    Post Calculating Whether a Range of Values falls inside or outside of a min and max range

    Hi

    I've pulled together a very basic risk metrics tracker. I would like to auto calculate whether or not a metric is inside or outside (or even below, inside, above) the specific tolerance range [R] for that metric when a user adds a new value every month [D2:M3].

    I have attached a sample of the Excel file I'm using.

    Any guidance would be very welcome
    Regards
    Mark
    Attached Images Attached Images
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-07-2022, 07:23 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    Welcome to the forum.

    Is it the LATEST column you wish to measure?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-07-2022, 07:28 AM #3
    Magaletta
    Magaletta is offline
    Registered User
    Join Date
    06-29-2022
    Location
    South Australia
    MS-Off Ver
    MS Windows 365 - Version 2204
    Posts
    46

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    Is this what you are after? Provided D4:M4 updates the minimum threshold
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 07-07-2022, 07:31 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    Try this:

    =LET(l,LOOKUP(2,1/(Table13[@[Jun 22 Actual]:[Mar-23 Actual]]<>""),Table13[@[Jun 22 Actual]:[Mar-23 Actual]]),IF(OR(l<[@[Minimum Threshold]],l>[@[Maximum Threshold]]),"Outside","Inside"))
    Reply With QuoteRegister To Reply
  5. 07-07-2022, 07:31 AM #5
    MD72
    MD72 is offline
    Registered User
    Join Date
    07-07-2022
    Location
    New Haw, Addlestone
    MS-Off Ver
    Office 356 Family
    Posts
    5

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    Hi Ali

    Thanks for the response and the welcome. Yes it is, sorry I didn't make that clear enough.
    Regards
    Mark
    Reply With QuoteRegister To Reply
  6. 07-07-2022, 07:32 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    @Magaletta

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
    Reply With QuoteRegister To Reply
  7. 07-07-2022, 07:32 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    @Mark - see post #4.
    Reply With QuoteRegister To Reply
  8. 07-07-2022, 07:46 AM #8
    MD72
    MD72 is offline
    Registered User
    Join Date
    07-07-2022
    Location
    New Haw, Addlestone
    MS-Off Ver
    Office 356 Family
    Posts
    5

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    Hi, great, this works a treat!! Thank you very much
    Regards
    Mark
    Reply With QuoteRegister To Reply
  9. 07-07-2022, 07:48 AM #9
    MD72
    MD72 is offline
    Registered User
    Join Date
    07-07-2022
    Location
    New Haw, Addlestone
    MS-Off Ver
    Office 356 Family
    Posts
    5

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    Hi Magaletta

    Thanks for responding and suggesting a solution. Ali's post has provided a working solution for me in this instance.

    Regards
    Mark
    Reply With QuoteRegister To Reply
  10. 07-07-2022, 07:48 AM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    You were offered two solutions. Which one are you referring to? Did you try my suggestion?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Reply With QuoteRegister To Reply
  11. 07-07-2022, 08:36 AM #11
    MD72
    MD72 is offline
    Registered User
    Join Date
    07-07-2022
    Location
    New Haw, Addlestone
    MS-Off Ver
    Office 356 Family
    Posts
    5

    Re: Calculating Whether a Range of Values falls inside or outside of a min and max range

    Hi Ali

    I was referring to your solution. I'll mark the thread as Solved and I did click the add reputation for you as well.
    Regards
    Mark
    Last edited by MD72; 07-07-2022 at 08:38 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate if data falls inside a range
    By Alexmedft in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-18-2022, 12:36 AM
  2. Returning multiple values if a cells value falls in a range of values.
    By KaeMe01 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-03-2022, 12:18 AM
  3. Ranking a value if it falls in a certain range of values
    By Excel15 in forum Excel General
    Replies: 1
    Last Post: 03-17-2015, 03:06 PM
  4. How to Return a Value if it falls within the values n a range of cells ?
    By af49 in forum Excel General
    Replies: 7
    Last Post: 06-12-2014, 05:00 AM
  5. Vlookup where lookup_value falls within range of values
    By SymphonyTomorrow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-20-2013, 02:19 PM
  6. Lookup a value in a range and return a value where it falls between the range values
    By JAUrrutia9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-22-2013, 04:15 AM
  7. Calculate if Range falls between two values
    By Michaniker in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-22-2010, 03:35 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1