Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find subtotals of 160 each
I must have been really tired yesterday. E2 should be
=IF($D2<$D1,C2,E1) Iain |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals problem: Excel 2003 (not, AFAIK, the nested subtotals bug) | Excel Discussion (Misc queries) | |||
Original subtotals should not be within nested subtotals in excel | Excel Worksheet Functions | |||
Subtotals: Nested subtotals below higher subtotal | Excel Discussion (Misc queries) | |||
Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) | |||
why are nested subtotals coming out below outer subtotals? | Excel Worksheet Functions |