+ 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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,093

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  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
    11,497

    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 2013
    Posts
    8,192

    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 2013
    Posts
    8,192

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,093

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,093

    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... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    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



  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... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,093

    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... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    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