Calculate Max / Min Help Please!

[nebrahimjee]

Hi. I have a temperature mapping system at work and every day i get exported to excel a spreadsheet that logs temperatures twice a minute.
By the end of a week i have several thousand rows that look like this:

08/01/2014 03:38	15.6
08/01/2014 03:38	15.5
08/01/2014 03:39	15.6
08/01/2014 03:39	15.5
08/01/2014 03:40	15.6

<colgroup><col><col></colgroup><tbody>
</tbody>

And so on.... In column A is that Date/Time, and Column B has the temperatures. Now i need to work out the daily maximum and minimums.
At the moment I copy all one days logs into another spreadsheet then just sort by column B, and write down the top and bottom temperatures.
This sucks, and takes too long. I have 6 spreadsheets of data (different thermometers) and i have about 3months of data on each spreadsheet that now needs to be processed!

Any help would be greatly appreciated. I do not understand much about Visual Basic code, so hopefully i can get a real easy simple solution!
Thanks in advance!

 

[oldbrewer]

=max(B1:B10000)

and of course min

 

[oldbrewer]

use sumproduct to filter by date / time in conjunction with above

 

[rilzniak]

I think this might work better:

Create a table to track all daily min and max values together and then add the formulas as shown below (this way all your data is together if you need to refer back).

	D	E	F
1	Date	Min	Max
2	08/01/14	=min(if($A$2:$A$1000=D2,$B$2:$B$1000))	=max(if($A$2:$A$1000=D2,$B$2:$B$1000))

<tbody>
</tbody>

You will need to hit Ctrl+Shift+Enter when entering the formulas as it is an array formula.

I'm assuming that $A$2:$A$1000 are where all of the dates are located and $B$2:$B$1000 are where the temperatures are located (you can of course change the row references and it will still work). As you enter the data you can copy down using Ctrl+D in E3 and F3, and forever after. Hope that helps!

 

[markmzz]

Maybe this:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=MAX(IF($D2=INT($A$2:$A$44540),$B$2:$B$44540))

=MIN(IF($D2=INT($A$2:$A$44540),$B$2:$B$44540))

Markmzz

 

[rilzniak]

Maybe this:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=MAX(IF($D2=INT($A$2:$A$44540),$B$2:$B$44540))

=MIN(IF($D2=INT($A$2:$A$44540),$B$2:$B$44540))

Markmzz

Won't this will round the value down and create less accurate results?

 

[markmzz]

Now i need to work out the daily maximum and minimums.

Won't this will round the value down and create less accurate results?

We need to calc the daily maximum and minimums (look at the quote above). So we need integers numbers for the date (column A). Look at this:

Date/Time	Temp		Date	Max	Min
08/01/2014 03:38	68,1		08/01/2014	88,00	13,90
08/01/2014 03:38	34,5		09/01/2014	89,80	11,20
08/01/2014 03:39	62,9		10/01/2014	88,50	11,10
08/01/2014 03:39	59,6		11/01/2014	88,50	10,40

<tbody>
</tbody>

Markmzz

 

[oldbrewer]

									02/03/2014 00:00
	01/03/2014 01:34	3							03/03/2014 00:00
	01/03/2014 03:58	4
	01/03/2014 06:22	9
	01/03/2014 08:46	6
	01/03/2014 11:10	7						###############
	01/03/2014 13:34	8			this		11	02/03/2014 01:34	13
	01/03/2014 15:58	9			approach		12	02/03/2014 03:58	14
	01/03/2014 18:22	10			pulls		13	02/03/2014 06:22	15
	01/03/2014 20:46	11			out		14	02/03/2014 08:46	9
	01/03/2014 23:10	9			all		15	02/03/2014 11:10	17
	02/03/2014 01:34	13			date_times	16	02/03/2014 13:34	18
	02/03/2014 03:58	14			between		17	02/03/2014 15:58	19
	02/03/2014 06:22	15			the		18	02/03/2014 18:22	20
	02/03/2014 08:46	9			date_times	19	02/03/2014 20:46	21
	02/03/2014 11:10	17			in		20	02/03/2014 23:10	12
	02/03/2014 13:34	18			j2 and j3		21
	02/03/2014 15:58	19					22
	02/03/2014 18:22	20					23
	02/03/2014 20:46	21					24
	02/03/2014 23:10	12					25
	03/03/2014 01:34	23					26
	03/03/2014 03:58	24					27
	03/03/2014 06:22	25					28
	03/03/2014 08:46	26					29
	03/03/2014 11:10	27					30
	03/03/2014 13:34	28					31
	03/03/2014 15:58	29					32
	03/03/2014 18:22	30					33
	03/03/2014 20:46	31					34
	03/03/2014 23:10	32
	04/03/2014 01:34	33
	04/03/2014 03:58	34
	04/03/2014 06:22	35
	04/03/2014 08:46	36
	04/03/2014 11:10	37
	04/03/2014 13:34	38						MIN	9
	04/03/2014 15:58	39						MAX	21
	04/03/2014 18:22	40
	04/03/2014 20:46	41
	04/03/2014 23:10	42
	05/03/2014 01:34	43					formula in cell marked #########
	05/03/2014 03:58	44					=IF(OFFSET($B$2,H8,0)>$J$2,IF(OFFSET($B$2,H8,0)<$J$3,OFFSET($B$2,H8,0),""),"")
	05/03/2014 06:22	45
	05/03/2014 08:46	46

<colgroup><col><col><col span="5"><col><col><col><col span="4"></colgroup><tbody>
</tbody>