Formula or macro
Hi All,
Hope some-one can help. I need a formula or macro that will convert a number which lies with a a range into another number [in order to normalise my data]
Example:
Between 0.01 and 0.99 change to 1
Between 1 and 2 change to 2
Between 2.05 and 4 change to 3
Between 4.05 and 6 change to 4
Between 6.05 and 10 change to 5
Greater than 10 change to 6
The actual "cell range" of the data varies. and all the numbers have 2 decimal places. Hope I have given enough info.
Regards
Walter
Here is the custom VBA
Here is the custom VBA function for the data ranges you have provided. You can modify it as per your requirement.
Function GetNormalisedNum(dblInput As Double) As Double
Select Case dblInput
Case 0.01 To 0.99
GetNormalisedNum = 1
Case 1 To 2
GetNormalisedNum = 2
Case 2.05 To 4
GetNormalisedNum = 3
Case 4.05 To 6
GetNormalisedNum = 3
Case 6.05 To 10
GetNormalisedNum = 5
Case Is > 10
GetNormalisedNum = 6
End Select
End Function
Vishesh
Excellent! works perfectly, thanks a mil!
Worksheet Function Method - Frequency
The frequency function does that too.. Assuming the data has 100 points ..
Data Bin Count
89 10 10 = 10 points up to value 10
83 20 8 = 8 points between 11 and 20
83 30 14 etc
90 40 7
96 50 10
50 60 8
15 70 10
25 80 10
30 90 10
48 100 13
n+