Excel Help

JohnSwift10

I have an excel spreadsheet with 1537 rows and 2 columns which repeat every 48 rows (time from 00:30 to 00:00 in column 1 and variable amounts in column 2) which I want to add up so that I get the total of each set of 48 numbers in column 2.

How do I automatically add a row every 48 rows and total each 48 rows?

At present I just manually insert a row every 48 rows and total each 48 rows but I would like to automate it if possible.

Chris_English

I'd use the SUMIF function. In a new column put each of the unique time values, then (if we assume your repeating times are in column A, the variable data is in column B, and the unique set of 48 times is in column D then the formula in column E1 would be =SUMIF(A$1:A$1537,D1,B$1:B$1537)

You then copy this formula down to cells E1 to E48.

[Deleted User]

JohnSwift10 said:

I have an excel spreadsheet with 1537 rows and 2 columns which repeat every 48 rows (time from 00:30 to 00:00 in column 1 and variable amounts in column 2) which I want to add up so that I get the total of each set of 48 numbers in column 2.

How do I automatically add a row every 48 rows and total each 48 rows?

At present I just manually insert a row every 48 rows and total each 48 rows but I would like to automate it if possible.

Sounds like it would be better to organise the data in columns instead - ie 48 rows but then each time it repeats, do it in a new column.

Does sound like the data needs another index as such, why are the 48 divisions repeating and not unique - should there be a date for example?

JohnSwift10

the first column is date/times from 00:30 to 00:00 in half hour increments hence 48 rows
the data is actually my gas usage for 32 days from 3rgy.com

I want to compare daily usage and usage at a particular time of the day,

I just thought there must be an easier way than copying a formula 32 times and making sure it is copied to the correct cell.

[Deleted User]

JohnSwift10 said:

the first column is date/times from 00:30 to 00:00 in half hour increments hence 48 rows
the data is actually my gas usage for 32 days from 3rgy.com

I want to compare daily usage and usage at a particular time of the day,

I just thought there must be an easier way than copying a formula 32 times and making sure it is copied to the correct cell.

Ok so makes more sense now I know what the data is.

Just use a pivot table, all done in a few clicks:



Put the row as date, values as consumption then select the rows go in PivotTable Analyze > Group Selection > Days

All done.

victor2

There are various tools in Excel which can analyse the data for you, and you could make life easier by putting the data in columns for each day, as already suggested. But if you want a formula to apply to the data as it is, the following SUMIFS formula will provide a total at the last reading of each day, and zero elsewhere (which you can always format out if you want):

=SUMIFS(B:B,A:A,">="&DATE(YEAR(A1),MONTH(A1),DAY(A1)),A:A,"<"&DATE(YEAR(A2),MONTH(A2),DAY(A2)))

Column A is the date/time, B the number you want to total. The formula can be copied down alongside the data, starting from row 1. Note it does look ahead at the next row.

I'm sure there is a more elegant approach, but this works with my Excel 2010!

Chris_English

JohnSwift10 said:

the first column is date/times from 00:30 to 00:00 in half hour increments hence 48 rows
the data is actually my gas usage for 32 days from 3rgy.com

I want to compare daily usage and usage at a particular time of the day,

I just thought there must be an easier way than copying a formula 32 times and making sure it is copied to the correct cell.

You don't need to copy it 30 times; you write it once, click and hold the bottom left corner of the cell, and drag it down.

Chris_English

victor2 said:I'm sure there is a more elegant approach, but this works with my Excel 2010!

Maybe the one I suggested above?

victor2

Chris_English said:

victor2 said:I'm sure there is a more elegant approach, but this works with my Excel 2010!

Maybe the one I suggested above?

You still need a formula to copy though.

Pivot Table is the elegant way - as described above!

JohnSwift10

Now to figure out how to do a pivot table

victor2 said:

=SUMIFS(B:B,A:A,">="&DATE(YEAR(A1),MONTH(A1),DAY(A1)),A:A,"<"&DATE(YEAR(A2),MONTH(A2),DAY(A2)))

that formula gives 0 as the answer

victor2

JohnSwift10 said:

Now to figure out how to do a pivot table

victor2 said:

=SUMIFS(B:B,A:A,">="&DATE(YEAR(A1),MONTH(A1),DAY(A1)),A:A,"<"&DATE(YEAR(A2),MONTH(A2),DAY(A2)))

that formula gives 0 as the answer

It won't on the last row of date/time stamps for the day.