Formula on a dynamic range of cells
 

I have a .txt file that I import into Excel every time a file is opened. In
column A I have numbers in seconds. I want to automatically change those
numbers into minutes, either by modifying the A column or populating a new
column using a formula and leaving the old one alone.

The problem I am fighting is Excel wants to put a value into the cell. So I
have a formula applied to the entire B column, for example, that is:
"=A:A/60" This works fine for the rows of data that I have, then puts in
zeros down to the excel limit of approx. 65000 cells. I try the formula:
"=IF(A:A="","",ROUND(A:A/60,1))", and this is better, as it does not insert
zeros and therefore does not screw up my pivot table. I still "fill" the
cells with blanks down to the cell limit. Can't I tell it to apply the
formula for a dynamic range of numbers somehow? I've tried formulas like:
"=A:A(COUNT(A:A))/60", but this is incorrect syntax. Anyone have any ideas?

Thanks!

Formula on a dynamic range of cells
 

Hi

If you want to convert each entry to minutes then enter this in B1 and copy
it down:

=A1/60

Or mabye you want the result to be 'm:ss', then format column B as custom:
[m]:ss

=Time(,,A1)

Copy the formula down the used range.

Regards,
Per

"Chally72" skrev i meddelelsen
...
I have a .txt file that I import into Excel every time a file is opened. In
column A I have numbers in seconds. I want to automatically change those
numbers into minutes, either by modifying the A column or populating a new
column using a formula and leaving the old one alone.

The problem I am fighting is Excel wants to put a value into the cell. So
I
have a formula applied to the entire B column, for example, that is:
"=A:A/60" This works fine for the rows of data that I have, then puts in
zeros down to the excel limit of approx. 65000 cells. I try the formula:
"=IF(A:A="","",ROUND(A:A/60,1))", and this is better, as it does not
insert
zeros and therefore does not screw up my pivot table. I still "fill" the
cells with blanks down to the cell limit. Can't I tell it to apply the
formula for a dynamic range of numbers somehow? I've tried formulas like:
"=A:A(COUNT(A:A))/60", but this is incorrect syntax. Anyone have any
ideas?

Thanks!