Complex excel formula needed

W

weatherguru

New Member
Joined
Jun 21, 2008
Messages
14
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:confused:
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
weatherguru said:
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:confused:
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
weatherguru said:
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
Click to expand...

Hi WeatherGuru:

How about ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABC
1Date1Date2MaxTemp
201 Jan 09:0002 Jan 09:0064.5
3***
Summary


</body></html>
 
Upvote 0
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
 
Upvote 0
weatherguru said:
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
Click to expand...

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) ...
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Macro needed in place of array formula
Replies
1
Views
269
Wguru
W
S
  • Question
VBA Help - Need listbox to scroll to top when opened
Replies
1
Views
329
rlv01
R
W
  • Solved
MAX Value in a Complex Environment
Replies
7
Views
446
AhoyNC
A
P
  • Question
Excel Line graph - Y axis issues - seems to be summing the data
Replies
2
Views
160
panesai1
P
LRATOZ
  • Question
In Excel 2021 what is the formula to return a date when the max value happened (Compare the same month over many years)
Replies
2
Views
747
LRATOZ
LRATOZ

Forum statistics

Threads
1,214,883
Messages
6,122,077
Members
449,064
Latest member
MattDRT

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top