Auto Formatting Problem with Formula and #DIV/0! error in calculating Percentage

[Woolgatherer]

ROW A filled with numbers

Sum of ROW A is the last number.


In ROW C i want to calculate the value of ROW A divide by Sum of ROW A, that is the last number.


Individually by putting formula it works but not filling automatically and gives error #DIV/0!


Can someone suggest how to solve, need to calculate percentage and want the sum value to be placed their only.

 

[Montrey]

Don't divide by zero then.


Can you please post a sample workbook.

 

[Woolgatherer]

[pre]

Column A              Column B
10                    0.2       (=A1/A5)
14                    #DIV/O!   (Auto formatting or drag drop gives this)
6
20
50  (THE SUM)

[/pre]

I have lot of data like this. Can't put formula in every cell.Please give a solution if i want that Sum to be their in the last Row.

 

[Hui]

Woolgatherer


Change your formula from =A1/A5

to: =IFERROR(A1/A5,"-")

Then copy down

 

[NARAYANK991]

Hi ,


First , there seems to be some confusion over rows and columns ; which do you mean ?


If you mean columns , then where does the data in column A end ? After 5 rows , 10 rows or is it dynamic ?


If it is dynamic , then defining a named range can ensure that irrespective of the extent of the data range , the last data cell in the column can be accessed.


One more question : how do you ensure that the last cell in the column contains the SUM of all the other numbers before it ?


If you can use absolute addressing for accessing the last cell ( which contains the SUM ) , then dragging down will not result in a #DIV/0! error ; at present you are getting this error because when you drag your formula down , A1/A5 becomes A2/A6 , A3/A7 and so on ; if A6 , A7 , and beyond do not have any data in them , the result will be a #DIV/0! error.


Narayan

 

[Luke M]

As Narayan pointed out, I think it's just a relative/absolute reference problem. change your formula to:

=A1/A$5