How to sum one number each 24 lines

**RaquelF** · 03-13-2015, 07:33 AM

Hi!
I have hourly data through an entire year in columns, so in every line i have a different hour of a day. but i want to make another column to introduce the number of the day of the year that where we stand.
It is a bit difficult to explain, so i'll try to give an example-> For 4th March, i have:
Hour Data Number of the day
1 67 63
2 500 63
3 444 63
4 567 63
... ... ...
(the last column is what i want - 4th March = 31+28+4=63th day of the year)
So, what i want is some form of summing one day each 24 lines, so that in the Hour '1' on the 5th of March i would have 64 in the last column.

Thank you!

**AranDG** · 03-13-2015, 07:43 AM

If you have the numbers 1 to 8760 (number of hours in a normal year) in column A then you could use the following but your data would have to start in row 2 to allow the lookup of the number in the previous row. This will just increment the number by 1 every time it reaches 24 hours.

=MAX(IF(A2/24=INT(A2/24),D1+1,D1),1)

If you do not have the numbers in the cells then I would use the following which will use the row number as the 1 to 8760. This will just increment the number by 1 every time it reaches 24 hours.

MAX(IF(row(a2)/24=INT(row(a2)/24),D1+1,D1),1)

If you have the date or time in the sheet it would be an easier formula. Let me know either way

**RaquelF** · 03-13-2015, 07:45 AM

Thank you!!

How to sum one number each 24 lines

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to sum one number each 24 lines

Re: How to sum one number each 24 lines

Re: How to sum one number each 24 lines

Thread Information

Users Browsing this Thread

Similar Threads

Number lines disregarding blank lines

[SOLVED] Insert blank lines to make the total number of lines 67

creATING NUMBER OF LINES FROM ONE NUMBER

number VBA lines

[SOLVED] How do I set up a way to add up the number of lines used when cal.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions