# Complex excel formula needed

#### weatherguru

##### New Member
I need to write a formula that will do the following for me:
I have two separate worksheets, one containing raw weather data i.e date/time, temp etc in separate columns. The other worksheet is a summary table with date, max temp, time etc in separate columns. I wish to write a formula of sorts to find the max temp using the date as a reference. I will try to explain this further.
Worksheet 1 has date/time in one column, temp in another. The data relates to each minute of every hour of every day. Worksheet 2 just has date (mm//dd/yy) max temp, time etc. I wish to use the date in worksheet 2 to find max temp in worksheet 1. Can anyone help with this please, providing I have explained myself clearly?

Thanks
Guru

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Yogi Anand

##### MrExcel MVP
I need to write a formula that will do the following for me:
I have two separate worksheets, one containing raw weather data i.e date/time, temp etc in separate columns. The other worksheet is a summary table with date, max temp, time etc in separate columns. I wish to write a formula of sorts to find the max temp using the date as a reference. I will try to explain this further.
Worksheet 1 has date/time in one column, temp in another. The data relates to each minute of every hour of every day. Worksheet 2 just has date (mm//dd/yy) max temp, time etc. I wish to use the date in worksheet 2 to find max temp in worksheet 1. Can anyone help with this please, providing I have explained myself clearly?

Thanks
Guru

Hi WeatherGuru:

Welcome to MrExcel Board!

one way ...

use the following array formula in cell B2 of sheet Summary ...

=MAX(IF(INT(RawWeatherData!\$A\$2:\$A\$15)=Summary!A2,RawWeatherData!\$B\$2:\$B\$15))

this formula is then copied down.

I hope this helps.

#### weatherguru

##### New Member
Thank-you. This works quite well. Further to this, in the raw data date/time column, the format of each cell looks like this e.g.01 Jan 9:00. How can I find the maximum for each day say between 9am 01 Jan and 9am on the 02 Jan? Any help would be appreciated.

Thanks
Guru

#### Yogi Anand

##### MrExcel MVP
Thank-you. This works quite well. Further to this, in the raw data date/time column, the format of each cell looks like this e.g.01 Jan 9:00. How can I find the maximum for each day say between 9am 01 Jan and 9am on the 02 Jan? Any help would be appreciated.

Thanks
Guru

Hi WeatherGuru:

Excel Workbook
ABC
1Date1Date2MaxTemp
201 Jan 09:0002 Jan 09:0064.5
3***
Summary

</body></html>

#### weatherguru

##### New Member
Hi there,

I have come across a problem with the original array formula that was posted. It seems that there is a limit on the row number that can be used. e.g. I am referencing a range from b2:b517606 but it will only give a value up to b2:b445000 before I get #REF! as the cell value. I have checked the cell reference, format etc but cannot seem to get around this. Any ideas?
Another question: Now that I have a formula to get the maximum values, is there one i can use to get the times that the maximum occurred without having to reference each individual day. i.e. can I reference the whole range like when getting the maximum temp? Any advice would be most appreciated and welcome.

Thanks
Guru

#### Yogi Anand

##### MrExcel MVP
Hi there,

I have come across a problem with the original array formula that was posted. It seems that there is a limit on the row number that can be used. e.g. I am referencing a range from b2:b517606 but it will only give a value up to b2:b445000 before I get #REF! as the cell value. I have checked the cell reference, format etc but cannot seem to get around this. Any ideas?
Another question: Now that I have a formula to get the maximum values, is there one i can use to get the times that the maximum occurred without having to reference each individual day. i.e. can I reference the whole range like when getting the maximum temp? Any advice would be most appreciated and welcome.

Thanks
Guru

Hi WeatherGuru:

Couple of things ...

1) If your rows go to 517606, you must be using EXCEL 2007

2) in regard to using whole columns, using the

=MAX(IF ... type formulations

I don't know about EXCEL 2007, but earlier versions of EXCEL don't permit use of whole column ranges

3) You are dealing with a very large data set, if EXCEL would work for you, then for using the whole columns as ranges, you may want to consider using the D-functions, such as DMAX and DGET

4) In case there are multiple MAXIMUMs, you may also want to consider utilizing FILTERing to extract the relevant values

5) ...

Replies
1
Views
129
Replies
6
Views
528
Replies
6
Views
361
Replies
1
Views
312
Replies
4
Views
626

1,191,204
Messages
5,985,275
Members
439,953
Latest member
suchitha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back