Excel 2010 Only negative values are displayed [SOLVED]

[Harry Covair]

Hi the forum,

I want to retrieve the absolute min temperature per day from 1833 to 2012.

The date and the min are in B2 to C65384

The day numbers are in F2-F32 and the month numbers are in G1-R1

I developed a formula to analyze the data:

{=SMALL(IF((LEFT(TEXT($B$2:$B$65384;"dd/mm/yyyy");2) =TEXT($F2;"dd"))*(MID(TEXT($B$2:$B$65384;"dd/mm/yyyy");4;2)=TEXT(G$1;"dd"));$C$2:$C$65384;0);1)}.

The Text function is used to avoid the problem with the dates before and after 1/1/1900.

The results are correct as long the temperatures are negative. In case of positive temperatures, the formula returns 0. I tried the SMALL and MIN function; the results are the same.

Could some people explain me what is going wrong in the formula?

Thanks in advance


Harry

 

[Luke M]

Are there any blank cells in the B2:B65384 range that would be causing a 0 value to appear?

 

[Harry Covair]

Hi Luke M


Thanks or your answer.


All the cells in the range contain date.

I uploaded the file with DropBox. It can help to understand the problem


https://www.dropbox.com/s/m1nwzh7zv72xga7/min%20per%20day.xlsx


Regards


Harry

 

[SirJB7]

@Luke M

Hi!

Your EPASS once again take care of your CASSFML illness and... here you have a few lines from the uploaded file.

-----

[pre]

UCCLE	date		temp min	temp Max	Empty	NoTitle	1	2	3	… col J:Q	12
1833	01/01/1833	0		0			1	-14,1	-16	-10,9	-2,9		-8
02/01/1833	-4,8		-1,4			2	-14,1	-17,4	-9,5	-3,7		-11,3
03/01/1833	-6,8		-3,1			3	-13,2	-16,3	-11,1	-3		-13,3
04/01/1833	-6,8		-3,9			4	-13,1	-15,8	-10,9	-3		-10,4
05/01/1833	-9		-4,4			5	-14	-13,3	-9,5	-3,8		-12,4
06/01/1833	-8,5		1,4			6	-13,1	-16,6	-11,3	-4,8		-12
07/01/1833	-5		1,7			7	-12,8	-18,3	-10,4	-4,5		-15,4
10/01/1833	-10		-2,4			8	-17,7	-15,7	-8,3	-2,7		-17,3
11/01/1833	-9,2		2,8			9	-16,8	-13,7	-7,9	-5		-17,7
12/01/1833	-4,6		4,8			10	-16,2	-13,4	-9,4	-3,8		-12,2
13/01/1833	-0,2		4,8			11	-13,7	-13,5	-11,2	-3,2		-11,1
16/01/1833	-4,8		3,2			12	-13,5	-16,1	-8,9	-4,2		-13,3
17/01/1833	-4,3		3,1			13	-15	-16,8	-11,9	-2,4		-13,7
18/01/1833	-1,4		1,4			14	-18,1	-17,7	-13,6	-2,5		-15,7
19/01/1833	-5,6		-2,4			15	-17,4	-17	-10,9	-2,8		-14,3
20/01/1833	-5,6		-1,4			16	-19,6	-13	-7	-3,1		-12,8
21/01/1833	-8,4		-0,9			17	-16,7	-16,4	-6,3	-3,3		-15,2
22/01/1833	-7,8		0,4			18	-17,7	-15,5	-7,1	-3,8		-14,7
23/01/1833	-8,3		0,6			19	-19,5	-15,9	-9,5	-3,5		-13,6
24/01/1833	-10,3		-1,6			20	-18,2	-15,8	-10,1	-2,7		-15,8
25/01/1833	-9,8		-0,2			21	-17,1	-14,5	-8,6	-2,5		-14,9
26/01/1833	-7,8		2,5			22	-17,9	-12,1	-7,1	-2		-13,8
27/01/1833	-1,8		4,6			23	-18,7	-16,7	-7,4	-2,2		-11,3
28/01/1833	1,5		5,4			24	-11,5	-16,2	-7,7	-2,3		-12
29/01/1833	0,7		5,8			25	-21,1	-14,1	-4,7	-2,2		-12,1
30/01/1833	-0,2		4,4			26	-14,7	-13,2	-6,3	-2		-16,7
31/01/1833	-0,7		3,6			27	-17,6	-11,2	-5,4	-1,1		-12,6
01/02/1833	-0,9		2,1			28	-14,6	-11,8	-4,9	-0,8		-11,6
02/02/1833	-3,9		8,6			29	-14,5	-10,1	-6,1	-3		-15
03/02/1833	0,3		8,3			30	-14,9	0	-5	-1,6		-16,7
04/02/1833	1,4		10,4			31	-13	0	-3,8	0		-14,1
05/02/1833	4,8		10,8
06/02/1833	6,1		10,6
07/02/1833	4,5		9,8			1	13,1	14,6	18,4	21,2		14,4

[/pre]
-----


BTW, it took half an year to calculate.


Regards!


@Harry Covair

Hi!

Just for the records:

EPASS: executive personal assistant secretary & slave

CASSFML: can't access shared files from my location



Regards!

 

[Harry Covair]

Hi the forum

Hi SirJb7

I strongly agree with your comment on the elapsed time.

In fact, I wrote a VBA routine to find the extreme min or max temperature by day. But as it is not easy to check the relevance of the results with a large amount of data, I imagined an Excel approach that should give me the same values.

Due to the use of three loops that read the entire data, the VBA version has also a long elapsed time (on my PC, about 40 seconds). Use the Run and Clear buttons to test.


But for the moment, I try to solve the zeroes value in the spreadsheet.

I posted the full version on DropBox

https://www.dropbox.com/s/udpindcgfkbud6c/min%20per%20day.xlsm


Hoping someone can help me


Kind regards


Harry

 

[SirJB7]

Hi, Harry Covair!

Looking a little more carefully to your workbook and regarding the not so huge amount of data but yes the huge number of array formulas needed, I recommend you to forget about a formula solution and keep your VBA procedure.

Regards!

 

[Harry Covair]

Hi SirJB7,

On the bottom, you are absolutely right, the VBA solution should be preferred. My call to the forum had as sole purpose to understand why the SMALL function displays zero in case of positive values.


Regards


Harry

 

[Colin Legg]

Hi, the reason

SMALL() displays 0 in case of positive values is because your [code]IF() statement returns 0s if the condition is not met.


Here's your formula again:

[pre][code]{=SMALL(IF((LEFT(TEXT($B$2:$B$65384;"dd/mm/yyyy");2) =TEXT($F2;"dd"))*(MID(TEXT($B$2:$B$65384;"dd/mm/yyyy");4;2)=TEXT(G$1;"dd"));$C$2:$C$65384;0);1)}.[/pre]
Just to clear out the clutter, this rewrite makes it more obvious:


[code]{=SMALL(IF( Conditions ; C2:C65384 ; 0 ) ; 1)}


So if there are only negative numbers when 'Conditions' is [code]TRUE, then you would get an array like this:

[code]{-3;0;0;0;0;-4;0;0;0;0;-10 ...... }

and the [code]SMALL() function would correctly pick out the smallest number which is -10.


But, if there are only positive numbers when 'Conditions' is [code]TRUE, then you would get an array like this:

{3;0;0;0;0;4;0;0;0;0;10 ....... }

so then the SMALL()[/code] function would pick 0 which is the smallest number in the array.


If you were to rewrite the formula so the IF()[/code] returned FALSE[/code] when the condition is not met then the formula would return a positive number if that number is the smallest.


{=SMALL(IF( Conditions ; C2:C65384 ) ; 1)}[/code]


The array would look like this:

{3;FALSE;FALSE;FALSE;FALSE;4;FALSE;FALSE;FALSE;FALSE;10 ....... }[/code]


SMALL()[/code] will ignore the FALSE[/code]'s, so it will return 3.

 

[Colin Legg]

I also made a few quick changes to get rid of those very slow array formulas. Using a pivot table speeds things up a lot.


I deleted all of the array formulas.


In E1 I put the word Day and then in E2 I put the formula

=LEFT(TEXT(B2,"dd"),2)+0

In F1 I put the word Month and then in F2 I put the formula =MID(TEXT(B2,"dd/mm"),4,2)+0

I then filled these formulas down E and F and let them calculate, which was pretty fast on my computer (well under a second).


Then I created a pivot table referencing C1:F65384, added Month to the columns, Day to the Rows and Temp Min to the Values. I then changed the Temp Min Values field to aggregate as MIN rather than COUNT.


https://www.dropbox.com/s/2y4ymcx14jko5ct/min%20per%20day.xlsx

 

[Harry Covair]

Hi Colin Legg,

Nothing like a little slice of Excel for breakfast.

My thanks are especially warm that your explanation is simple and understandable.

I appreciate your suggestion to solve the problem with a pivot table.

But this Excel approach is not operational; There sole purpose was to verify the accuracy of a VBA routine that does the same but much faster.

I reiterate my thanks for the didactic approach of your explanation

Harry