+ Reply to Thread
Results 1 to 8 of 8

i want to use if formula when the cell has number ends with .1 like 452.1

  1. 08-17-2016, 04:59 AM #1
    hazemrezo
    hazemrezo is offline
    Registered User
    Join Date
    09-29-2013
    Location
    Egypt,Giza
    MS-Off Ver
    Excel 2003
    Posts
    78

    i want to use if formula when the cell has number ends with .1 like 452.1

    hi all
    i want to use formula( if) when the cell has number like 4523.1 or any further numbers 4523.20 or 4523.60

    like = if (D5="number ends with .1",true,"") or any other actions i want to run it in my formula


    thanks in advance

    Hazemrezo
    Reply With QuoteRegister To Reply
  2. 08-17-2016, 05:04 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: i want to use if formula when the cell has number ends with .1 like 452.1

    Assuming A1=452.1

    Then try

    B1=IF(ROUND(MOD(A1,1),1)=0.1,TRUE,"")
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 08-17-2016, 05:05 AM #3
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,811

    Re: i want to use if formula when the cell has number ends with .1 like 452.1

    You can do this:

    =IF(D5-INT(D5)=0.1,TRUE,"")

    looking specifically for the value 0.1, or this:

    =IF(MOD(D5)>0,TRUE,"")

    if you are looking for any fractional value.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  4. 08-17-2016, 05:09 AM #4
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: i want to use if formula when the cell has number ends with .1 like 452.1

    Quote Originally Posted by Pete_UK View Post

    =IF(MOD(D5)>0,TRUE,"")


    Pete
    Mr. Pete =IF(MOD(D5,1)>0,TRUE,"") highlighted part perhaps missing
    Reply With QuoteRegister To Reply
  5. 08-17-2016, 05:15 AM #5
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,811

    Re: i want to use if formula when the cell has number ends with .1 like 452.1

    Thanks for pointing that out, Ankur. I think I need to get a new keyboard, as that seems to be happening more and more.

    Pete
    Reply With QuoteRegister To Reply
  6. 08-17-2016, 05:29 AM #6
    hazemrezo
    hazemrezo is offline
    Registered User
    Join Date
    09-29-2013
    Location
    Egypt,Giza
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: i want to use if formula when the cell has number ends with .1 like 452.1

    hey shukla

    i tried to use your formula but its not work , you can check by this work book
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 08-17-2016, 05:32 AM #7
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: i want to use if formula when the cell has number ends with .1 like 452.1

    Quote Originally Posted by hazemrezo View Post
    hey shukla

    i tried to use your formula but its not work , you can check by this work book
    Did you check post #2

    Try

    C4=IF(ROUND(MOD(B4,1),1)=0.1,B4,A4) and drag down!
    Reply With QuoteRegister To Reply
  8. 08-17-2016, 05:36 AM #8
    hazemrezo
    hazemrezo is offline
    Registered User
    Join Date
    09-29-2013
    Location
    Egypt,Giza
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: i want to use if formula when the cell has number ends with .1 like 452.1

    Thanks very much it 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. How do I create a custom number format in excel that ends with a letter?
    By guitarteacher in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 12-21-2015, 09:18 AM
  2. Formula ends with #NUM! but need to equal zero if nothing returns
    By mort.marshall.20 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2013, 05:25 PM
  3. [SOLVED] Hours to number conversion + IF Function ends with #Value.
    By roly63 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 5
    Last Post: 11-08-2013, 01:12 PM
  4. [SOLVED] Setting a range that ends where data ends?
    By Mgassma in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2013, 09:46 AM
  5. Checking every 2 rows, finding the number a cell ends with, and grouping
    By ninj5 in forum Excel General
    Replies: 4
    Last Post: 01-22-2013, 04:39 PM
  6. Delete whole if the cell ends with something
    By mthem2003 in forum Excel General
    Replies: 4
    Last Post: 08-18-2009, 09:16 PM
  7. Auto Fill Series when number ends in -1
    By JGus in forum Excel General
    Replies: 7
    Last Post: 12-22-2005, 11:20 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