Microsoft Excel-- hide sums with no answers?

Question

Is there a way to hide the sum of a formula where the variables don't exist yet? Here's the scenario:

I use excel to track parts expenses & labor hours (and expenses) at work. I have my budget master sheet filled with my formulas, however, it brings up "#DIV/0!" where my percentages are at, becuase there is nothing in the column above it yet (because that work week doesn't exist yet). Is there a way I can make that disappear, without having to type it in every week? Same with the variables going left to right-- the formula is already in place, to subtract the budget amount from the actual amount, so it shows negative budget amount, and I'd like it to stay blank, without copying and pasting (or retyping) the formula every payweek.

Any help would be great! Thanks!

Here's an example of my formula:

D11 will eventually contain a dollar amount to represent labor income, and D12 would contain a negative dollar amount representing shop wages at that time also. D14 then contains the formula to show the labor margin (D11-D12) and then D15 shows the percentage of income.

Answer 1

You haven't shown a sample of your formula so for this example below, substitute "My_Formula" accordingly.

IF (ISERROR(My_Formula), "", My_Formula)


It really is that simple!

Kind Regards,
QwertyKPH @ Yahoo!

Answer 2

I'm using the 2007 version. Launching Excel to find out now.

Answer 3

Change the color of the font. I hope you didn't give me a thumbs down..since, I was trying to find my manual....


Anyway,
You can "Hide"...it is a technique that is used to hide data that isn't relevant to a particular report or sensitive data that you do not want others to see.

Decrease the column or row width to 0 (or select a range and then press CTRL+0). This can hide either column or row.

It often gets frustrating trying to use the mouse to unhide a range of columns or rows...An alternative to unhide is by selecting the columns or rows to the right and left (north and south--depending) of the hidden ones and then press "CTRL+SHIFT+)"

Hope this helps.

Answer 4

take your formula and put it into a "IIF" clause where the criteria checks the cell and use the function ISBLANK or '0' then if its true then the answer is '0', false will run the original formula. so it would look something like this

=IIF(ISBLANK(D1), 0, 'original formula')

you can replace the '0' with whatever you want

Answer 5

Use an "if" to see if you are going to divide by zero. In other words, instead of this (assuming A11 is zero):

=sum(A1:A10)/A11

Do this (to display an empty cell):

=if(A11<>0,sum(A1:A10)/A11,"")

Or this (to display a zero):

=if(A11<>0,sum(A1:A10)/A11,0)


The "if" has three parts:
A condition that must evaluate to true or false
What to do (or display) if the condition is true
What to do (or display) if the condition is false

Answer 6

Use the iserr function

http://downloads.sgkopi.com/iserr_function.xls