Problem converting hourly data into a daily sum.

Try the SUMIFS function.

Assuming that your date / time entries are in sheet 1 column B, rainfall mm in column B and the date to total in Sheet 2 A2

=SUMIFS(Sheet1!$B:$B, Sheet1!$A:$A,">="&Sheet2!$A2,Sheet1!$A:$A,"<"&Sheet2!$A2+1)

Pete, I've been trying your suggestion, but it doesn't seem to work correctly. I think I understand the principle and think that the reason for this is that I can't get Excel to do the sequential dates with the date format that my data is in ( 2006-01-01 00:00 ), it just comes up as #VALUE! for all the dates below. I have tried using a more simple 01/01/2006 and can achieve the sequential dates this way, but Excel doesn't then recognise that these are the same dates as in my other sheet. Is there a way of telling it that they are the same date, just in a different format?

I have used the formatting to try and tell Excel this by going to cell formatting > Custom > (and typing in) dd/mm/yyyy hh:mm. However this still doesn't seem to work.

Thanks again

Andy

Originally Posted by Andrew_H92

I have tried using a more simple 01/01/2006 and can achieve the sequential dates this way,...

That is what I expected you to do in Sheet2.

... but Excel doesn't then recognise that these are the same dates as in my other sheet.

It sounds as if your "dates" in Sheet1 are text values that just look like dates. An easy way to convert them is to put the number 1 into a empty cell somewhere on Sheet1. Then select that cell and click <copy>. Then move the cursor and select all the offending dates in column A of Sheet1, then right-click and choose Paste Special. Click against Values and against Multiply in the dialogue box, and then click OK and press the <Esc> key. If you see numbers that are about 39000 or 40000 then you will need to format the cells as dd/mm/yyyy hh:mm. Then you can delete the 1 from the original blank cell.

Your summary table on Sheet2 should now show the correct results.

Hope this helps.

Pete

I've tried these and neither seem to work.

Following what Pete suggested, I didn't get a numer similar to 39000, the dates just stayed the same. Using the custom formatting I have told it that its a date. Sheet 2 still comes up with #VALUE! with:

=SUMPRODUCT(--(INT(Sheet1!A$1:A$10048)=A2),Sheet1!B$1:B$10048)

in the formula box.

Is there anything else that could be wrong here?

Thanks again

Andy

Do you have any missing dates in that range in column A ?

You could always post the workbook here (click on Go Advanced while you are posting and use the paperclip icon, or use Manage Attachments) and we can take a direct look at it.

Pete

The original dates are not in a format that will work with Pete's suggestions.

Use a date with +1 in the results table as Pete suggested then use the formula I suggested in post #5 to get the results.

You might need to adjust the format in the text function, Pete, can you assist if needed please, I'm afk and the forum isn't phone browser friendly.

There are a couple of missing dates each year yes. Here is the file I'm working on.

Cheers again

Andy

Hi Andy,

I've converted your date/times in Sheet1 to "proper" date/times, and now the totalling formula on Sheet2 works correctly (I've adjusted the ranges to suit your data)

Hope this helps.

Pete

Brilliant, thanks very much. This is working just as I needed. So I can apply what you did to the following year, how did you convert the data/times to "proper" dates?

Cheers

Andy

Hi Andy,

multiplying by 1 didn't work for your data as there were spaces at the beginning of each entry, so I had to introduce a formula in a helper column to do the conversion. This was copied down and the values fixed, before copy/pasting the values over those in the original column and then the helper column could be deleted. In detail, the steps were as follows (assuming your new dates are in a separate file or sheet, beginning in A1):

Enter this formula into C1:

=DATEVALUE(A1) + VALUE(RIGHT(A1,5)&":00")

This may give you a number about 40-odd thousand, so you need to reformat it - I used the format that your original data was in, i.e. yyyy-mm-dd hh:mm, but you could use the more usual (for UK users) dd/mm/yyyy hh:mm. If you widen column C more than is strictly necessary, you will see this aligned to the right, whereas column A values are aligned to the left - a simple check for numbers and text values. To copy this formula down, select cell C1 and double-click the fill handle (the small black square in the bottom right corner of the cursor). Then, with those cells still highlighted, click <copy>, then right-click and choose Paste Special | Values | OK and then press the <Esc> key - this will have fixed the values. Again with the cells in column C still highlighted, click <copy>, move the cursor to A1 and press the <Enter> key - this will have over-written the original values in column A.

So, you can just delete column C and then your data is in the correct format to be copied into the main file/sheet. You will need to adjust the range references in the formula in B2 of Sheet2, and then copy that down again.

If you intend to do this a number of times, then it might be better to use Jason's first SUMIFS formula (which he has since changed) as this uses full-column references and so will not need to be adjusted if you add more data. The alternative formula is:

=SUMIFS(Sheet1!$B:$B, Sheet1!$A:$A,">="&$A2,Sheet1!$A:$A,"<"&$A2+1)

which you can put in B2 of Sheet2, and then copy down.

Hope this helps.

Pete

Cheers both Pete and Jason. Your help has been invaluable. What would have taken me a good few days to do manually has taken me an hour or so today.

Thanks again

Andy

Hi Andy,

glad to be of help - maybe you could acknowledge us in your dissertation !!

As this was your first thread, there are a couple of things you should know:

1. You should mark your thread as Solved if you consider it to be so - the FAQ describes how to do this.

2. You can pass on your thanks directly to any poster who you think has helped you (and add to their reputation score) by clicking on the "star" icon in the bottom left corner of any post that you think was helpful (actually, on other threads as well, not just your own).

Best wishes on your degree course.

Pete

Thanks for this information - and I will certainly achknowledge you both at the beggining of my dissertation.

I have one futher question. I'm doing a similar task today, except with pressures, and instead of a total, I need a mean.

I have set up the spreadsheet in the same was as yesterday, except changed:

=SUMPRODUCT(--(INT(Sheet1!A$1:A$10048)=A2),Sheet1!B$1:B$10048)

to...

=AVERAGE(--(INT(Sheet1!A$1:A$10048)=A2),Sheet1!B$1:B$10048)

However, every day seems to come out with the same average pressure, which is clearly wrong. I was wondering if there is something in this formula which is incorrect?

Thanks again

Andy

Cheers Jason. I have that working now - much appreciated

Andy

Sorry, I have a similar question. If instead of a mean or total I want a max value for the day, how would I change the formula? I tried:

=MAXIFS(Sheet1!$B:$B,Sheet1!$A:$A,">="&A2,Sheet1!$A:$A,"<"&A2+1)

but this just gives me : "#NAME?"

Thanks

Andy

Hi Andy,

That is because MAXIFS is not a function within Excel. You can do it using an array formula, like this:

Formula:

Please Login or Register  to view this content.

Note that I've used range references rather than full-column, as that would be very slow - you will need to change these to suit your data.

*Note also that an array formula must be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the normal <Enter>, but it can be copied down in the normal way(s).

Hope this helps.

Pete

Unfortunately there is no function for MAXIFS (or at least not yet), that needs to be done with the older methods.

This formula needs to be array confirmed or you will get wrong results, if any.

=MAX(IF(INT(Sheet1!$A$1:$A$9362)=A2,Sheet1!$B$1:$B$9362))

To array confirm the formula, copy it and paste into B2, but don't press enter, hold down Shift and Ctrl, then press Enter.

When you look at the formula bar, the formula should be enclosed in curly brackets, if it's not, click in the formula bar and try Shift Ctrl Enter again.

It's not a good idea to use this method with full columns unless you want to wait a few hours for the results to caclulate.

As a non array alternative that can be used with full columns, you could try this one.

=MAX(OFFSET(Sheet1!B:B,MATCH(A2+1,Sheet1!A:A)-1,,-COUNTIFS(Sheet1!A:A,">="&Sheet2!A2,Sheet1!A:A,"<"&Sheet2!A2+1)))

Usually OFFSET is best avoided when possible, but in this scenario, I don't think that the volatile recalculation will be any more or an issue than the recalculation of the array equivilants if changes are made.

Thanks for these responses, my problem is solved. You guys have been brilliant

Andy

Andy, something that might be worth considering.

Wow, I've had a play around with this and this makes what I'm trying to do even eeasier. Cheers again You honestly can't imagine how helpful you're been

Andy

It's not a method I normally use, just wish I'd given it some thought sooner.

Let me know if there is anything you're not sure about with setting it up. Once you've done it a couple of times, and changes you need should become reasonably self explanatory.

I'm now trying to work with wind directions, and I'm trying to find a mean of the wind direction for the entire day. As before, I have the same list of dates and times alongside a list of wind directions.

As wind directions are more complex than most variables to work with (350 and 10 should average out at 360, not 180, as excel would assume) I have found a formula online which seems to do when I need:


=MOD(DEGREES(ATAN2(AVERAGE(COS(RADIANS(A2:A3))),AVERAGE(SIN(RADIANS(A2:A3)))))+360,
360)

However, this is a slow process. Is there a way to integrate this formula with the simple ones from earlier in this thread (see below) to allow me to calculate the daily mean wind directions for every day of an entire year at once?

e.g. =AVERAGEIFS(Sheet1!$B:$B,Sheet1!$A:$A,">="&A2,Sheet1!$A:$A,"<"&A2+1)

Thanks in advance.

Andy

Hi Andy,

would averaging work if you were to subtract 180 from your degrees (in column C), so 350 and 10 would become 170 and -170, giving an average of 0 (same as 360) ?

Try it out with some of your values. If that does work for you then you could use the formula you quote, but change $B:$B to $C:$C.

Hope this helps.

Pete

I'll have a go in a bit, thanks again!

Andy

I've had a go with your method this morning and its working for me, so thanks again Pete!

You're welcome, Andy.

I think if you have any follow-up questions in the future you might be better to start a new thread (with a link back to this thread), as many contributors won't look at a thread with a number of replies and so you might be missing out on other possible solutions.

Pete

Hi Pete,

I have the same issues as Andy in terms of not getting the desired results when I use your suggested formulas. I'm working with some meteorological data which is going to contribute to my MSc dissertation and am having some trouble. The data I have is hourly rainfall and cloud water interception, but I need a daily total.

May you please help.

You are responding to a thread that is over two years old. Forum Rule 02 says that you should not attempt to hijack someone else's thread - instead, you should start your own, with a link back to the original if you think it is relevant. Once you have done that, come back to this thread and include a link to your new thread, so it will be easier to find it.

Hope this helps.

Pete

hi Pete

Here is the file I'm working on.
how can i convert hourly rainfall to daily rainfall in each station acc to "station_i" & "time" & "hourly rainfall" columns?


Thanks a lot for any assistance which you might be able to offer.

behnam

You should start your own (new) thread, rather than hijacking someone else's. See the Forum Rules at the top of the screen.

Pete