#DIV/0 how can I avoid this?

[Phylis Sophical]

In A1 to A5, I have blank cells that I will eventually insert values into. A6 contains the total of A1 to A5 example:=sum(A1:A5)

In B1 to B5, I want to divide the number in column A by
the total (A6)
formula is: =A1/A6 and so on.

The problem is that if I don't have a value in A1 to A5 yet, the the formula cells in column B show #DIV/0!
What can I put in that formula to make the B cells show nothing until there is a value in the A cells.

I have a small checkbook worksheet I got off that net that has something simular where nothing shows in the balance column until there is a date in the first column. It looks like this:
=IF(B35="","",(G34-E35+F35))
The double quotes and comma's have something to do with 'if there is not a date in the date column, don't show anything in the balance column. In other words, there is no entry there yet.

I know this is an awfully long explanation. I'm sure there is a fancy Excel word I am looking for, but that's the problem, I don't know what that's called. Can someone tell me what I need in that formula and what Excel calls it when you don't want anything to show in the formula column until there
is data entered that relates to the formula.

[barryt]

use this for cell b2
then fill it down
=IF(A1=0,0,(A1/$A$6))

[Waxaholic]

Try wrapping your formula in this:

=IF(ISERROR(your formula),"",your formula)

The quotes can be substituted with a 0 if you like.

Waxaholic

[Phylis Sophical]

That worked, thanks. barryt, why do you use 2 zero's? Is there a time you would use 1 or 3 zero's? What is the difference between using zero's and double quotes? I'm just curious as I have to do this often with varying things in the cell. Dates, dollar amounts, plain numbers, etc.

[Lighting]

On 2002-04-16 19:34, Phylis Sophical wrote:
That worked, thanks. barryt, why do you use 2 zero's? Is there a time you would use 1 or 3 zero's? What is the difference between using zero's and double quotes? I'm just curious as I have to do this often with varying things in the cell. Dates, dollar amounts, plain numbers, etc.

Hi,

Double quotes is when the cell is empty.

Lighting

[Phylis Sophical]

And what's the difference with double zero's? Why would you use one verses the other?

[Phylis Sophical]

Well I got smart and looked in Help for the error message. Apparently the double quotes denotes a text string which explains it's use in the date column of my checkbook. It would follow then that double zero's denote number strings.

On the other hand, Wax, your formula with the quote marks worked too. Thanks from the bottom line of my spreadsheet!

[barryt]

=IF (A1=0,0,(A1/$A$6))

I used a1=0 instead of a1="" because if the cell has a value of 0 instead of a blank cell you would still get the error message

If a1 =0 then it makes the cell = 0 or i you could have it make it blank

When a1 doesn't = 0 then it put the a1/$a$6 formula in and it able to calculate it

Another way that you could do the formula instead of the =IF (A1=0,0,(A1/$A$6))
Is
If($a$6 =0,0,(a1/$a$6))
This is what you would put in the cell b1
Then you could fill it down to b5

[Mark W.]

Frequently, I see IFs such as...

=IF(B1=0,0,A1/B1)

This formula has an unnecessary relational operator (bolded above) which requires additional processor time.

=IF(B1,A1/B1,0) does exactly the same thing without this comparison because Excel's logical functions treat all non-zero numeric values as TRUE and 0 as FALSE.

[Phylis Sophical]

Thanks Marc the true / false values (0=false) alone increase my learning curve by 50%. Understanding the logic of Excel is half the battle.