Interpolation and calculation from table

[bujaman]

OK, I am trying to interpolate and perform calculations from a table based on multiple variables at the same time. I have sort of figured out part using nested if statements, but it is getting ugly fast, so I am hoping for some help. What I have is a table defining how to find the value for Ca. That value is based on 3 inputs, whether something is flat or round, a predetermined value of C, and something called the Aspect Ratio. If a user selects a flat object, and the aspect ratio is less than 2.5, Ca = 1.2. If The aspect ratio if between 2.5 and 7, then the sheet needs to interpolate Ca somewhere between 1.2 and 1.4, and so on. If the user selects round, it gets more complicated. I have attached the table and some sample data to hopefully show what I am looking for. I need to fill in the Ca column with the proper value based on the requirements in the table and the data given. Any ideas
?

[p45cal]

In the table you have, for the round shape, this:
C < 4.4
4.4 ≤ C ≤ 8.7
C > 64

but what happens for values of C greater than 8.7 and less than or equal to 64 (I realise that in the sample values for C in column B there aren't any, but I expect it's possible!) ?

ps. It might be helpful if you were to manually calculate the values you expect to be returned in each case (in D2:D9) so that we can test against them; could you update the file?

Are these the values you want for the Flats?:
Shape C Aspect Ratio Ca
Flat 3.2 1.5 1.2
Flat 27.6 7.5 1.416666667
Flat 10.3 15.8 1.693333333
Flat 7.5 24.9 1.996666667
Flat 1.5 45.6 2

[p45cal]

For others there's at least 1 cross post:
http://www.mrexcel.com/forum/excel-questions/728598-multiple-interpolation-based-table-input-data.html

For bujaman, some light reading:http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

[bujaman]

I figured it out. The best way I came up with was writing a custom function. Here it is:

Function Ca(Shape, C, AR)

If Shape = "Flat" And AR <= 2.5 Then
Ca = 1.2
ElseIf Shape = "Flat" And AR >= 25 Then
Ca = 2
ElseIf Shape = "Flat" And AR > 2.5 And AR < 7 Then
Ca = 1.2 + (AR - 2.5) * (1.4 - 1.2) / (7 - 2.5)
ElseIf Shape = "Flat" And AR >= 7 And AR < 25 Then
Ca = 1.4 + (AR - 7) * (2 - 1.4) / (25 - 7)
End If


If Shape = "Round" And C < 4.4 Then
If AR <= 2.5 Then
Ca = 0.7
ElseIf AR > 2.5 And AR < 7 Then
Ca = 0.7 + (AR - 2.5) * (0.8 - 0.7) / (7 - 2.5)
ElseIf AR >= 7 And AR < 25 Then
Ca = 0.8 + (AR - 7) * (1.2 - 0.8) / (25 - 7)
ElseIf AR >= 25 Then
Ca = 1.2
End If
End If


If Shape = "Round" And C > 8.7 Then
If AR <= 2.5 Then
Ca = 0.5
ElseIf AR > 2.5 And AR < 7 Then
Ca = 0.5 + (AR - 2.5) * (0.6 - 0.5) / (7 - 2.5)
ElseIf AR >= 7 And AR < 25 Then
Ca = 0.6
ElseIf AR >= 25 Then
Ca = 0.6
End If
End If


If Shape = "Round" And C >= 4.4 And C <= 8.7 Then
If AR <= 2.5 Then
Ca = 1.43 / (C ^ 0.485)
ElseIf AR > 2.5 And AR < 7 Then
Ca = 1.43 / (C ^ 0.485) + (AR - 2.5) * (1.47 / (C ^ 0.415) - (1.43 / (C ^ 0.485))) / (7 - 2.5)
ElseIf AR >= 7 And AR < 25 Then
Ca = 1.47 / (C ^ 0.415) + (AR - 7) * ((5.23 / C) - (1.47 / (C ^ 0.415))) / (25 - 7)
ElseIf AR >= 25 Then
Ca = 5.23 / C
End If
End If




End Function

[p45cal]

That is certainly the cleanest way to do it for the worksheet!
Don't forget to mark any cross posts as solved so that no unwitting helper works needlessly for a solution you've already got.

[Kenneth Hobs]

Function Ca(Shape As String, C As Double, AR As Double) As Double
Application.Volatile False
Select Case LCase(Shape)
Case "flat"
Select Case True
Case AR <= 2.5
Ca = 1.2
Case AR >= 25
Ca = 2
Case AR > 2.5 And AR < 7
Ca = 1.2 + (AR - 2.5) * (1.4 - 1.2) / (7 - 2.5)
Case AR >= 7 And AR < 25
Ca = 1.4 + (AR - 7) * (2 - 1.4) / (25 - 7)
Case Else
End Select
Case "round"
Select Case True
Case C < 4.4
Select Case True
Case AR <= 2.5
Ca = 0.7
Case AR > 2.5 And AR < 7
Ca = 0.7 + (AR - 2.5) * (0.8 - 0.7) / (7 - 2.5)
Case AR >= 7 And AR < 25
Ca = 0.8 + (AR - 7) * (1.2 - 0.8) / (25 - 7)
Case AR >= 25
Ca = 1.2
Case Else
End Select
Case C > 8.7
Select Case True
Case AR <= 2.5
Ca = 0.5
Case AR > 2.5 And AR < 7
Ca = 0.5 + (AR - 2.5) * (0.6 - 0.5) / (7 - 2.5)
Case AR >= 7 And AR < 25
Ca = 0.6
Case AR >= 25
Ca = 0.6
Case Else
End Select
Case C >= 4.4 And C <= 8.7
Select Case True
Case AR <= 2.5
Ca = 1.43 / (C ^ 0.485)
Case AR > 2.5 And AR < 7
Ca = 1.43 / (C ^ 0.485) + (AR - 2.5) * (1.47 / _
(C ^ 0.415) - (1.43 / (C ^ 0.485))) / (7 - 2.5)
Case AR >= 7 And AR < 25
Ca = 1.47 / (C ^ 0.415) + (AR - 7) * ((5.23 / C) - _
(1.47 / (C ^ 0.415))) / (25 - 7)
Case AR >= 25
Ca = 5.23 / C
Case Else
End Select
Case Else
End Select
Case Else
End Select
End Function

[p45cal]

bujaman, please:
Don't forget to mark any cross posts as solved so that no unwitting helper works needlessly for a solution you've already got.http://www.mrexcel.com/forum/excel-questions/728598-multiple-interpolation-based-table-input-data.html