We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
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

choyaa
Posts: 226 Forumite
in Techie Stuff
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")
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")
0
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?0 -
Your problem could be due to a cell being a text cell. Instead of the value 0 it has the character '0'0
-
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.0 -
It's actually related to rounding down, not up. 0.0000001% will appear as 0 but is not in fact zero.0
-
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.0 -
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-b3e7f61a213cScience isn't exact, it's only confidence within limits.0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

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