Extracting Data From A Chart

[Benby]

I use a whirling hydrogometer for my work in QA/QC, once I have taking the readings, I then have to look up on chart to get Relative Humidity & Dew Point readings based on the Dry & Wet Bulb readings...

If my readings from my WH are as follows, Dry B - 15°c, Wet B 13°c, that gives me a difference of 2°c (Depression) I then look at left hand col for 15°c and top col 2°c, this points me to the intersect of 79% Relative Humidity & 9°c Dew Point from the chart...

What formula do I use for different DB & WB readings into cells that would automatically give me the RH & DP output readings in cells based on the chart...

Hope you understand what I am after, below is a cut down version of my chart...

Excel Workbook
	A	B	C	D	E	F	G
1		WBD
2	DB	1.00	1.00	1.50	1.50	2.00	2.00
3		RH%	DPc	RH%	DPc	RH%	DPc
4	15	90	13	85	12	80	12
5	14.5	90	13	85	12	80	11
6	14	90	12	84	11	79	10
7	13.5	89	12	84	11	79	10
8	13	89	11	84	10	79	9
9
10	Dry	Wet	Diff	RH	DP
11	15c	13c	2c	??	??
Sheet3

Excel 2007

 

[Showard27]

Hi

You could do this with spearate vlookup and hlookup routines, but this will need the building of separate tables each time

A quicker way would be through code, the following works, and you could either attach it to a recalculation button, or to a custom function.

Sub RHFind()
Db = ActiveSheet.Range("a11").Value
diff = ActiveSheet.Range("C11").Value

For i = 4 To 8
If ActiveSheet.Range("A" & i).Value = Db Then
rc = i
Else: End If
Next i

For j = 2 To 6 Step 2
If ActiveSheet.Range("a1").Offset(1, j).Value = diff Then
rh = j
Else: End If
Next j

For k = 3 To 7 Step 2
If ActiveSheet.Range("a1").Offset(1, k).Value = diff Then
dp = k
Else: End If
Next k

ActiveSheet.Range("E11").Value = ActiveSheet.Range("a1").Offset(rc, rh).Value
ActiveSheet.Range("D11").Value = ActiveSheet.Range("a1").Offset(rc, dp).Value

End Sub

 

[Benby]

Hi

You could do this with spearate vlookup and hlookup routines, but this will need the building of separate tables each time

A quicker way would be through code, the following works, and you could either attach it to a recalculation button, or to a custom function.

Sub RHFind()
Db = ActiveSheet.Range("a11").Value
diff = ActiveSheet.Range("C11").Value

For i = 4 To 8
If ActiveSheet.Range("A" & i).Value = Db Then
rc = i
Else: End If
Next i

For j = 2 To 6 Step 2
If ActiveSheet.Range("a1").Offset(1, j).Value = diff Then
rh = j
Else: End If
Next j

For k = 3 To 7 Step 2
If ActiveSheet.Range("a1").Offset(1, k).Value = diff Then
dp = k
Else: End If
Next k

ActiveSheet.Range("E11").Value = ActiveSheet.Range("a1").Offset(rc, rh).Value
ActiveSheet.Range("D11").Value = ActiveSheet.Range("a1").Offset(rc, dp).Value

End Sub

Thanks for the reply but how do I go about setting up what you say, I'm a excel novice

 

[Showard27]

OK apologies

You need VBA open in excel, insert a new module, then copy the routine into the module.

Next create a new command button on the spread sheet, and assign the macro RHFind

If you are doing this then remember that I have hard coded your ranges and therefore if the chart you are looking at is larger than the sample, then these would need to change.

Also as a novice, which I am only 1 stage up on that myself, then a bit of an understanding of VBA will help you understand what is going on here with this code

The alternative is to go with my original suggestion and use VLookup and Hlookup.

First use Vlookup for the value in c11, in every cell for 1 row below the table (use the formula builder to help you), then use HLookup for the value in C13, along row 2, and pickfrom your newly created row.

Rember also that in each of these cases code or formula, you need to make sure the "search" value is formatted the same as the tables (ie no "°C")

 

[Benby]

Cheers, will have a go with the VBA, other solution looks a bit complicated for me ...

Thanks for your time...