Zero divisor problem

[boblock1]

I have a problem that hopefully some of you more technically gifted than myself can help me with.

I have been using the following formula to calculate a percentage figure for some large spreadhseets that I am working on:
=IF(L5=0,"",J5/L5)

For some reason, the formula started giving me #DIV/0 errors. Excel help says use the formula that I am using, which doesn't help much. The divisor in these equations is zero.

Thanks for any help you can give me.

Bob

 

[SimonP]

Bob,
Are you sure that the divisor is a numeric zero? This can happen if the 'number' is actually text - usually if the data was imported from another source.
Try selecting one of the rows that gives the error and overtyping the zero in the divisor cell with a zero.

Simon

 

[boblock1]

Simon,

You are right, thanks. The data came from an Access db, and the spreadsheet is listing them as text. How can I run a macro that will convert the text to numbers?

Bob

 

[SimonP]

The quick (non macro) way is to use Excel's VALUE formula to convert all of the numbers in column L to a value.

i.e. in a blank column enter
=VALUE(L5)
copy this down for every row of data, then copy the whole column & paste it back on top of column L using paste special>values.

Simon

 

[Mark W.]

A quicker, non-macro way would be to use the Data | Text to Columns... menu command.