+ Reply to Thread
Results 1 to 20 of 20

show 0,0.5,1 depending on various condition

  1. #1
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    show 0,0.5,1 depending on various condition

    hey guys i need help with a formulae.


    I have attached the sample file.

    If Range "T" is above 2 and equal to or less than 17 then it must be show 0.5 in range "V"

    If Range "T" is equal to greater than 18 than it must show 1 in range "V"

    However if range "T" is 0, 1 or 2 then it must show 0 in range "V"
    Attached Files Attached Files
    Last edited by anilpatni1234; 06-21-2018 at 12:53 AM.

  2. #2
    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
    79,398

    Re: need help with IF

    Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

    • Use concise, accurate thread titles.
    • Your post title should describe your problem, not your anticipated solution.
    • Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
    • Responding to a request to change your thread title by doing so is mandatory.

    To change a title go to your first post, click EDIT then Go Advanced and change your title.

    No help to be offered, please, until the OP complies with this request.
    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.

  3. #3
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: need help with IF

    Thank you for guiding.

    I have changed the tittle

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: show 0,0.5,1 depending on various condition

    Please Login or Register  to view this content.
    Ben Van Johnson

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: show 0,0.5,1 depending on various condition

    or
    V3=IF(T3<=2,0,IF(T3<18,0.5,1))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: show 0,0.5,1 depending on various condition

    HEy Ban ,''

    Thank you so much for your help

  7. #7
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: show 0,0.5,1 depending on various condition

    Hey Guys,
    I need help

    I am facing a error regarding this formulae.

    I have attached my workbook.
    The question is :

    If Sheet "SOLD" Range "O" is above 2 and equal to or less than 17 then it must be show 0.5 in range "P"

    If Sheet "SOLD" Range "O" is equal to greater than 18 than it must show 1 in range "P"

    However if Sheet "SOLD" range "O" is 0, 1 or 2 then it must show 0 in range "P"

    The formulae which Ben helped me make is :

    Please Login or Register  to view this content.
    However if you see ,
    Sheet "Sold" cell "O2" and "O3" both have value "2"

    and the formulae above is in range P

    However the formuale is giving different results in both cells .

    However if Sheet "SOLD" range "O" is 0, 1 or 2 then it must show 0 in range "P"
    It must show 0 in both cells "P2, P3" according to the formuale'

    However its showing "0" in P3 and "0.5" in P2

    Whats the error?

    I need it to show 0 as I quoted above.

    ANy help please?
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: show 0,0.5,1 depending on various condition

    This formulae is referring to only Range "O"
    However its giving different results . I am getting very confused

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: show 0,0.5,1 depending on various condition

    O2 & O3 are not same
    O2=2.00000000000001
    O3 = 2
    So that you are getting different result
    you can find the same by using below formula
    =o2=o3
    you can get false

  10. #10
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: show 0,0.5,1 depending on various condition

    Hey Samba,

    Thaks for your prompt reply.

    So how do i make changes to this formulae?


    Please Login or Register  to view this content.
    I am going to have a huge database.
    So what formuale sould I use?

    is there any other way to avoid the extra fraction digits after "2"
    Last edited by anilpatni1234; 10-20-2018 at 02:58 AM.

  11. #11
    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
    79,398

    Re: show 0,0.5,1 depending on various condition

    How about this?

    =IF(ROUND(O3,0)<=2,0,IF(AND(ROUND(O3,0)>2,ROUND(O3,0)<18),0.5,1))

  12. #12
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: show 0,0.5,1 depending on various condition

    Hey Samba,

    I found a way out.
    I added a new column next to column "O" .
    In the new column "P" I added a formulae
    Please Login or Register  to view this content.
    Thus, this removes the fraction digit.

    HOWEVER,
    I need the integrate the above formulae in column O itself without adding any new column.
    Thus is it possible to have a single formulae in cell O2 of sheet "SOLD" ?

    Can I have
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    together in one cell?

  13. #13
    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
    79,398

    Re: show 0,0.5,1 depending on various condition

    Did you see my post?

  14. #14
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: show 0,0.5,1 depending on various condition

    Hey Ali,
    Sorry for late reply, I was having some issue loggin in

    The formulae of yours does nor work properly.

    I have attached 2 workbook.
    In workbook test1 , I have incorporate your formulae
    In workbook test 2, I have mine earlier formulae.

    If i change dates to 23/10/2018 in cells B2,C2,D2 of sheet PRODUCT then it must show 0.5 in cell P2 (according to the formulae which I had earlier)
    However in yours it shows blank.

    In file test1 its blank,
    in file test2 its 0.5
    Attached Files Attached Files

  15. #15
    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
    43,896

    Re: show 0,0.5,1 depending on various condition

    ANILPATNI. Your thread is getting a bit messy. Please remove your "added columns" TRUNC, etc, and post a nice clean sheet (preferably without macros) showing more than 2 rows of data (about 10 would be ideal) along with your manually calculated results.

    I can't tell what is part of your core query and what is additional "stuff" that you have added later and which is simply causing confusion.
    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

  16. #16
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: show 0,0.5,1 depending on various condition

    Hey Glenn,
    Thanks for your response.

    Here is the workbook attached.

    Basically in my workbook, I have a mortgage business and I need to calculate the number of months between 2 dates

    In sheet PRODUCT , range E,F,G i enter the date of morgage
    On sheet SOLD, range B,C,D, i enter the date when the morgage is reedemmed.

    So here are the conditions :

    If Sheet "SOLD" Range "O" is above 2 and equal to or less than 17 then it must be show 0.5 in range "P"

    If Sheet "SOLD" Range "O" is equal to greater than 18 than it must show 1 in range "P"

    However if Sheet "SOLD" range "O" is 0, 1 or 2 then it must show 0 in range "P"


    Column P has formulae
    Please Login or Register  to view this content.
    Now both cell O2 and O3 have the same value "2" . However Column P is showing different results.


    Samba Pointed out this to me
    O2 & O3 are not same
    O2=2.00000000000001
    O3 = 2
    Attached Files Attached Files

  17. #17
    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
    43,896

    Re: show 0,0.5,1 depending on various condition

    I asked you to do three things... and you did none of them. Oh well.

    Remove Data validation from column O.

    O2, copied down:
    =ROUND(N2*30,0)
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 10-20-2018 at 06:35 AM.

  18. #18
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: show 0,0.5,1 depending on various condition

    Hey Glenn,

    Sorry. Sorry to have not understood well.

    But thank you so much.

    The above trick works for me.

    Thank you so much.


    Thank you so much for helping me.

  19. #19
    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
    79,398

    Re: show 0,0.5,1 depending on various condition

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

  20. #20
    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
    43,896

    Re: show 0,0.5,1 depending on various condition

    You're welcome.



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

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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