#DIV/0 how can I avoid this?

Phylis Sophical

New Member
Joined
Jan 13, 2002
Messages
42
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try wrapping your formula in this:

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

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

Waxaholic
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
=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
 
Upvote 0
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.
 
Upvote 0
Thanks Marc the true / false values (0=false) alone increase my learning curve by 50%. Understanding the logic of Excel is half the battle.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top