+ Reply to Thread
Results 1 to 3 of 3

Formula recognise rounded number

  1. #1
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Formula recognise rounded number

    Hi

    I'm using LOOKUP to allocate codes to numbers.
    =LOOKUP(H12,{0,1,30,40,50,60,70,80},{0,1,2,3,4,5,6,7})

    This returns e.g. 50 as 4

    However the focus cell is formatted to round up and I would like LOOKUP to recognise the displayed rounded number rather than the actual value.
    e.g. 49.5 displays as 50. I would like this to return 4, rather than 3.

    At the moment this is my workaround:
    = LOOKUP(H12,{0,0.5,29.5,39.5,49.5,59.5,69.5,79.5},{0,1,2,3,4,5,6,7})

    It works, however I wonder if there is a better more modular solution?
    Attached Files Attached Files

  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,202

    Re: Formula recognise rounded number

    No as formatting is exactly that - it does not change the underlying value

    If it works, don't fix it !!
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    08-23-2014
    Location
    Cape Town, South Africa
    MS-Off Ver
    2019
    Posts
    19

    Re: Formula recognise rounded number

    Thank you for the feedback - I'll stick to my solution

+ 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: 2
    Last Post: 01-16-2018, 07:21 AM
  2. [SOLVED] Formula that can take a number and divide it into a rounded number evenly
    By cmorten82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-20-2016, 07:26 PM
  3. Formula Help: IF/THEN that provides whole number rounded to a given unit of measure
    By new2purchasing in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-07-2014, 10:25 AM
  4. New to VBA, Function returning number rounded to whole number
    By Sonnerstudent in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2013, 01:39 PM
  5. syntax for comparing a rounded number to a rounded list
    By stockgoblin42 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2013, 10:12 AM
  6. My formula does not recognise a value as a number
    By stanja in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2009, 12:25 PM
  7. Rounded-off number
    By herve in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-03-2005, 01: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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1