IF, ISBLANK, ETC.

[Maureen]

I have a table listing 6 names in rows in column A,
corresponding 2003 data in column B and corresponding data
in column C. In column D, I would like to show the
percentage increase or decrease between 2003 and 2004, and
where data is missing either in 2003 and/or 2004, show
100%. I'm using the following formula:
IF(ISBLANK(B55),100%,(C55-B55/C55). This is fine but how
do I modify my formula to cover the possibility of a blank
cell in column C and the possibility of blank cells in
both column B and C in the same row?

Thanks

 

[Bob Phillips]

Maureen,

Try this

=IF(OR(ISBLANK(B55),ISBLANK(C55)),100%,(C55-B55)/C55)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

Try this

=IF(OR(ISBLANK(B55),ISBLANK(C55)),100%,(C55-B55)/C55)

...

3 function calls? Tsk, tsk.

=IF(COUNTA(B55:C55)<2,100%,(C55-B55)/C55)

 

[Bob Phillips]

...
..

3 function calls? Tsk, tsk..

How remiss of me, I have been looking to reduce that recently, but I lapsed
(I'm only human, really!)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

((bottom posted and snipped)

 

[Maureen]

I'm still having difficulty with this.

With both formulas I get the following results:

1. When values are present in B55 and C55, the results are
correct.
2. When values are present in B55, but C55 is blank or 0,
the result is #DIV/0! I need "100%" as the response.
3. When values are present in C55, but B55 is blank, the
result is #VALUE! I need "100%" as the response.
4. When both B55 and C55 are blank or 0, the result is
#VALUE! I need a blank here.

What am I doing wrong?

 

[Bob Phillips]

Maureen,

You didn't mention 0 as a possible value. This is easily circumvented with
=IF(OR(IS(B55),ISBLANK(C55),B55=0,C55=0),100%,(C55-B55)/C55)

The other issue means that B55 is not blank, it must contain at least one
space. This is harder to manage as there is no knowing how many spaces it
will contain. Just make sure that you leave it blank or put zero in..

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

You didn't mention 0 as a possible value. This is easily circumvented with
=IF(OR(IS(B55),ISBLANK(C55),B55=0,C55=0),100%,(C55-B55)/C55)

Typo at IS(...). A shorter alternative, since blank cells evaluate 0 in numeric
contexts,

=IF(N(B55)*N(C55),(C55-B55)/C55,100%)

 

[Bob Phillips]

...

Typo at IS(...). >

Where did that go?

A shorter alternative, since blank cells evaluate 0 in numeric
contexts,
=IF(N(B55)*N(C55),(C55-B55)/C55,100%)

Who cares that it's shorter, it solves the embedded spaces problem, that
makes it ideal.

Bob

 

[Maureen]

I'm sorry I must be thick but I'm getting "100%" when both
B55 and C55 are blank.

If this helps:

1. B55 is inputted manually.
2. C55 is now formulated to show blank ("") if the
referenced cell is blank ("").
3. When either B55 or C55 has a value, D55 has to show the
result (C55-B55)/C55.
4. When either B55 or C55 is blank, D55 has to equal 100%.
5. When both B55 and C55 are blank, D55 has to be blank.

Help.

 

[Harlan Grove]

I'm sorry I must be thick but I'm getting "100%" when both
B55 and C55 are blank.

If this helps:

1. B55 is inputted manually.
2. C55 is now formulated to show blank ("") if the
referenced cell is blank ("").
3. When either B55 or C55 has a value, D55 has to show the
result (C55-B55)/C55.
4. When either B55 or C55 is blank, D55 has to equal 100%.
5. When both B55 and C55 are blank, D55 has to be blank.

Sorry, didn't read your follow-up carefully.

Does C55 contain a formula?

#3 and #4 are inconsistent, and #3 is suspect in any case. If C55 doesn't
contain a nonzero numeric value, (C55-B55)/C55 will evaluate either #VALUE! or
#DIV/0!. #3's condition should be when *both* B55 and C55 have values, shouldn't
it?

If I'm correct about #3, then try

=CHOOSE(1+COUNT(B55:C55),"","100%",1-B55/C55)

which will still return #DIV/0! when C55 equals zero. Since 1-B55/C55 = 100%
when B55 = 0, if you want to guard against #DIV/0! when C55 = 0, try

=CHOOSE(1+COUNTIF(B55:C55,">0"),"","100%",1-B55/C55)

 

[Guest]

Yes, C55 has a formula: IF(G32=0;"";G32).

I want to keep the Zero Value Option in "Tools, Options"
in case I might want to display the "0" result.

You are correct. Item 3 should be "when both B55 and C55
have values, D55 should compute".

Using the second formula (with COUNTIF), these are the
results I get:

1. If B55 is blank and C55 has a value, D55=100%. OK
2. If B55 has a value and C55 is blank, D55=100%. OK
3. If B55 and C55 have values, D55=100%. NO, it should
compute.
4. If both B55 and C55 are blank, D55=100%. NO, it should
be blank.

Is the formula in C55 the problem?

Thanks.

 

[Harlan Grove]

Yes, C55 has a formula: IF(G32=0;"";G32). ...
You are correct. Item 3 should be "when both B55 and C55
have values, D55 should compute".

Using the second formula (with COUNTIF), these are the
results I get:

1. If B55 is blank and C55 has a value, D55=100%. OK
2. If B55 has a value and C55 is blank, D55=100%. OK
3. If B55 and C55 have values, D55=100%. NO, it should
compute.

Not for me. With 1 in B55 and 5 in C55, the formula returns 80% on my machine.
Do you have recalculation set to automatic? Is your B55 value much less than
your C55 value (e.g., B55/C55 < 0.005) while the cell containing this formula is
formatted as percentage with no decimal places?

4. If both B55 and C55 are blank, D55=100%. NO, it should
be blank.

Again, not for me. When both cells are blank, my formula on my machine evaluates
to "". It really looks like you have recalculation set to manual. What happens
when you press [F9]?

 

[Maureen]

SORREE! I was using the formula for line 55 on line 54
without modifying it. It works perfectly now. It must be
the coffee in Québec. Merci beaucoup!!!

In my example, B55=300,000 and C55=759,500 and D55=

-----Original Message-----

Yes, C55 has a formula: IF(G32=0;"";G32). ...
You are correct. Item 3 should be "when both B55 and C55
have values, D55 should compute".

Using the second formula (with COUNTIF), these are the
results I get:

1. If B55 is blank and C55 has a value, D55=100%. OK
2. If B55 has a value and C55 is blank, D55=100%. OK
3. If B55 and C55 have values, D55=100%. NO, it should
compute.

Not for me. With 1 in B55 and 5 in C55, the formula returns 80% on my machine.
Do you have recalculation set to automatic? Is your B55 value much less than
your C55 value (e.g., B55/C55 < 0.005) while the cell containing this formula is
formatted as percentage with no decimal places?

4. If both B55 and C55 are blank, D55=100%. NO, it should
be blank.

Again, not for me. When both cells are blank, my formula on my machine evaluates
to "". It really looks like you have recalculation set to manual. What happens
when you press [F9]?