SOLVED

#NUM error

Copper Contributor

Hi,

This formula is returning a #NUM error and I can't see why

=IF(AND(G27+G28<>0,TODAY()>Dashboard!D29+10),DATEDIF(Dashboard!K29,TODAY(),"d")&" Days Overdue",G27+G28)

The error only occurs when looking at 'Dashboard!D29', which is using this formula =IF(D29<>"",D29+13,"") to return a date. I am using the same formulas throughout the sheet with no issue and if I change Dashboard!D29 to D28 or D30 it works (same formulas in these cells). I can't see what is wrong with these formulas when they work perfectly everywhere else.

 

23 Replies

Without seeing the sheet, is the date in K29 after 12/1/2022?

@Emily3918 

Does Dashboard!D29 really contain the formula =IF(D29<>"",D29+12,"") ? If so, that would cause a circular reference since the formula refers to its own cell...

Hi Patrick,
The date returned is 4/12/22, but I have the same formula returning dates earlier and later with no errors.
Hi Hans,
It isn't a circular reference as it is saying if D29 is not empty then add 12 to the date that is in D29, otherwise return a blank. The formula is not in D29. it is in K29.
I need to clarify that the issue is with D29 AND K29 from the 'Dashboard' tab in the first formula. If I change these to the dates above and below (D/K28 or 30) the formula works.
Hi Detlef,
Thanks for the link, but it isn't really applicable to this formula. As I have said the formula works for every other date and cell reference, so I don't think the error is because of the DATEDIF function.

@Emily3918 

IF(), AND() and TODAY() don't produce #NUM!.

Arithmetic operators and comparison operators don't produce #NUM!.

Leaving DATEDIF(). I rest my case.

 

@Emily3918 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Except that I have over 100 formulas exactly the same that are not producing #NUM, so thanks but this is not the solution.

@Emily3918 

No, I cannot access it. Please right-click the workbook on Dropbox and select Dropbox > Copy Dropbox Link from the context menu. Then paste that link into a reply.

@Emily3918 

And I have more than 100 formulas that produce #NUM!.

 

@Emily3918 

Nope, that link requires me to login.

@Emily3918 

Change +10 to another value. Probably +13.

 

@Emily3918 

See @Detlef Lewin's reply.

In the current formula, Dashboard!K14 is after TODAY(), so DATEDIF(Dashboard!K14,TODAY(),"d") returns an error.

Remarks:

  • Dashboard!K29 does not contain the formula =IF(D29<>"",D29+13,"") as you claimed (which would cause a circular reference), but =IF(D28<>"",D28+13,"") which is perfectly fine.
  • Instead of DATEDIF(Dashboard!K14,TODAY(),"d") you can use the simpler TODAY()-Dashboard!K14. If you had used that, you would have seen that the number of overdue days became negative.
I appreciate the time that some of you have put into my query, but I don't think that any of you have understood the issue.
Your remarks do not make any sense, K29 does contain the formula =IF(D29<>"",D29+13,""), I copied and pasted it out of that cell, maybe you were looking in the cell K28 which does have the formula =IF(D28<>"",D28+13,""). It isn't circular, it is a fairly basic IF statement that works in all other cells and did not produce #NUM anywhere else.
In the spread sheet that I attached the cell with #NUM was no longer in referring to K29 as I had removed some lines and columns, it was referring to K14, which I had highlighted red. Maybe I should have pointed this out.
All the remarks seem to miss the obvious point in that these formulas are working in every other cell except for this one, if they were circular then why was #NUM not occurring in all of them?
While the suggested formula of Today()-Dashboard!K14 also works the issue appears to have resolved itself and is not occurring elsewhere. If it does come up again, I will try this alternative, but as it is I will not be requiring any more advice on this matter. Thankyou.
1 best response

Accepted Solutions
best response confirmed by Emily3918 (Copper Contributor)
Solution

@Hans Vogelaar 

I have isolated the problem. Using the formula below (which is an example that applies to the whole sheet and is not specific to the cells indicated). =IF(AND(G27+G28<>0,TODAY()>Dashboard!D29+10),DATEDIF(Dashboard!K29,TODAY(),"d")&" Days Overdue",G27+G28) 

If
Dashboard!D29+10 is less than Today() and
Dashboard!K29 is only 1 day greater than Today().

The formula will return #NUM!

More than 1 day greater than Today() and everything works again.
This means if you were looking at my sheet more than 1 day after the date in Dashboard!K29 you wouldn't have seen the #NUM! error.
If I change ">Dashboard!D29+10" to ">Dashboard!K29" the formula works no matter what the date is. 
I can't recall why I decided to use "Dashboard!D29+10", I assume that I wanted the number of workdays not weekdays from the start date even though, although that didn't really affect the end result. Anyway, it all works now.

View solution in original post