TheThinker
New Member
- Joined
- Jul 29, 2010
- Messages
- 5
Hey all and thanks in advance for helping me with this. I'm currently working with Excel 2007 on WinXP Pro SP3. The file was originally created in Excel 2003, so it's saved as .XLS.
This is kind of difficult to explain, but I have two columns of calculated values (G and I) that I'm trying to compare to values in a matrix on another sheet so that a value within the cell at a particular point in the matrix can be selected. Basically, it's a risk response matrix and the values in G and I are impact and likelihood, respectively. You end up with a formula which, in English, appears like:
If G is less than a certain value on the Y axis of the matrix, and I is less than a certain value on the X axis of the matrix then select the value at the intersecting point.
I've gotten it to the point that it seems to pull some values, but they aren't correct at all.
Here's the code I'm working with:
And here's a link to the document:
https://docs.google.com/leaf?id=0B8...OTZjNjE1NzBlZDMy&sort=name&layout=list&num=50
This is kind of difficult to explain, but I have two columns of calculated values (G and I) that I'm trying to compare to values in a matrix on another sheet so that a value within the cell at a particular point in the matrix can be selected. Basically, it's a risk response matrix and the values in G and I are impact and likelihood, respectively. You end up with a formula which, in English, appears like:
If G is less than a certain value on the Y axis of the matrix, and I is less than a certain value on the X axis of the matrix then select the value at the intersecting point.
I've gotten it to the point that it seems to pull some values, but they aren't correct at all.
Here's the code I'm working with:
Code:
Function rValue(pval As String) As String
If pval = " " Then
rValue = " "
ElseIf pval = 0 Then
rValue = " "
ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
rValue = Range("Assumptions!N12").Value
ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L12").Value
ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J12").Value
ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H12").Value
ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F12").Value
ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7") Then
rValue = Range("Assumptions!N11").Value
ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L11").Value
ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J11").Value
ElseIf pval < Range("assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H11").Value
ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F11").Value
ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
rValue = Range("Assumptions!N10").Value
ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L10").Value
ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J10").Value
ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H10").Value
ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F10").Value
ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
rValue = Range("Assumptions!N9").Value
ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L9").Value
ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J9").Value
ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H9").Value
ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F9").Value
ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
rValue = Range("Assumptions!N8").Value
ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L8").Value
ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J8").Value
ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H8").Value
ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F8").Value
Else: rValue = " "
End If
End Function
https://docs.google.com/leaf?id=0B8...OTZjNjE1NzBlZDMy&sort=name&layout=list&num=50
Last edited: