problem with isblank when cell has a formula in it that is returning a Zero, date or empty

[COL_MCC]

Hi Hope you can help.

I am trying to get the formula below to work but if the cell is blank but has a formula in the formula return Overdue when it should just be blank

I have tried two different formulas and same result

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I can see that due to the B2 cell or the B18 cell having a formula in it (but empty string) it causes the cell to state overdue and that is not the case as it can't be Overdue if no date is provided. I want instead for the cell to show nothing?

cheers for any help.

[TMS]

I think ISBLANK will return false if there is a formula in the cell, even if it returns null ( "" ).

I'd start the formula with

Please Login or Register  to view this content.

[COL_MCC]

Hi,

Thanks for that, i swopped it around and same result. It is because of the formula in B18 but doesn't seem to make a difference rearranging the formula

Please Login or Register  to view this content.

8=100,"Complete", IF(B18 < TODAY(),"Overdue","On Track")))[/CODE]

I have attached the excel sheet if that helps,

Cheers

[TMS]

This formula

Please Login or Register  to view this content.

seems to work as I would expect it to.

If B18 is blank, it returns blank. If B18 is NOT blank and A18 = 100, it will return "Complete". If B18 is NOT blank AND A17 is NOT 100, then the date in B18 is compared to today's date ( TODAY() ). If the date is less than TODAY(), it will return "Overdue", otherwise it will return "On Track".

If the priority is the value in A18, you should swap the first two tests over. Like this:

Please Login or Register  to view this content.

In this case, if A18 = 100, the formula will show "Complete" regardless if B18 is blank or has a date in it.

How would you expect these formulae to function?

[COL_MCC]

Hi,

thanks for that, i added in a = 0 to account for the date 00/01/1900

eg

=IF(A1=100, "Complete", IF( B1="", "",

Please Login or Register  to view this content.

IF( B1=0, "",IF(B1 < TODAY(), "Overdue", "On Track"))))

cheers

[TMS]

You could use OR:

Please Login or Register  to view this content.

It had not dawned on me that you have set the display zeroes as blank option, so I was focusing on row 18 where you simply had a date in cell B18.

Might be better if you used a formula in column B that returned a blank rather than hiding zero values, given that it applies to the whole worksheet.

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.