Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

formula issue (not reconising zero)

0

I'm trying to get Column A to add 1 if another cell is either zero or less

=IF(D712<=0,"",A712+1) but it's reconising 0

my aim is to not have the value in column A show if the total (column D) is zero or less as the loan would have been paid

EDIT

I have uploaded finished sheet.

Thank you John

excelformulas-functionsfinance
MS Office Version: 2019
posted by: wildecoyote1966 (rep: 30) on Sat Sep 25, 2021 at 7:55 am
Answer
Discuss

Answers

0
Selected Answer

Albert

Bear with me...

To not display zero values on a sheet, the usual method works- go File/Options/Advanced/ then down to "Display Options for this worksheet" and uncheck "Show a zero in cells that have zero value". Click OK.

In your sheet, that appears to do nothing (where $0.00 shows) but if you change the format of those cells to General. you'll see they have tiny non-zero values e.g. in cell D698 it's 0.000146644 and in D699 it's 1.51304E-07 (showing in Engineering notation and equivalent 1.513 /10,000,000). Overtype with zero and the cell will appear empty.

I guess your options are to check your workings to ensure a true zero value is reached or use conditional formatting for where the value is less than 0.01, the font is white. (That's suggest in the Microsoft guidance Display or hide zero values).

You could change your formula in A712 to 

=IF(D711<=0.01,"",A711+1)
That will blank A712 but mess up your other cells e.g. M to Q (giving #VALUE errors).

A quick fix for that is to use Conditional Formatting (and ISERROR based on each cell) to set both cell fill and font to white- see attached file where that's done for A17:T1000 of Amort.

Hope this helps.

posted by: John_Ru (rep: 6142) on Sat Sep 25, 2021 at 8:41 am
Discuss

Discussion

Hi John
Thanks for the quick response. I did the not displlay zero thing and nothing changed.

I don't understand why it has that issue with the .000? thing when all the figures are formatted to $

Do I need to do a rounding thing with every formula?
if I remove the 5000 extra payment that's just a sample thing but may happen i n the future.
wildecoyote1966 (rep: 30) Sep 25, '21 at 9:17 am
Albert, I said the "not display zero thing" does nothing with your values (cells showing $0.00 but actually non-zero) but DOES work if you type 0 in such a cell, even if it's formatted as Currency. E.g. I just tried 0.000000001 which shows as $0.00 and so was not blanked (unlike a true value of 0).

Not sure what to suggest since your columns in Amort are so heavily cross referenced. My formula works in A so I'll send a revised file.
John_Ru (rep: 6142) Sep 25, '21 at 9:39 am
I did as you suggested John and with minor adjustment I think it works
wildecoyote1966 (rep: 30) Sep 25, '21 at 10:26 am
Looks good Albert. Teamwork rules! Thanks for selecting my Answer.
John_Ru (rep: 6142) Sep 25, '21 at 11:17 am
Hello wildecoyote,

Looking at your original post: "I'm trying to get Column A to add 1 if another cell is either zero or less =IF(D712<=0,"",A712+1)" I think you only need to change your IF formula.

As is, it reads: if D712 is less than or equal to 0 is TRUE, then do(enter/display) nothing ( "" ); if it is FALSE then add 1 to A712. Switch your IF statement to:
=IF(D712<=0,A712+1,"").
Cheers   :-)
WillieD24 (rep: 547) Sep 26, '21 at 10:24 am
Hi Willie
I'm going to leave it for now as it seems to be working
I will keep what you said in mind
Thanks
wildecoyote1966 (rep: 30) Sep 26, '21 at 9:04 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login