Hello,

I have a column of decimal numbers, and I need to compare my numbe
with the column to find out where my number fits in. Once I know wher
my number fits in, I can perform some calculations on my number. Her
is my data:

OD Titre
3.815 262144
2.912 131072
1.554 65536
0.426 32768
0.084 16384
0.043 8192

Assume my number is 3.5. Notice 3.5 is not in the list, so I want t
find out where it would go if it were in the list. I want to write
getPosition function to do this.

After I find out where my number would go, if it were in the list,
need to perform the following calculations:

A=(sample OD-low OD)/(high OD-low OD)

Titre sample=(A*(high titre-low titre)) + low titre

The "low OD", "high OD", "low titre", and "high titre" values are al
relative to where my number is in the list.

So for 3.5, low OD = 2.912, high OD = 3.815, low titre = 131072, hig
titre = 262144.

Could someone please help me with writing a VB function to do this, a
I do not know where to start.

Thanks

--
Message posted from http://www.ExcelForum.com

Hi Glook,

You dont need a UDF you can use the MATCH function.
see help on MTCH with descending sort


Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

"Glook " wrote in message
...
Hello,

I have a column of decimal numbers, and I need to compare my number
with the column to find out where my number fits in. Once I know where
my number fits in, I can perform some calculations on my number. Here
is my data:

OD Titre
3.815 262144
2.912 131072
1.554 65536
0.426 32768
0.084 16384
0.043 8192

Assume my number is 3.5. Notice 3.5 is not in the list, so I want to
find out where it would go if it were in the list. I want to write a
getPosition function to do this.

After I find out where my number would go, if it were in the list, I
need to perform the following calculations:

A=(sample OD-low OD)/(high OD-low OD)

Titre sample=(A*(high titre-low titre)) + low titre

The "low OD", "high OD", "low titre", and "high titre" values are all
relative to where my number is in the list.

So for 3.5, low OD = 2.912, high OD = 3.815, low titre = 131072, high
titre = 262144.

Could someone please help me with writing a VB function to do this, as
I do not know where to start.

Thanks.


---
Message posted from http://www.ExcelForum.com/

Glook

Not sure if this will work but I cobbled it together so it may need more
testing. OD column must be sorted descending as shown and it will return 0
if the number you are comparing is greater than the highest OD or lower than
the lowest OD. (The function would not have values higher and/or lower if
this was the case)

Function TitreSample(dCheckNumber As Double, rODRange As Range) As Double
Dim lPosition As Long
Dim dLowOD As Double, dHighOD As Double
Dim lLowTitre As Long, lHighTitre As Long
Dim dMaxNo As Double, dMinNo As Double
With Application
dMaxNo = .WorksheetFunction.Max(rODRange)
dMinNo = .WorksheetFunction.Min(rODRange)
End With
If dCheckNumber dMaxNo Or dCheckNumber < dMinNo Then Exit Function
With Application
.Volatile True
lPosition = .WorksheetFunction.Match(dCheckNumber, rODRange, -1)
End With
dHighOD = rODRange(lPosition).Value
dLowOD = rODRange(lPosition + 1).Value
lHighTitre = rODRange(lPosition).Offset(0, 1).Value
lLowTitre = rODRange(lPosition + 1).Offset(0, 1).Value
TitreSample = (((dCheckNumber - dLowOD) / (dHighOD - dLowOD)) *
(lHighTitre - lLowTitre)) + lLowTitre
End Function


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Glook " wrote in message
...
Hello,

I have a column of decimal numbers, and I need to compare my number
with the column to find out where my number fits in. Once I know where
my number fits in, I can perform some calculations on my number. Here
is my data:

OD Titre
3.815 262144
2.912 131072
1.554 65536
0.426 32768
0.084 16384
0.043 8192

Assume my number is 3.5. Notice 3.5 is not in the list, so I want to
find out where it would go if it were in the list. I want to write a
getPosition function to do this.

After I find out where my number would go, if it were in the list, I
need to perform the following calculations:

A=(sample OD-low OD)/(high OD-low OD)

Titre sample=(A*(high titre-low titre)) + low titre

The "low OD", "high OD", "low titre", and "high titre" values are all
relative to where my number is in the list.

So for 3.5, low OD = 2.912, high OD = 3.815, low titre = 131072, high
titre = 262144.

Could someone please help me with writing a VB function to do this, as
I do not know where to start.

Thanks.


---
Message posted from http://www.ExcelForum.com/