MINS, Match, Index - ignore "0"'s or "-"'s

[albert_de]

I am trying to create a spreadsheet that summarises our Weather Station’s raw data.

But I am struggling with data from the Weather Station that returns a “0” value or “-“ when no data was recorded by the weather station.

The attached Image helps to explain what I am trying to achieve.

Thank you for any direction that Members maybe able to offer me. I really am stuck!

 

[etaf]

As you have 2021 version, you may be able to use FILTER()
=MIN(FILTER(B1:B20,(B1:B20<>"")*(B1:B20<>0)))

I suspect a much better way to do this
=INDEX(A1:A20,MATCH(MIN(FILTER(B1:B20,(B1:B20<>"")*(B1:B20<>0))),B1:B20,0))

ALSO if 2 results , index/match will only bring back the 1st result and not both or more

what happens if more than 1 result with the same temp value in column B ?

Book8
	A	B	C	D	E
1
2	DATE-1	-			DATE-8
3	DATE-2	1
4	DATE-3	2
5	DATE-4	0
6	DATE-5	4
7	DATE-6	0.1
8	DATE-7	-
9	DATE-8	0.0001
10	DATE-9	10
11	DATE-10	20
12	DATE-11	0.001
Sheet1

Cell Formulas
Range	Formula
E2	E2	=INDEX(A1:A12,MATCH(MIN(FILTER(B1:B12,(B1:B12<>"")*(B1:B12<>0))),B1:B12,0))

BUT
Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[etaf]

you should also have LET()
so this should work and bring mutiple values of the MIN , also can be used for a single value
=LET(m,MIN(FILTER(B1:B12,(B1:B12<>"")*(B1:B12<>0))),FILTER(A1:A12,B1:B12=m))

OR in full
=FILTER(A1:A12,B1:B12=MIN(FILTER(B1:B12,(B1:B12<>"")*(B1:B12<>0))))

Book8
	A	B	C	D	E	F	G	H	I
1				MIN	Single Value		LET - Multiple values		Without LET
2	DATE-1	-		0.0001	DATE-2		DATE-2		DATE-2
3	DATE-2	0.0001					DATE-8		DATE-8
4	DATE-3	2
5	DATE-4	0
6	DATE-5	4
7	DATE-6	0.1
8	DATE-7	-
9	DATE-8	0.0001
10	DATE-9	10
11	DATE-10	20
12	DATE-11	0.001
Sheet1

Cell Formulas
Range	Formula
D2	D2	=MIN(FILTER(B1:B12,(B1:B12<>"")*(B1:B12<>0)))
E2	E2	=INDEX(A1:A12,MATCH(MIN(FILTER(B1:B12,(B1:B12<>"")*(B1:B12<>0))),B1:B12,0))
G2:G3	G2	=LET(m,MIN(FILTER(B1:B12,(B1:B12<>"")*(B1:B12<>0))),FILTER(A1:A12,B1:B12=m))
I2:I3	I2	=FILTER(A1:A12,B1:B12=MIN(FILTER(B1:B12,(B1:B12<>"")*(B1:B12<>0))))
Dynamic array formulas.

 

[albert_de]

Thank you for your quick reply.

Here is a Dropbox Link to the Spreadsheet that I am creating. It Contains the Raw Data in the Raw Data tab. With query results being posted in the Summary and Records Tab.

Dropbox

www.dropbox.com

You will see the "0" results on the Summary Tab in the Maximum Low results (e.g. E238 & E249.
The Records Tab is where I am trying to Index queries from the Raw Data by Year, whilst also ignoring "0" or "-" values

Regarding if there are 2 Temp's the same - that's OK. So long as one of those results is returned that would work.

Now I need to sit down and study what you have told me in your posts!

Thank you very much for your guidance

 

[etaf]

Thanks, OK thats a lot more complicated then the image example - as you have merged cells and a mixture of various weather results
the rawdata sheet - the dates are TEXT , so that another complication - so a DATE>text to columns needed on that column to change to REAL dates / times

Raw data , does not have any - in the results , 0 can exist in that file

you could use column B of rawdata - to find the lowest value and the date/time ??? and also filter that by YEAR - is that an option?

having a bit of trouble working out whats going here , as quite a complicated spreadsheet


I also have a weather station - Watson - and uses ecowitt.net to produce the same sort of results

 

[albert_de]

Column B is an Average of Column C & D. So its not really a reflection Low data.

I will have a go with your formula's and see if I can move forward

 

[etaf]

ok , i have been playing with your spreadsheet and not really found a solution

Not an average of C and D
see column E
so you want to use the MIN temp column C ??

HP2551 Weather Data v2.1.xlsx
	B	C	D	E
1069	9.8	8.3	13.4	10.9
1070	12.3	11.0	13.7	12.4
1071	13.9	11.0	17.6	14.3
1072	18.3	17.3	21.3	19.3
1073	17.1	15.0	18.7	16.9
1074	14.4	13.0	15.3	14.2
1075	13.7	12.8	14.5	13.7
1076	12.9	12.5	13.3	12.9
1077	14.3	12.4	19.9	16.2
1078	18.2	16.5	19.9	18.2
1079	17.1	15.3	19.5	17.4
1080	14.6	14.0	15.3	14.7
1081	13.5	13.1	14.3	13.7
1082	13.3	13.0	13.6	13.3
1083	15.3	13.2	18.9	16.1
1084	19.7	18.9	20.5	19.7
1085	18.7	16.1	21.1	18.6
1086	15.9	15.7	16.2	16.0
Raw Data

Cell Formulas
Range	Formula
E1069:E1086	E1069	=AVERAGE(C1069:D1069)

 

[albert_de]

Yes, I'm wanting to use Column C for Low Temp.
I have spent a while trying to figure how to exclude 0's. Maybe it can't be done?!

 

[albert_de]

Fti, the Raw Data is from ecowitt.net

 

[FormR]

I have spent a while trying to figure how to exclude 0's.

Hi, just add another criteria to your MINIFS() function - i.e. in E238 :

=IF(E$237=TRUE,MINIFS('Raw Data'!$C$3:$C$30000,'Raw Data'!$C$3:$C$30000,"<>0",'Raw Data'!$A$3:$A$30000,"2022-08*"),"")