3 columns; date and two data, problem is sorting and calculating with respect to date

Hello Guys,

I have three column data. First column is date, second and third are numerical data. I have 24 data entries per day on the first column (for every hour). I am trying to multiply second column with the third for each hour and get the total of these multiplications for the day and divide it by the total of second column for the same day.

I am very confused. Can someone give me a hand?

Thanks,

Johnny

Originally Posted by NBVC

see attached for what I meant....

I entered each individual date in E1:E4 and then applied this formula to F1 and copied down:

Please Login or Register  to view this content.

That's awesome. I have a little question. When I ask excel to multiply each column and add them up and divide that with the classical sum, the result is 43.49795656 instead of 43.95625 (yours for Jan. 16th). Both cases excel does the calculations. The other dates are off a little too.

Thanks,

Originally Posted by NBVC

You're right...there's a problem with my formula....I didn't notice you had the times in column A also....

amend my formula in F1 to:

Please Login or Register  to view this content.

this takes the "day" part only to compare against E1.

It works great now. Thank you very much for your help.