|  | Simpler way to suppress missing data? | | |
| | | Square Peg |  |
|  Posted: Mon Sep 22, 2008 2:17 am Post subject: Simpler way to suppress missing data? |  |
I created a sheet 8-10 cells where the user can fill in values. There
are 20-25 cells containing the results of calculations based on those
input cells.
Initially, the input cells are blank. This causes various results in
the derivative cells. Some of them get Value errors, some get division
by zero errors, and others get zero.
I want any cell that depends other cells to show a blank until all
source cells have data.
The best way I could find to do this is to replace a formula like
=(B2*C2)+(D2*E2)
with this monstrosity
=IF(OR(B2="",C2="",D2="",E2=""),"",(B2*C2)+(D2*E2))
Isn't there some to tell Excel to run the formula if all variables
have data, otherwise return ""? That is, just what I did above, but
with just the formula in the cell? |
| |
| | | T. Valko | |
| | Posted: Mon Sep 22, 2008 3:22 am Post subject: Re: Simpler way to suppress missing data? | |
| |  | |
| Quote: | The best way I could find to do this is to replace a formula like
=(B2*C2)+(D2*E2)
with this monstrosity
=IF(OR(B2="",C2="",D2="",E2=""),"",(B2*C2)+(D2*E2))
|
That's hardly a monstrosity!
You can repace the OR with something slightly more compact:
=IF(COUNT(B2:E2)<4,"",(B2*C2)+(D2*E2))
--
Biff
Microsoft Excel MVP
"Square Peg" <SquarePeg@Round.Hole> wrote in message
news:t86ed4lt7eu9mo968mtcprhulldhl1vj4g@4ax.com...
| Quote: | I created a sheet 8-10 cells where the user can fill in values. There
are 20-25 cells containing the results of calculations based on those
input cells.
Initially, the input cells are blank. This causes various results in
the derivative cells. Some of them get Value errors, some get division
by zero errors, and others get zero.
I want any cell that depends other cells to show a blank until all
source cells have data.
The best way I could find to do this is to replace a formula like
=(B2*C2)+(D2*E2)
with this monstrosity
=IF(OR(B2="",C2="",D2="",E2=""),"",(B2*C2)+(D2*E2))
Isn't there some to tell Excel to run the formula if all variables
have data, otherwise return ""? That is, just what I did above, but
with just the formula in the cell? |
|
| |
| | | Square Peg | |
| | Posted: Mon Sep 22, 2008 3:42 am Post subject: Re: Simpler way to suppress missing data? | |
| | | |
On Mon, 22 Sep 2008 01:22:02 -0400, "T. Valko"
<biffinpitt@comcast.net> wrote:
| Quote: | The best way I could find to do this is to replace a formula like
=(B2*C2)+(D2*E2)
with this monstrosity
=IF(OR(B2="",C2="",D2="",E2=""),"",(B2*C2)+(D2*E2))
That's hardly a monstrosity!
|
Well, OK, I might have exaggerated a tad. I did simplify the
expression. The real one involves a longer exression and a bunch of
named cells.
| Quote: | You can repace the OR with something slightly more compact:
=IF(COUNT(B2:E2)<4,"",(B2*C2)+(D2*E2))
|
That's a clever alternative, but requires that the cells be in a
range. I guess I simplified the expression too much.
So, is there no way to tell Excel to do the work? It knows which cells
have no data. I was hoping that maybe some type of conditional
formatting would work. Something like, "Valid data" or "Source cells
not blank". Excel has the new Conditional Formatting button, but I
couldn't find a combination that would do it.
I'd much much prefer to put the actual formula in the cell and the
error handling elsewhere. Better documentation and all that.
I guess the alternative is to write a macro. Then I can check each
value manually. Seems like a waste of programming time when Excel
already knows the answer. I would think that this would come up quite
often. |
| |
| | | Roger Govier | |
| | Posted: Mon Sep 22, 2008 6:01 am Post subject: Re: Simpler way to suppress missing data? | |
| | | |
Hi
I believe you are using XL2007.
You could use use the new IFERROR() function.
=IFERROR(your_formula,"")
--
Regards
Roger Govier
"Square Peg" <SquarePeg@Round.Hole> wrote in message
news:2bbed45p9psva8alqucsma1t2rkb0fcnv4@4ax.com...
| Quote: | On Mon, 22 Sep 2008 01:22:02 -0400, "T. Valko"
biffinpitt@comcast.net> wrote:
The best way I could find to do this is to replace a formula like
=(B2*C2)+(D2*E2)
with this monstrosity
=IF(OR(B2="",C2="",D2="",E2=""),"",(B2*C2)+(D2*E2))
That's hardly a monstrosity!
Well, OK, I might have exaggerated a tad. I did simplify the
expression. The real one involves a longer exression and a bunch of
named cells.
You can repace the OR with something slightly more compact:
=IF(COUNT(B2:E2)<4,"",(B2*C2)+(D2*E2))
That's a clever alternative, but requires that the cells be in a
range. I guess I simplified the expression too much.
So, is there no way to tell Excel to do the work? It knows which cells
have no data. I was hoping that maybe some type of conditional
formatting would work. Something like, "Valid data" or "Source cells
not blank". Excel has the new Conditional Formatting button, but I
couldn't find a combination that would do it.
I'd much much prefer to put the actual formula in the cell and the
error handling elsewhere. Better documentation and all that.
I guess the alternative is to write a macro. Then I can check each
value manually. Seems like a waste of programming time when Excel
already knows the answer. I would think that this would come up quite
often. |
|
| |
| | | Square Peg | |
| | Posted: Mon Sep 22, 2008 6:56 am Post subject: Re: Simpler way to suppress missing data? | |
| | | |
On Mon, 22 Sep 2008 01:02:53 -0700 (PDT), Pete_UK
<pashurst@auditel.net> wrote:
| Quote: | Why not use conditional formatting on the cell with the error in,
using Formula Is with this formula:
=ISERROR(cell)
and then setting the foreground colour to white to match the
background colour, so that the cell appears blank.
|
I tried several things. They are all in this workbook, which I
uploaded to SendSpace:
LINK
The sheet has a little table as follows:
B C D E
3 a b a*b Rule
4 2 3 4 cell contains error --> font=red
5 3 4 12 cell contains error --> custom="error"
6 2 4 8 iserror(D6) --> font=red
7 3 5 15 iserror(cell) --> font=red
8 1 2 2
1. Row 4: I set up a conditional format for "Cell contains" and
selected "Errors". I chose to format the text as red (so I could see
it). This works. White also works as you suggested.
2. Row 5: I set up the same conditional format conditions, but this
time I chose a Custom format being the text "Error". What happens here
is bizarre:
a. With numbers in B4:C4, D4 is the product.
b. Delete either number (leaving a space), and D4 gets "#VALUE!".
c. Put the number back and D4 gets "error", my custom format.
d. Put any number in again, and D4 gets the product. This also
works if go to Sheet 2 and back.
3. In Row 6 & 7, I tried to do what you suggest, but I don't know how
to code the ISERROR formula.
The Row 4 solution seems to work. I would like to know why the others
don't.
Suggestions? |
| |
| | | Square Peg | |
| | Posted: Mon Sep 22, 2008 7:08 am Post subject: Re: Simpler way to suppress missing data? | |
On Mon, 22 Sep 2008 09:01:06 +0100, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:
| Quote: | Hi
I believe you are using XL2007.
You could use use the new IFERROR() function.
=IFERROR(your_formula,"")
|
Yes, I am using 2007.
Your solution works. Thanks.
But the Help indicates that it should not work. It shows an example
very much like mine but with a quotient (B2/C2). It says that if B2
contains "", no error will be returned because it will take it to be
zero. If C2 is "", then a divide by zero error will occur.
When I tested it (=iferror(B2/C2,"Error"), either one got the error
and produced the "Error" text. |
| |
| | | Pete_UK | |
| | Posted: Mon Sep 22, 2008 8:02 am Post subject: Re: Simpler way to suppress missing data? | |
| | | |
Why not use conditional formatting on the cell with the error in,
using Formula Is with this formula:
=ISERROR(cell)
and then setting the foreground colour to white to match the
background colour, so that the cell appears blank.
Hope this helps.
Pete
On Sep 22, 6:42 am, Square Peg <Square...@Round.Hole> wrote:
| Quote: | On Mon, 22 Sep 2008 01:22:02 -0400, "T. Valko"
biffinp...@comcast.net> wrote:
The best way I could find to do this is to replace a formula like
=(B2*C2)+(D2*E2)
with this monstrosity
=IF(OR(B2="",C2="",D2="",E2=""),"",(B2*C2)+(D2*E2))
That's hardly a monstrosity!
Well, OK, I might have exaggerated a tad. I did simplify the
expression. The real one involves a longer exression and a bunch of
named cells.
You can repace the OR with something slightly more compact:
=IF(COUNT(B2:E2)<4,"",(B2*C2)+(D2*E2))
That's a clever alternative, but requires that the cells be in a
range. I guess I simplified the expression too much.
So, is there no way to tell Excel to do the work? It knows which cells
have no data. I was hoping that maybe some type of conditional
formatting would work. Something like, "Valid data" or "Source cells
not blank". Excel has the new Conditional Formatting button, but I
couldn't find a combination that would do it.
I'd much much prefer to put the actual formula in the cell and the
error handling elsewhere. Better documentation and all that.
I guess the alternative is to write a macro. Then I can check each
value manually. Seems like a waste of programming time when Excel
already knows the answer. I would think that this would come up quite
often. |
|
| |
| | | Roger Govier | |
| | Posted: Mon Sep 22, 2008 12:26 pm Post subject: Re: Simpler way to suppress missing data? | |
| | | |
Hi
I don't think you can have tested it correctly. It does behave as it should.
With Nothing in B2 and 1 in C2 I get 0
With 1 in B2 and Nothing in C2 I get Error
I think you must have a space in B2 and nothing in C2 - that will produce
Error.
--
Regards
Roger Govier
"Square Peg" <SquarePeg@Round.Hole> wrote in message
news:6uned45o36o0df7f08act20e0l7n496v0m@4ax.com...
| Quote: | On Mon, 22 Sep 2008 09:01:06 +0100, "Roger Govier"
roger@technology4unospamdotcodotuk> wrote:
Hi
I believe you are using XL2007.
You could use use the new IFERROR() function.
=IFERROR(your_formula,"")
Yes, I am using 2007.
Your solution works. Thanks.
But the Help indicates that it should not work. It shows an example
very much like mine but with a quotient (B2/C2). It says that if B2
contains "", no error will be returned because it will take it to be
zero. If C2 is "", then a divide by zero error will occur.
When I tested it (=iferror(B2/C2,"Error"), either one got the error
and produced the "Error" text. |
|
| |
| | | Square Peg | |
| | Posted: Mon Sep 22, 2008 1:52 pm Post subject: Re: Simpler way to suppress missing data? | |
On Mon, 22 Sep 2008 15:26:42 +0100, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:
| Quote: | Hi
I don't think you can have tested it correctly. It does behave as it should.
With Nothing in B2 and 1 in C2 I get 0
With 1 in B2 and Nothing in C2 I get Error
I think you must have a space in B2 and nothing in C2 - that will produce
Error.
|
You are right. I had a space in the cell, not an empty cell.
Unfortunately, this means that IFERROR won't do what I need done. I
was looking for a way to suppress all output if any of the independent
variable cells do not have values, including being empty.
Sadly, this means that the conditional formatting solution that I
thought was working, is not for the same reason. (rats)
Thanks for the tip. It's a useful function. |
| |
|
|
