Null Value - $1,000,000 Question

[Null]

Has Excel ever fixed the problem of not being able to have a foruml
result in a true "NULL" value (even though null is nothing).

I went throgh this with Excel a few years ago and to make a very lon
complicated story short, no one (techs, engineers, product development
could solve the problem. Results like 0, "", "Null", etc. will no
work for what I'm doing because it requires many tiers of calculations


I'm buying the beer if anyone can help me with this on3e

 

[Don Guillett]

one way. I'd rather have the 1mil or Wild Turkey 101

=IF(OR(I5={""," "}),1,2)

 

[Paul]

Has Excel ever fixed the problem of not being able to have a forumla
result in a true "NULL" value (even though null is nothing).

I went throgh this with Excel a few years ago and to make a very long
complicated story short, no one (techs, engineers, product development)
could solve the problem. Results like 0, "", "Null", etc. will not
work for what I'm doing because it requires many tiers of calculations.

I think the issue is what you mean by a "formula returning a null". The
usual mistake is to equate in your mind "null" and "blank". It's certainly
true that you cannot write any formula in A1 such that ISBLANK(A1) will
return TRUE. Unlike the other IS functions, ISBLANK tests the cell
properties rather than the cell value. The cell is not blank if it contains
anything, including a formula! This contrast is indeed confusing and ought
to be explained in Help. However, once understood, suitable formulas can be
constructed, even with "many tiers of calculations".

 

[Biff]

Just to add to Pauls reply:

I think alot of the confussion can be attributed to a
formula that returns the null string "". The cell is not
blank or MT because as Paul explained, it contains a
formula even though the cell formula returns the null
string and appears to be blank. The null string is in fact
a zero length text string and the cell will evaluate to
text. To further complicate matters, a formula that tests
a cell for the null string can evaluate to true thereby
leading you to believe that the cell is blank. As Paul
stated, once you understand these details, they are very
easy to deal with.

Biff

 

[Null]

Thanks for the replys.

I've tried every approach imaginable years ago without any luck. A
that time an Excel programmer told me that to add a true Null valu
would require reprogramming Excel's calculation engine. They even sai
I was the first person to ever request this function - You can'
imagine how this floored me!!! ???

It took me at least 5 minutes initially to explain to the engineer tha
I needed a true null result, not "" or any of the other variant type o
returns because they did not work for my applications. Even tried t
get their "goat" by saying Lotus could perform this function. I gues
the bottom line is that Excel still has not fixed this flaw.

A tremendous amount of flexibility is lost without this function an
the result is increased workload and lots of wasted time

 

[Null]

Our ogranization does not pay for a help line. Do you know what avenue
are currently available to contact Excel?

Question: How often do you ask for nothing (null) and have so man
problems? hah

 

[Quandan.nl]

Would the #NULL! error do the trick for you?

In that case you can trigger this error by using a formula like
=SUM(A1 B1). Yes indeed, that's a space inbetween A1 and B1. This is
accepted for intersecting ranges like (A1:C1 B1:B2), which intersect
in B1... if the ranges do not intersect, the #NULL! error is raised...
It's error value 1.

If this does the trick, please contact me for my bank account details
;-)

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl

 

[Null]

Quandan.nl, that doesn't work but thanks anyway. I'm using neste
conditional statements that must have a null outcome for a give
circumstance. The desired outcome is that "no" zero values are show
on tables given certain time restrictions. This would also solve th
problem on charts where graphing shows inappropriate zero values - thi
would eliminate periodically updating data series as the yea
progresses.

While you can get an output (e.g. table) to show nothing (excludin
charts) initially, it'll catch up with you with subsequent levels o
calculations. That's why only a true null value can provide th
desired result. Never dreamed I'd think of admitting Lotus could d
something better than Excel but this is the exception, and a major on
at that!

Signing off for the day, catch up to you guys tomorrow

 

[Paul]

Thanks for the replys.

I've tried every approach imaginable years ago without any luck. At
that time an Excel programmer told me that to add a true Null value
would require reprogramming Excel's calculation engine. They even said
I was the first person to ever request this function - You can't
imagine how this floored me!!! ???

It took me at least 5 minutes initially to explain to the engineer that
I needed a true null result, not "" or any of the other variant type of
returns because they did not work for my applications. Even tried to
get their "goat" by saying Lotus could perform this function. I guess
the bottom line is that Excel still has not fixed this flaw.

A tremendous amount of flexibility is lost without this function and
the result is increased workload and lots of wasted time.

You continue to talk in generalities, but have not yet given a single
example of what cannot be achieved because of this 'flaw'. As I tried to
explain, I believe that the flaw is that there is no explanation in Help.
This leads people to think that one thing should happen, and when it doesn't
they are confused. I'm not for one moment questioning that you have formulas
that don't work. I'm simply saying that I think they could be made to work.
Try posting a specific example.

 

[Null]

Paul,

Lets try something really simple.

You’ve set up a budget - Mar thru Jan. Time now for month en
reporting is Sep. The information you are tracking must be reported i
tabular and charted formats. Source data for the tabular report come
from other worksheets. The data source for the charted data is th
tabular report (the tabular report also supplies info to other parts o
the worksheet). Zero values are not to be shown in reporting fo
actuals past Sep. An EAC must be calculated (sample formula, e.g
=IF(AND(N6>0,O6=0),N6,IF(AND(M4>0,N4>0),"",IF(AND(M4>0,N4=0),M6+N5,IF(AND(M4=0,N4=0),M8+N5,IF(M4=0,"","Err")))))
Presentation of the tabular and charted data will display all month
of budget and 6 months of actuals.

Formulas must exist in the tabular format for all budget/actua
(monthly, and cumulative) and the EAC – Mar thru Jan. All chartin
data series must extend the full length of the data, Mar thru Jan.
Notice the problem with the EAC formulas hitting zeros - it’s almos
complete but it will never work correctly until NULL can be obtained.

SEE ATTACHED FILE FOR EXAMPLE

So there, detailed instructions and a picture. There are a millio
really trick operations that are possible (only) if a NULL value can b
derived. I look at the null feature as a toggle, it exists or it doe
not exist. If it still does not exist, then Excel fell on their fac
big time on this one. If you are not at an advanced level of Exce
don’t waste your time trying to solve this.

Best of luck (you'll need it):

 

[Alan]

You've set up a budget - Mar thru Jan. Time now for month end
reporting is Sep. The information you are tracking must be reported
in tabular and charted formats. Source data for the tabular report
comes from other worksheets. The data source for the charted
data is the tabular report (the tabular report also supplies info to
other parts of the worksheet). Zero values are not to be shown in
reporting for actuals past Sep. An EAC must be calculated (sample
formula, e.g.

=IF(AND(N6>0,O6=0),N6,IF(AND(M4>0,N4>0),"",
IF(AND(M4>0,N4=0),M6+N5,IF(AND(M4=0,N4=0),
M8+N5,IF(M4=0,"","Err"))))).

Presentation of the tabular and charted data will display all months
of budget and 6 months of actuals.

Formulas must exist in the tabular format for all budget/actual
(monthly, and cumulative) and the EAC - Mar thru Jan. All charting
data series must extend the full length of the data, Mar thru Jan.
Notice the problem with the EAC formulas hitting zeros - it's
almost complete but it will never work correctly until NULL
can be obtained.

I don't fall into the category of a great expert but nevertheless:

Why not just test against the current month around the existing
formula, and for future months, return #NA. Excel will then ignore
#NA when charting thus avoiding the zero results that you are
otherwise charting.

That is the way I have always gotten around this type of issue.

Altneratively, only chart up to and including the current month, and
use a dynamic range (defined name) in the chart source data so that
you don't have to amend the charts each month (that would drive you
mad!)

Let's be clear about this, I totally agree that it would be really
great to be able to return a NULL result from a formula, but I have
always managed to circumvent the omission without too much trouble
whenever I have come up against it.

HTH,

Alan.

 

[Paul]

I don't fall into the category of a great expert but nevertheless:

Why not just test against the current month around the existing
formula, and for future months, return #NA. Excel will then ignore
#NA when charting thus avoiding the zero results that you are
otherwise charting.

That is the way I have always gotten around this type of issue.

Altneratively, only chart up to and including the current month, and
use a dynamic range (defined name) in the chart source data so that
you don't have to amend the charts each month (that would drive you
mad!)

Let's be clear about this, I totally agree that it would be really
great to be able to return a NULL result from a formula, but I have
always managed to circumvent the omission without too much trouble
whenever I have come up against it.

HTH,

Alan.

Thanks, Alan, for this sensible reply to Null, giving a simple way of
attaining his requirements without rewriting the Excel code. As I suspected,
once we saw a specific example, the way around it was clear, which was my
central point. There's more than one way of doing most things! I totally
agree with your last paragraph: the ability to generate a NULL result from a
formula would be extremely useful, and would no doubt simplify some jobs,
but isn't essential.