Copy the formula not the cell data?

[J Hawes]

I am trying to copy a formula to all the cells in a column by using
copy/paste. It copies the formula correctly but it also copies the data
from the selected cell instead of leaving it blank.

For example, if the cell used for the copy contains the number 40, then 40
appears in all the copied cells. The formula itself is correct.

This function is for calculating the a sum of hours and minutes. e.g. 2
hours 30 minutes + 1 hour 50 minutes = 4 hours 20 minutes. The formula
works just fine.

Here is the Hours formula:

=IF((E9+C10)>60, D9+B10+1,D9+B10)

Here is the Minutes formula:

=IF((E9+C10)>60,E9+C10-60,E9+C10)

I think I need to format the formula so that it says logically...

=IF (B10+C10) <>0 THEN (the formulae above) i.e. if the hours and minutes
for the row are not blank then do the calculation, but I don't know the
syntax for this.

Thank you very much for any help.

Joan

 

[JulieD]

Hi

from the sound of it your calculation might be set to manual rather than
automatic .. .try tools / options / calculations and ensure automatic is
selected .. now when you copy a formula to another cell, the formula should
evaluate the cells it refers to.

Let us know if this helped
Cheers
JulieD

 

[David McRitchie]

Hi Joan,
Aside from the problem you mentioned, I think the calculations and
display would be easier on yourself if you used Excel time rather than
trying to store time as 2 digit hours component, and 2 digit minutes
component in separate cells.

 

[J Hawes]

The calculation option was already set to automatic. Sorry.

Joan

 

[J Hawes]

It isn't the clock time I am summing, it is the accumulated number of
hours/minutes taken to complete a task.

Example:

Row 1 Task hours Task minutes Hours-to-date Minutes-to-date
Row 2 2 30 2
30
Row 3 4 45 7
15

Does this help?

Joan


"David McRitchie" <d

(e-mail address removed)> wrote in message

 

[David McRitchie]

Perhaps your formula had a leading space in it turning it to text.

=IF((E9+C10)>60, D9+B10+1,D9+B10)

Here is the Minutes formula:
=IF((E9+C10)>60,E9+C10-60,E9+C10)

The following would allow insertion/deletion of rows and allow you
to start at row 2 because SUM ignores cells with text

C2: =SUM(OFFSET(C2,-1,0),A2,INT((SUM(OFFSET(D2,-1,0),B2))/60))
D2: =MOD(SUM(B2,OFFSET(D2,-1,0)),60)


what I was suggesting was:

A1: 'task_hh:mm
B1: 'total_hhmm
A2: 02:30
B2: =SUM(OFFSET(B2,-1,0),A2) format as [h]:mm
A3: 04:45
B3: =SUM(OFFSET(B3,-1,0),A3) format as [h]:mm

 

[J Hawes]

Hi David,

Actually, I just got the *&%(&^% thing to work, using these formulas:

Hours =IF(B10+C10=0," ",IF(E9+C10>59,D9+B10+1,D9+B10))

Minutes =IF(B10+C10=0," ",IF(E9+C10>59,E9+C10-60,E9+C10))

Many thanks to all for your help. I really do appreciate this newsgroup.

Joan

Perhaps your formula had a leading space in it turning it to text.

=IF((E9+C10)>60, D9+B10+1,D9+B10)

Here is the Minutes formula:
=IF((E9+C10)>60,E9+C10-60,E9+C10)

The following would allow insertion/deletion of rows and allow you
to start at row 2 because SUM ignores cells with text

C2: =SUM(OFFSET(C2,-1,0),A2,INT((SUM(OFFSET(D2,-1,0),B2))/60))
D2: =MOD(SUM(B2,OFFSET(D2,-1,0)),60)


what I was suggesting was:

A1: 'task_hh:mm
B1: 'total_hhmm
A2: 02:30
B2: =SUM(OFFSET(B2,-1,0),A2) format as [h]:mm
A3: 04:45
B3: =SUM(OFFSET(B3,-1,0),A3) format as [h]:mm



--

It isn't the clock time I am summing, it is the accumulated number of
hours/minutes taken to complete a task.

Example:

Row 1 Task hours Task minutes Hours-to-date Minutes-to-date
Row 2 2 30 2
30
Row 3 4 45 7
15

Does this help?

Joan


"David McRitchie" <d

(e-mail address removed)> wrote in message