Outputting Discrete data from continuous dataset

[ahume12]

Hi there

I am having some issues putting a formula together to extract data from a continuous data set into a discrete data set.

I have existing table data which shows temperature between two locations in miles. In this dataset, the temperature is recorded at a non uniform/continuous interval
So the data could show 23.7 degrees between 59.01 and 59.19 miles and 21.0 between 59.19 miles and 59.37 miles.

I would like to process this data into a standard dataset, which outputs the minimum temperature between two uniform mileages
So the output data would be 59.00 to 59.25 is 21 degrees, which is the minimum temperature of the above within the bound of 59.00 to 59.25.

I have tried excel "ifs" function as well as some "if" "and" combined functions, but cant get anything that approaches the correct solution.

Would appreciate any help

Thanks
A

 

[Peter_SSs]

Welcome to the MrExcel board!

Is there a relationship between what you described above and the two images you provided? I cannot see where 21 degrees comes from in the images.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ahume12]

Apologies - I have been thinking about this for quite some time and I didn't explain adequately.
My description above was just that - it was not relevant to the data I am working with. My images above were in an attempt to give some context as I couldn't get that AddIn installed.

Managed to get the AddIn installed. See below for a bit more context.

From Data -

Sample Data AH.xlsx
	A	B	C
1	From Miles Decimal	To Miles Decimal	Temp
2	59.57	60.41	25.2
3	60.41	60.66	17.3
4	60.66	60.91	21.4
5	60.91	61.16	24.6
6	61.16	61.41	16.9
Data From

To Data -

Sample Data AH.xlsx
	A	B	C
1	Miles From	Miles To	Min Temp
2	59.00	59.25	???
3	59.25	59.50	???
4	59.50	59.75	???
5	59.75	60.00	???
6	60.00	60.25	???
Data To

 

[Peter_SSs]

Looks like you are wanting to populate C2:C6 in 'Data To'?
If so, what are the expected results and how do you get them manually?

 

[Peter_SSs]

Regarding your 'Report': Just post again with the additional information.
(After you have made a few more posts you will then have a 10 minute window after posting to edit your post. )

 

[ahume12]

Apologies again - I posted the reply before I had finished.
The Data From is the existing data I have in the continuous format.

Sample Data AH.xlsx
	A	B	C
1	From Miles Decimal	To Miles Decimal	Temp
2	59.57	60.41	25.2
3	60.41	60.66	17.3
4	60.66	60.91	21.4
5	60.91	61.16	24.6
6	61.16	61.41	16.9
Data From

The Data To is the processed data - I would like the Data To table to read as follows.

Sample Data AH.xlsx
	A	B	C
1	Miles From	Miles To	Min Temp
2	59.00	59.25	N/A
3	59.25	59.50	N/A
4	59.50	59.75	25.2
5	59.75	60.00	25.2
6	60.00	60.25	25.2
7	60.25	60.50	17.3
8	60.50	60.75	17.3
9	60.75	61.00	21.4
10	61.00	61.25	16.9
Data To

If more than 1 temperature is found within the new range defined within the Data To table range, I would like excel to return the minimum of two values.

 

[Peter_SSs]

Apologies again - I posted the reply before I had finished.

No problem, easy enough to do.

See if this is it. Check/edit your table name from sheet 'Data From'

ahume12.xlsm
	A	B	C
1	Miles From	Miles To	Min Temp
2	59	59.25	#N/A
3	59.25	59.5	#N/A
4	59.5	59.75	25.2
5	59.75	60	25.2
6	60	60.25	25.2
7	60.25	60.5	17.3
8	60.5	60.75	17.3
9	60.75	61	21.4
10	61	61.25	16.9
Data To

Cell Formulas
Range	Formula
C2:C10	C2	=MIN(IFNA(VLOOKUP([@[Miles From]],Table1,3),999),VLOOKUP([@[Miles To]],Table1,3))

 

[ahume12]

No problem, easy enough to do.

See if this is it. Check/edit your table name from sheet 'Data From'

ahume12.xlsm
	A	B	C
1	Miles From	Miles To	Min Temp
2	59	59.25	#N/A
3	59.25	59.5	#N/A
4	59.5	59.75	25.2
5	59.75	60	25.2
6	60	60.25	25.2
7	60.25	60.5	17.3
8	60.5	60.75	17.3
9	60.75	61	21.4
10	61	61.25	16.9
Data To

Cell Formulas
Range	Formula
C2:C10	C2	=MIN(IFNA(VLOOKUP([@[Miles From]],Table1,3),999),VLOOKUP([@[Miles To]],Table1,3))

This has worked! Thank you.

From reading the formula you posted, it looks like the steps are as follows (so I can understand)
1 - You are looking to vlookup miles from temp from Column 3 in table 1. If Excel cant find this, then it returns 999, an artificially high value to enable the MIN function to work.
2 - You are looking to vlookup miles to from Column 3 in table 1.
3 - The MINfunction then compares the two and returns the minimum

Usually I wound expect to see a TRUE or FALSE here after the vlookups. Are you taking advantage of VLOOKUP using the approximate match to return the next highest value?

 

[Peter_SSs]

Glad it worked for you.

1- Yes & Yes
2- Yes
3- Yes

Are you taking advantage of VLOOKUP using the approximate match to return the next highest value?

No, I'm using the default final argument of True to find an approximate match but if there is no exact match it matches the next lowest value, not highest.

 

[ahume12]

Thank you very much for the quick response and the understanding in my replies.
Many thanks again