Sum and Average - Only if Dates Match

[reactant]

Hi all,

Basically, I've got hourly temperature data (one column the time/date stamp, the adjacent column the temperature), and I want to average it to average daily temperature (One column with the date, and the adjacent column . I could manually select the data to average, but it's around three years worth of data, so that's sort of out of the question.

What I'm hoping to do is have Excel look at the date in the average temperature spread, then average all of the hourly temperatures that have that date associated with it.

Let me know if I need to provide any more information/an example spreadsheet.

I've tried something like =AVERAGEIF(G9:G33464,"=H10",E9:E33464) (with H10 being the date for the average temperature spread), but I get a divide by zero error.

Any help would be greatly appreciated.

Thanks.
-Reactant

[FDibbins]

without seeing the layout of you're data, i would suggest you try using a =averageifS() function. start the formula, then click the fx and let that help you through what it needs

if you still have a problem, upload a sample worksheet and i will walk you through it

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[TheExcelphile]

You are probably getting the error because in your original data, you have a date and time stamp, and you are trying to compare that with a date only. What you need to do is in a separate column, extract the date portion of your date and time stamp. If your original date and time is in E9, use the formula =INT(E9) in the new column and autofill. Then use your Averageif formula using the new column in the 'range' part of the formula. It will work.

[reactant]

FDibbins,

I didn't even know about that little fx button trick - that should save me a lot of headache in the future!

Thanks - I think I've got it solved now.
-Reactant

[FDibbins]

happy to help. please consider adding the reputation to those that helped (see point 3 below)