Excel help?

david1946

I have a spreadsheet of my electricity usage from ngr3y and want to compare usage between certain times of the day such as 19:00 to 00:00.

I want to sum cells B40:B50 then B88:B98 B146:B156 all the way down to B2008:B2018.

The only way I can think of doing this at present is to put =sum(b40:B50) in cell C50 then manually copying this down to cell C98 then C146 all the way down.

This is laborious, there must be an easier way to do this.

seanfos

A Pivot Table (https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576) will allow you to analyse and filter your data in many powerful ways

Sean

Cornucopia

How are you going to display the results?   

You could just number the rows starting with 0 at row 40, and then use something like this

=if(mod(C40,48)=0,sum(D40,D50),"")

And then copy that all the way down.   Column C would be your index, and column D your data.  

[Deleted User]

I've answered this in detail on another thread recently:

https://forums.moneysavingexpert.com/discussion/comment/78947450#Comment_78947450

david1946

hi tallmansix I've managed to make pivot tables and can get totals for days but how do I make a pivot table that will show me the totals of a partial day like between 19:00 and 23:30.

the reason for this is because when I go out in  the evening I turn the heating off so I want to compare the usage as against the evenings when I don't turn the heating off.

david1946

Cornucopia said:

How are you going to display the results?   

You could just number the rows starting with 0 at row 40, and then use something like this

=if(mod(C40,48)=0,sum(D40,D50),"")

And then copy that all the way down.   Column C would be your index, and column D your data.  

It might be me but I can't get this formula to show what I need.

Cornucopia

david1946 said:

Cornucopia said:

How are you going to display the results?   

You could just number the rows starting with 0 at row 40, and then use something like this

=if(mod(C40,48)=0,sum(D40,D50),"")

And then copy that all the way down.   Column C would be your index, and column D your data.  

It might be me but I can't get this formula to show what I need.

It's only a suggestion.   If you post some sample data, someone might be able to do it for you.

There's was an almost identical thread, but I can't find it.

[Deleted User]

david1946 said:

hi tallmansix I've managed to make pivot tables and can get totals for days but how do I make a pivot table that will show me the totals of a partial day like between 19:00 and 23:30.

the reason for this is because when I go out in  the evening I turn the heating off so I want to compare the usage as against the evenings when I don't turn the heating off.

Step 12 in the comment I linked to showed you how to break in down by hour, just filter using the drop down at the top of the column for the hours you want.

Also I'd put months in the columns on step 12 so just change that to days instead.

MikeTK

Thank You for the answers.