+ Reply to Thread
Results 1 to 7 of 7

Modify value according to couple conditions

  1. 04-11-2021, 11:55 AM #1
    Juanhijuan
    Juanhijuan is offline
    Registered User
    Join Date
    04-11-2021
    Location
    Germany
    MS-Off Ver
    2021
    Posts
    10

    Modify value according to couple conditions

    I am very new to Excel and no idea if something like that is possible and if I am able to write down what I would like to achieve but let's try. Please be understanding.

    I have to write down a concentration of specific reagent in an excel cell but:
    1. if value is smaller than 0.1 such "message" should be left in the cell "<0.1".
    2. if value is smaller than 1 it has to be rounded to two decimal places.
    3. if value is greater or equal to 1 and smaller than 10 it has to be rounded to one decimal place.
    4. if value is greater or equal to 10 such "message" should be left in the cell ">10".


    Is smth like that doable in excel ?
    Reply With QuoteRegister To Reply
  2. 04-11-2021, 12:07 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Modify value according to couple conditions

    If it's out of range... like 0.01 and 30... are those numbers ENTERED by you or GENERATED by a formula?
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 04-11-2021, 12:08 PM #3
    JohnTopley
    JohnTopley is offline
    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,206

    Re: Modify value according to couple conditions

    Try

    =IF(A1<0.1,"<0.1",IF(A1<1,ROUND(A1,2),IF(A1<10,ROUND(A1,1),">10")))

    value in A1


    No ....Misread requirement (yet again!)
    Last edited by JohnTopley; 04-11-2021 at 12:43 PM.
    Reply With QuoteRegister To Reply
  4. 04-11-2021, 12:31 PM #4
    Juanhijuan
    Juanhijuan is offline
    Registered User
    Join Date
    04-11-2021
    Location
    Germany
    MS-Off Ver
    2021
    Posts
    10

    Re: Modify value according to couple conditions

    Quote Originally Posted by Glenn Kennedy View Post
    If it's out of range... like 0.01 and 30... are those numbers ENTERED by you or GENERATED by a formula?
    They are entered by me.
    Reply With QuoteRegister To Reply
  5. 04-11-2021, 12:36 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Modify value according to couple conditions

    Maybe this is what you want....

    The original values are retained, but they are formatted as you desired, using 4 conditional formatting formulae... along these lines.

    =AND(A1>=0.1,A1<1)

    see conditional formatting manager.

    Home/Conditional Formatting/Manage rules.

    The rules have been applied to the yellow cells.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 04-11-2021, 01:07 PM #6
    Juanhijuan
    Juanhijuan is offline
    Registered User
    Join Date
    04-11-2021
    Location
    Germany
    MS-Off Ver
    2021
    Posts
    10

    Re: Modify value according to couple conditions

    Thank you Glenn!

    That works great!
    Reply With QuoteRegister To Reply
  7. 04-11-2021, 01:08 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Modify value according to couple conditions

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
    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. Randomized, static assignment from list with no duplicates, with a couple other conditions
    By Haderach in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-01-2020, 01:40 AM
  2. [SOLVED] Modify existing code to act on multiple conditions
    By MikeWaring in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2018, 03:11 PM
  3. How to Change Absolute Cell Reference based on a couple of conditions
    By hamidxa in forum Excel General
    Replies: 5
    Last Post: 04-28-2014, 07:54 AM
  4. detect what comes after two identical cells with a couple of conditions
    By tlacloche in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-27-2013, 11:45 AM
  5. VBA modify value based on conditions of another cell
    By sk8shorty01 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-26-2011, 01:20 PM
  6. Loop based on a couple of conditions
    By h_aesa1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-31-2010, 01:22 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1