We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Excel Function not always working

Options
Hi,


A formula I have placed in Excel doesn't always work for me, if the value in column A is greater than "0" it should return "6 months" otherwise it should return "N/A"


The issue is that I have values in column "A" that equal "0," however the value returned is "6 months". This only happens sometimes and I was wondering is there a way to correct this?


=IF(A1>0.1,"6 Months", "N/A")

Comments

  • =IF(A1>0,"6 Months", "N/A")

    Not sure why you are referencing ".01" if the formula need to work on "zero" or "not zero". You could be running into problems where the value being checked is being rounded from multiple decimals to give the impression that it is zero, when in fact it is not.

    Of course, as it is written, it will return "N/A" for negative values. If you use:

    =IF(A1=0,"N/A", "6 Months")

    then anything that isn't zero will get the 6 months treatment, whether or not it is a negative value.

    Any help to you?
  • unforeseen
    unforeseen Posts: 7,381 Forumite
    Part of the Furniture 1,000 Posts Photogenic Name Dropper
    Your problem could be due to a cell being a text cell. Instead of the value 0 it has the character '0'
  • choyaa
    choyaa Posts: 226 Forumite
    Column A1 is in percentage format, I have changed the formula to "=IF(A1>0,"6 Months", "N/A"), however I am still getting the problem of "6 months" being returned for "0" values.


    I think the problem relates to rounding up.
  • bod1467
    bod1467 Posts: 15,214 Forumite
    It's actually related to rounding down, not up. 0.0000001% will appear as 0 but is not in fact zero.
  • choyaa
    choyaa Posts: 226 Forumite
    bod1467 wrote: »
    It's actually related to rounding down, not up. 0.0000001% will appear as 0 but is not in fact zero.

    Makes sense as to why this issue is occurring, how do I correct it?
  • Zelazny
    Zelazny Posts: 387 Forumite
    Part of the Furniture 100 Posts Combo Breaker
    choyaa wrote: »
    Makes sense as to why this issue is occurring, how do I correct it?

    Four possibilities

    If the cell value is actually 0.000000001 then:
    (i) Treat it as if it were zero:
    =IF(A1>=0.05,"6 Months", "N/A")
    
    You'll need to adjust the 0.05 depending on how many decimal places you have visible - if the cell is set to show 0 decimal places you'd need to use 0.5 for example (as anything under that will show up as zero).

    (ii) Anything over 0 gives "6 Months" even if it still appears as zero
    =IF(A1>0,"6 Months", "N/A")
    

    (iii) Anything not equal to 0 gives "6 months" even if it still appears as 0 and even if it's negative:
    =IF(A1=0,"N/A", "6 Months")
    
    (as bingo bango said).

    (iv) Catch anything greater than zero, even if stored as text:
    =IF(ISERROR(A1+0), "N/A", IF(A1+0>0, "6 Months", "N/A"))
    

    If you think that there are more digits than are being shown, you can use the buttons on the home bar or the keyboard shortcuts (alt-h-9 and Alt-h-0 - tap the keys in succession, not all at once) to show them.
  • Fightsback
    Fightsback Posts: 2,504 Forumite
    Or perhaps this ?

    =IF(ROUND(A1,1)>0,"6 months","N/A")

    This is significance to 1dp, adjust the second argument of the round function if you wish to increase this.

    https://support.office.com/en-us/article/ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c
    Science isn't exact, it's only confidence within limits.
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.9K Banking & Borrowing
  • 253.1K Reduce Debt & Boost Income
  • 453.5K Spending & Discounts
  • 243.9K Work, Benefits & Business
  • 598.7K Mortgages, Homes & Bills
  • 176.9K Life & Family
  • 257.1K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.