2

I have a sheet formula of IF(Test1>0, Test1, NA()) which is designed to put a 'controlled' #N/A in the sheet if the condition is not met. Similarly, I am using IFERROR(Test2,NA()) elsewhere in the same sheet.

As the formulae for Testn are long, I would like to put them into a UDF but can't find a way to generate a 'controlled' #N/A error (one not green-flagged as an error on the sheet).

So far I have got as far as:

Function GTZero(sngInput As Single) As Variant

    If sngInput > 0 Then GTZero = sngInput Else GTZero = CVErr(xlErrNA)

End Function

This is then called in the sheet as =GTZero(Test1). The answer looks right but Excel flags this as a true error. I don't want to just use "#N/A" text as I need to use the results in a chart and want to skip the #N/As

ChemEnger
  • 71
  • 9
  • 1
    You can't, as far as I know. *Any* function that returns `#N/A` other than `NA()` will be flagged if you have that error checking option switched on. – Rory Nov 20 '19 at 12:03
  • 2
    Why don't you use `=IFERROR(GTZero(Test1),NA())`? If what you are trying to do is to calculate `Test1` just once, this should work for you. – Kirill Tkachenko Nov 20 '19 at 12:09
  • Instead of returning `#N/A`, return a "BLANK CELL" as shown [HERE](https://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel). When I say "BLANK CELL", I do not mean `""` as this will get plotted in the chart. Also Right click on the `Chart | Select Data | Hidden and Empty Cells | Show Empty Cell as "Connect data points with line"`This again is tested on a line chart. – Siddharth Rout Nov 20 '19 at 12:19
  • BTW I am referring to `Przemyslaw Remin` answer in the above link – Siddharth Rout Nov 20 '19 at 12:27
  • My original assumption was that "Test1" was a named range. However, the statement "As the formulae for Test*n* are long" gives me some doubt. How is `Test1` really represented when you call the function? Because otherwise your code seems fine. – AJD Nov 20 '19 at 19:06
  • BTW, I just tested your code as written in Excel O365, and it worked as expected. And yes, it is a 'true error' because that is what you are telling the UDF to do. – AJD Nov 20 '19 at 19:13

2 Answers2

1

No VBA needed. You can do this trick using formulas:

e^(ln(whatever)) always returns whatever because e^ and ln() are inverse functions.

Except... ln() has no real solution for negative numbers and 0, resulting in error in Excel, which is what you want.

So you can do this:

= IFERROR(EXP(LN(Test1)),NA())

Same as your formula, except Test1 only appears once.

See below working example. Formula in B1 is dragged down.

enter image description here

ImaginaryHuman072889
  • 4,336
  • 5
  • 12
  • 43
  • Thanks @ImaginaryHuman. That's a really elegant solution and I get it but I'd be a bit worried about someone coming along later and getting worried by the EXP and LN! – ChemEnger Nov 20 '19 at 13:53
  • That seems like a silly reason to not do something... but ok. Anyway you should mark one of these answers as correct if they helped you. – ImaginaryHuman072889 Nov 20 '19 at 19:48
  • A thought - could also use `=IFERROR(Test1^1,NA())` to give the same sort of result? – ChemEnger Nov 29 '19 at 10:56
  • 1
    @ChemEnger No... (any number)^1 is always defined, even for negative numbers. This accomplishes nothing. Try it for yourself. (-1)^1 still returns (-1) in Excel. – ImaginaryHuman072889 Nov 30 '19 at 13:29
1

Seems like this is not possible. I've tried putting =NA() or just #N/A as string in one cell and returning a reference to that cell from the UDF. I've also tried defining a name which refers to =NA() and returning this name, but nothing worked.
According to support.office.com:

Note: If you enter an error value directly in a cell, it is stored as that error value but is not marked as an error. However, if a formula in another cell refers to that cell, the formula returns the error value from that cell.

Kirill Tkachenko
  • 327
  • 1
  • 8
  • Looks like you're right. I think probably your suggestion of just `=IFERROR(GTZero(Test1),NA())` is going to be the way to go. Either that or live with / 'ignore the flags. – ChemEnger Nov 20 '19 at 14:02