Help with simplification of IF/OR formula

D

dimexi

Board Regular
Joined
May 21, 2012
Messages
108
Hi All, I was wondering if I could get some help/advice.

I have two tables illustrated below:

Table 1

X values Y Values

1 100

2 200

3 300

4 400

5 500

6 600

7 700



Table 2

Entered X value Linearly interpolated Y

2.56

3.72

4.69

5.82



Now, table 2 is a user input, and my problem is getting a simplistic formula that can look up the entered X value in table 1, if it is not exact, linearly interpolate ONLY between 2 points and return the answer. For example, for 2.56, I would like the formula to go to table 1 and linearly interpolate between 2 and 3 and give the Y value.

I know I can achieve this with nested If/Or statements, that is, I could use =IF(OR(G11>B3,G11<B4,G11=B3,G11=B4),FORECAST(G11,C3:C4,B3:B4),"no") and in place of “no”, I would nest another If and OR to look at the next two pairs. The problem is there is a limit of 7 nested Ifs.

Can anyone help? I suspect the answer is an array formula, but I am not the best at those.



Hope it is clear, and Thank you
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Nested levels of functions: 64
if you've 7 that means you've XL2007 or less so update your profile about Excel version
 
Upvote 0
If you have no objection to using a macro-enabled workbook, then I have a function that will do this interpolation for you, and I will post that code here if you want it.
 
Upvote 0
Even if you don't want to use it, it could help somebody else. To use it for your case, lets say your X values from table 1 are in A2:A8, and your y values from table 1 are in B2:B8. Your call would look like this:
VBA Code: 
=LOOKUPX(2.56,$A$2:$A$8,$B$2:$B$8)

The function code is below, and good luck to you.
VBA Code: 
Public Function LOOKUPX(X As Single, XRange As Range, ValueRange As Range, _
         Optional XLog As Boolean, Optional ErrMsg As String)
'
'   User-defined function for 1 dimensional table lookup with interpolation.
'
'   Written (or more likely "found") Mar-2002.
'   Revised September 2014 to allow the X range and Value range to be non-adjacent to one another, and to allow the X values
'       to be sorted as either ascending or descending.
'
'   Arguments:
'       X -             The table coordinate where the answer is needed.
'       XRange -        The range of x values.
'       ValueRange -    The range for the values to be interpolated in the table.
'       XLog -          0 for linear interpolation, 1 for logarithmic.
'       ErrMsg -        Optional string variable that will be displayed on errors.
'
'   The designation of the direction for x is arbitrary.  Either will work.
'   The X values must be sorted, but they can be sorted ascending or descending.
'
On Error GoTo ErrorHandler  ' Enable error-handling routine.
'
Dim DeltaX As Double, FX0 As Double, FX1 As Double, X0 As Double, X1 As Double, _
        X0Pos As Double, X1Pos As Double, XTable As Range, XFlag As Boolean
'
'   Assign values to optional argument
'
    If IsMissing(XLog) Then
        XLog = False
    End If
'
'   Determine column or row orientation of XRange &
'   Determine if XRange is sorted ascending or descending &
'   Find bracketing value positions in the table &
'   Find bracketing x values and function values.
'   If X is out of range, add to error message and exit function.
'
    If XRange.Column < ValueRange.Column Then
        If Application.WorksheetFunction.Index(XRange, 2, 1) > Application.WorksheetFunction.Index(XRange, 1, 1) Then
            X0Pos = Application.WorksheetFunction.Match(X, XRange, 1)
        Else
            X0Pos = Application.WorksheetFunction.Match(X, XRange, -1)
        End If
        If XRange.Count = X0Pos Then
            XFlag = True
            X1Pos = X0Pos
        Else
            X1Pos = 1 + X0Pos
        End If
        X0 = Application.WorksheetFunction.Index(XRange, X0Pos, 1)
        FX0 = Application.WorksheetFunction.Index(ValueRange, X0Pos, 1)
        X1 = Application.WorksheetFunction.Index(XRange, X1Pos, 1)
        FX1 = Application.WorksheetFunction.Index(ValueRange, X1Pos, 1)
        If Application.WorksheetFunction.Index(XRange, 2, 1) > Application.WorksheetFunction.Index(XRange, 1, 1) And X > X1 Then
            ErrMsg = "Data Out of Range " & ErrMsg
            GoTo ErrorHandler
        ElseIf Application.WorksheetFunction.Index(XRange, 2, 1) < Application.WorksheetFunction.Index(XRange, 1, 1) And X < X1 Then
            ErrMsg = "Data Out of Range " & ErrMsg
            GoTo ErrorHandler
        End If
    Else
        If Application.WorksheetFunction.Index(XRange, 1, 2) > Application.WorksheetFunction.Index(XRange, 1, 1) Then
            X0Pos = Application.WorksheetFunction.Match(X, XRange, 1)
        Else
            X0Pos = Application.WorksheetFunction.Match(X, XRange, -1)
        End If
        If XRange.Count = X0Pos Then
            XFlag = True
            X1Pos = X0Pos
        Else
            X1Pos = 1 + X0Pos
        End If
        X0 = Application.WorksheetFunction.Index(XRange, 1, X0Pos)
        FX0 = Application.WorksheetFunction.Index(ValueRange, 1, X0Pos)
        X1 = Application.WorksheetFunction.Index(XRange, 1, X1Pos)
        FX1 = Application.WorksheetFunction.Index(ValueRange, 1, X1Pos)
        If Application.WorksheetFunction.Index(XRange, 1, 2) > Application.WorksheetFunction.Index(XRange, 1, 1) And X > X1 Then
            ErrMsg = "Data Out of Range " & ErrMsg
            GoTo ErrorHandler
        ElseIf Application.WorksheetFunction.Index(XRange, 1, 2) < Application.WorksheetFunction.Index(XRange, 1, 1) And X < X1 Then
            ErrMsg = "Data Out of Range " & ErrMsg
            GoTo ErrorHandler
        End If
    End If
'
'       Transform to logarithmic interpolation if needed
'
    If XLog Then
        X0 = Log(X0) / Log(10)
        X1 = Log(X1) / Log(10)
        X = Log(X) / Log(10)
    End If
'
'       Calculate Normalized DeltaX
'
    If XFlag Then
        DeltaX = 0
    Else
        DeltaX = (X - X0) / (X1 - X0)
    End If
'
    LOOKUPX = FX0 + DeltaX * (FX1 - FX0)    '   Calculate the interpolated result
'
Exit Function   ' Exit to avoid handler.
'
'   Display user defined or default error message on error.
'
ErrorHandler:
   MsgBox ErrMsg, , "LOOKUPX Function Error"
    Err.Clear   ' Clear Err object fields
    LOOKUPX = ""
End Function
 
Upvote 0
Here is an old school method using AGGREGATE to find the largest x value that is <= the target x. Using that to establish an index, the desired sets of x and y pairs are used in a traditional point-slope formula. This assumes that your given x,y pairs have a monotonically increasing/decreasing character.
Book1
ABCDEF
1X valuesY valuesX inputY interp
211002.56256
322003.72372
433004.69469
544007.2outside range
65500
76600
87700
9
Sheet3
Cell Formulas
RangeFormula
F2:F5F2=IFERROR(((INDEX($B$2:$B$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1)+1)-INDEX($B$2:$B$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1))) * (E2-INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1)))) /(INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1)+1)-INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1)))+(INDEX($B$2:$B$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1))),"outside range")
 
Upvote 0
Try:
Code: 
=FORECAST(G11,OFFSET(INDEX($B$3:$C$9,MATCH(G11,$B$3:$B$9,1),1),,1,2,1),OFFSET(INDEX($B$3:$C$9,MATCH(G11,$B$3:$B$9,1),1),,,2,1))
 
Upvote 0
KRice said:
Here is an old school method using AGGREGATE to find the largest x value that is <= the target x. Using that to establish an index, the desired sets of x and y pairs are used in a traditional point-slope formula. This assumes that your given x,y pairs have a monotonically increasing/decreasing character.
Book1
ABCDEF
1X valuesY valuesX inputY interp
211002.56256
322003.72372
433004.69469
544007.2outside range
65500
76600
87700
9
Sheet3
Cell Formulas
RangeFormula
F2:F5F2=IFERROR(((INDEX($B$2:$B$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1)+1)-INDEX($B$2:$B$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1))) * (E2-INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1)))) /(INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1)+1)-INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1)))+(INDEX($B$2:$B$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1))),"outside range")
Click to expand...
This is awesome!! I get the logic behind it, but I have never used aggregate, so I will study the formula to get an understanding of how it works as a whole.

Sorry for not using XL2BB for my illustration! It has been a while since I posted here haha. Thank you! No doubt I will be back with some questions
 
Upvote 0
FracinDean said:
Even if you don't want to use it, it could help somebody else. To use it for your case, lets say your X values from table 1 are in A2:A8, and your y values from table 1 are in B2:B8. Your call would look like this:
VBA Code: 
=LOOKUPX(2.56,$A$2:$A$8,$B$2:$B$8)

The function code is below, and good luck to you.
VBA Code: 
Public Function LOOKUPX(X As Single, XRange As Range, ValueRange As Range, _
         Optional XLog As Boolean, Optional ErrMsg As String)
'
'   User-defined function for 1 dimensional table lookup with interpolation.
'
'   Written (or more likely "found") Mar-2002.
'   Revised September 2014 to allow the X range and Value range to be non-adjacent to one another, and to allow the X values
'       to be sorted as either ascending or descending.
'
'   Arguments:
'       X -             The table coordinate where the answer is needed.
'       XRange -        The range of x values.
'       ValueRange -    The range for the values to be interpolated in the table.
'       XLog -          0 for linear interpolation, 1 for logarithmic.
'       ErrMsg -        Optional string variable that will be displayed on errors.
'
'   The designation of the direction for x is arbitrary.  Either will work.
'   The X values must be sorted, but they can be sorted ascending or descending.
'
On Error GoTo ErrorHandler  ' Enable error-handling routine.
'
Dim DeltaX As Double, FX0 As Double, FX1 As Double, X0 As Double, X1 As Double, _
        X0Pos As Double, X1Pos As Double, XTable As Range, XFlag As Boolean
'
'   Assign values to optional argument
'
    If IsMissing(XLog) Then
        XLog = False
    End If
'
'   Determine column or row orientation of XRange &
'   Determine if XRange is sorted ascending or descending &
'   Find bracketing value positions in the table &
'   Find bracketing x values and function values.
'   If X is out of range, add to error message and exit function.
'
    If XRange.Column < ValueRange.Column Then
        If Application.WorksheetFunction.Index(XRange, 2, 1) > Application.WorksheetFunction.Index(XRange, 1, 1) Then
            X0Pos = Application.WorksheetFunction.Match(X, XRange, 1)
        Else
            X0Pos = Application.WorksheetFunction.Match(X, XRange, -1)
        End If
        If XRange.Count = X0Pos Then
            XFlag = True
            X1Pos = X0Pos
        Else
            X1Pos = 1 + X0Pos
        End If
        X0 = Application.WorksheetFunction.Index(XRange, X0Pos, 1)
        FX0 = Application.WorksheetFunction.Index(ValueRange, X0Pos, 1)
        X1 = Application.WorksheetFunction.Index(XRange, X1Pos, 1)
        FX1 = Application.WorksheetFunction.Index(ValueRange, X1Pos, 1)
        If Application.WorksheetFunction.Index(XRange, 2, 1) > Application.WorksheetFunction.Index(XRange, 1, 1) And X > X1 Then
            ErrMsg = "Data Out of Range " & ErrMsg
            GoTo ErrorHandler
        ElseIf Application.WorksheetFunction.Index(XRange, 2, 1) < Application.WorksheetFunction.Index(XRange, 1, 1) And X < X1 Then
            ErrMsg = "Data Out of Range " & ErrMsg
            GoTo ErrorHandler
        End If
    Else
        If Application.WorksheetFunction.Index(XRange, 1, 2) > Application.WorksheetFunction.Index(XRange, 1, 1) Then
            X0Pos = Application.WorksheetFunction.Match(X, XRange, 1)
        Else
            X0Pos = Application.WorksheetFunction.Match(X, XRange, -1)
        End If
        If XRange.Count = X0Pos Then
            XFlag = True
            X1Pos = X0Pos
        Else
            X1Pos = 1 + X0Pos
        End If
        X0 = Application.WorksheetFunction.Index(XRange, 1, X0Pos)
        FX0 = Application.WorksheetFunction.Index(ValueRange, 1, X0Pos)
        X1 = Application.WorksheetFunction.Index(XRange, 1, X1Pos)
        FX1 = Application.WorksheetFunction.Index(ValueRange, 1, X1Pos)
        If Application.WorksheetFunction.Index(XRange, 1, 2) > Application.WorksheetFunction.Index(XRange, 1, 1) And X > X1 Then
            ErrMsg = "Data Out of Range " & ErrMsg
            GoTo ErrorHandler
        ElseIf Application.WorksheetFunction.Index(XRange, 1, 2) < Application.WorksheetFunction.Index(XRange, 1, 1) And X < X1 Then
            ErrMsg = "Data Out of Range " & ErrMsg
            GoTo ErrorHandler
        End If
    End If
'
'       Transform to logarithmic interpolation if needed
'
    If XLog Then
        X0 = Log(X0) / Log(10)
        X1 = Log(X1) / Log(10)
        X = Log(X) / Log(10)
    End If
'
'       Calculate Normalized DeltaX
'
    If XFlag Then
        DeltaX = 0
    Else
        DeltaX = (X - X0) / (X1 - X0)
    End If
'
    LOOKUPX = FX0 + DeltaX * (FX1 - FX0)    '   Calculate the interpolated result
'
Exit Function   ' Exit to avoid handler.
'
'   Display user defined or default error message on error.
'
ErrorHandler:
   MsgBox ErrMsg, , "LOOKUPX Function Error"
    Err.Clear   ' Clear Err object fields
    LOOKUPX = ""
End Function
Click to expand...
Thank you FrancinDean, I was trying to keep away from vba coding, mainly because: 1) it has been a while since I used it, so debugging it might be a bit of a mare for me 2) Many people will be using the spreadsheet, so the formula route would be easier for them.
I will keep this in my back pocket, and explore how it works.
 
Upvote 0
JackDanIce said:
Try:
Code: 
=FORECAST(G11,OFFSET(INDEX($B$3:$C$9,MATCH(G11,$B$3:$B$9,1),1),,1,2,1),OFFSET(INDEX($B$3:$C$9,MATCH(G11,$B$3:$B$9,1),1),,,2,1))
Click to expand...
Thanks Jack, I haven't loaded this yet, but I will try it. This is the ideal length of formula I had in mind. I will let you know if it works
 
Upvote 0
Tried to edit the above post, but I think it was too late... Anyway, thanks Jack (again), your version works perfectly and it is easy for me to understand right away. I have never used the offset function to store arrays before, so I have learnt something new and extremely useful for my day to day.

And thanks everyone again for your help, all the options work very well and have expanded my excel knowledge a bit more ;).
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Get Cell Difference in Array SCAN/LAMBDA
Replies
2
Views
69
MrRajKumar
M
M
  • Solved
Need help adapting a formula
Replies
2
Views
357
mattbird
M
S
  • Question
Excel VBA to lookup rows using multiple conditions
Replies
1
Views
246
Roncondor
R
Z
  • Question
Calculation assistance to form an excel formula
Replies
7
Views
150
Zwax
Z
mjones
  • Solved
Tricky counting formula
2 3 4
Replies
36
Views
931
mjones
mjones

Forum statistics

Threads
1,215,042
Messages
6,122,810
Members
449,095
Latest member
m_smith_solihull

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top