Find subtotals of 160 each
 

can I get excel to run down a column of numbers(col B) which correspond to
file names in col A and when a subtotal of 160 is added up,either highlight
that cell or place that subtotal in the cell in col C next to each subtotal
of 160?

Find subtotals of 160 each
 

Bpt22 wrote:
can I get excel to run down a column of numbers(col B) which correspond to
file names in col A and when a subtotal of 160 is added up,either highlight
that cell or place that subtotal in the cell in col C next to each subtotal
of 160?

You can do this with cell formula:

In C1 put: =$B$1
In C2 put: =$C1+$B2
Drop C2 down through all your rows.

In D1 put: =INT(C1/160)
Drop this down through all your rows

In E1 put: 0
Leave F2 Blank

In E2 put: =IF($D2<$D1,E1+C2,E1)
In F2 put: =IF(E2<E1,E2,"")
Drop these down through all your rows

The F column isn't necessary, it just looks neater.

Iain

Find subtotals of 160 each
 

Wonderful!Thanks!!

" wrote:



Bpt22 wrote:
can I get excel to run down a column of numbers(col B) which correspond to
file names in col A and when a subtotal of 160 is added up,either highlight
that cell or place that subtotal in the cell in col C next to each subtotal
of 160?

You can do this with cell formula:

In C1 put: =$B$1
In C2 put: =$C1+$B2
Drop C2 down through all your rows.

In D1 put: =INT(C1/160)
Drop this down through all your rows

In E1 put: 0
Leave F2 Blank

In E2 put: =IF($D2<$D1,E1+C2,E1)
In F2 put: =IF(E2<E1,E2,"")
Drop these down through all your rows

The F column isn't necessary, it just looks neater.

Iain

Find subtotals of 160 each
 

Bpt22 wrote:
Wonderful!Thanks!!


I take it you realised that 'Leave F2 blank' should have been 'Leave F1
blank'
doh.


Iain

" wrote:



Bpt22 wrote:
can I get excel to run down a column of numbers(col B) which correspond to
file names in col A and when a subtotal of 160 is added up,either highlight
that cell or place that subtotal in the cell in col C next to each subtotal
of 160?

You can do this with cell formula:

In C1 put: =$B$1
In C2 put: =$C1+$B2
Drop C2 down through all your rows.

In D1 put: =INT(C1/160)
Drop this down through all your rows

In E1 put: 0
Leave F2 Blank

In E2 put: =IF($D2<$D1,E1+C2,E1)
In F2 put: =IF(E2<E1,E2,"")
Drop these down through all your rows

The F column isn't necessary, it just looks neater.

Iain

Find subtotals of 160 each
 

I must have been really tired yesterday. E2 should be
=IF($D2<$D1,C2,E1)

Iain