Excel 2010 Formula Conundrum. Referencing a "" cell in a further formula 1

[PWD]

Good afternoon. I have a reporting worksheet where in one cell(1) if the sum of the other sheets is 0 then put in "" rather than populate the cells with zeroes all over the place. I then have another cell(2) where I have to calculate the percentage rate from cell(1)/cell(3), which is the total of that particular row. If cell(1) is "" then I get a #VALUE error as it's trying to divide "" by something numeric - whereas I need it to be 0%.

Hope this makes sense!

Cell(1); Cell K49
=IF('SheetA'!K49 + 'SheetB'!K49=0,"",'SheetA'!K49 + 'SheetB'!K49)

Cell(3) Cell I49
5

Cell(2) Cell J49
=K49/I49

Therefore ""/5 equals error!!! But I need it to display 0% as there were no data.

Any ideas please?



Many thanks,
D€$

 

[zelgar]

You have 2 options as I see it.
1. In your IF statement in Cell K49, change the "" to 0 but have a conditional format in the column not to see the 0 (e.g., if the cell = 0 change the font of the character to be the same as the background)
2. Alternately, have cell J49 have an IF statement =IF(K49="",0,K49/I49)

 

[1726]

Drop the IF function in cell K49 and keep the zeros - but hide them properly using a custom number format.

Custom number formats are created and applied using the Number tab of the Format Cells dialog box.

Format K49 with a custom number format such as: 0;-0;

where 0 - is the format for +ve Numbers
;-0 - is the format for -ve numbers
; is the format that will Hide zeros

if you want 2 decimal places use: 0.00;-0.00;

if you want commas separating thousands(e.g currency) use: #,##0.00;-#,##0.00;

etc.

 

[PWD]

Hi (mysteriously-named) 1726. Very, "The Prisoner"!!

Well that's formatting I've never seen/used before!

An elegant result! Many thanks,

Many thanks,
D€$

 

[PWD]

But; there's always a "but"! I also have Conditional Formatting that looks for blanks that now doesn't kick in. Ah, got it. Put a new one in that has no formatting if = 0 and then stop. It's good to talk. Thanks for listening!!

Shoot, that doesn't appear to work; I'll start a new thread.

Many thanks,
D€$

 

[1726]

the ISBLANK function distinguishes between an empty cell and a hidden zero

Also the New/Edit formatting rule dialog box (conditional formatting) supports an option to format Blank or No Blank cells

Select rule type: Format only cells that contain

Then select: Blank / No Blank whichever suits your needs

 

[Deniall]

I have often used a slight variant of 1726's pos;neg;zero[ ] formatting for cases where the numbers are in a table.[ ] This uses the ANSII code 0183 character[ ](·) to represent the zero cases, by using a custom format along the lines of
[ ][ ][ ]0;-0;"·[ ]"

This approach has two advantages for me:
» [ ]The presence of the · rather than a blank cell is a visual aid to preserving the sense of the lines and columns.[ ] Yet the visual "presence" of the[ ]· is very light.
» [ ]I have a minor aversion to having ANY cell with content appearing to be blank.[ ] (On far to many occasions this has led to me or another user inadvertently deleting the cell, often when deleting an entire row or an entire column.)

In some contexts I prefer to use a minus sign or an n-dash instead of the[ ]·.

Doubtless others will disagree.

 

[bluedragon2]

In cell J49 you can put:

=IF('SheetA'!K49 + 'SheetB'!K49=0,"",K49/I49)

and not worry about the formatting.

But, all you are doing is working off the original formula which is OK for reporting, but if you need to further utilize data from this page, then you will run into the same issue.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[madonnac]

ISERROR is an optimized version of IF(ISERROR(

Cell(2) Cell J49
=K49/I49

would become

Cell(2) Cell J49
=IFERROR(K49/I49,"")

Note that this will trap all errors, so things like #VALUE! , #NA! and #ERR! will also be caught