I'll tell you what I see, Deepak. When I open your workbook, the first thing Excel does is tell me that editing is disabled. (That's because I downloaded it from the internet; MS Office is protecting me, you see.) I click the button to enable editing, and it then warns me about embedded macros. I select the option to enable the macro, and I see column 4 is all #VALUE. But when I paste R2C4 over R2C4:R18C4, it fills in the values correctly. So far, so good. I don't think that's an error; it was just temporarily confused because the worksheet didn't have macros enabled at first. At least, that's what I surmise.
There is something wrong, though, or sort of wrong. If you change a value in C3, the premium in C4 is updated correctly; but if you change a value in C2, C4 remains unchanged. This is because Excel can see that RC3 is an argument to the call to premium(), so when RC3 changes then RC4 needs to be recalculated. But Excel cannot tell by looking at the arguments to =premium that RC3 is also part of the equation.
You should fix that, and there are two ways. One is to rewrite your UDF so that both RC3 and RC4 are arguments to Premium, like this:
Code:
Function Premium(Payout, Age)
Select Case Payout
Case 300000: Select Case Age
Case Is < 36: prem = 2879
Case Is < 46: prem = 3188
Case Is < 56: prem = 4698
Case Is < 66: prem = 5422
Case Is < 71: prem = 7033
Case Is < 76: prem = 7865
Case Else: prem = 10347
End Select
Case 400000: Select Case Age
Case Is < 36: prem = 3686
Case Is < 46: prem = 4103
Case Is < 56: prem = 5954
Case Is < 66: prem = 6950
Case Is < 71: prem = 8989
Case Is < 76: prem = 10023
Case Else: prem = 13139
End Select
Case 600000: Select Case Age
Case Is < 36: prem = 4285
Case Is < 46: prem = 4823
Case Is < 56: prem = 7160
Case Is < 66: prem = 8393
Case Is < 71: prem = 10955
Case Is < 76: prem = 11970
Case Else: prem = 15596
End Select
End Select
Premium = prem
End Function
(I don't care whether you use my Case structure or stick to your If statements; I'm just demonstrating that you can use two arguments in the program.) Your call in C4 would then say "=Premium(RC2,RC3)", and the value displayed there would change whenever you changed either C2 or C3.
But the way I'd do it is to set up a table in another sheet and use lookup formulae to find the proper premium on that table. I like programming in VBA, but this looks to me like a case for a table, instead. Take a look at the sample I've uploaded. There are lots of ways to do the lookup—and by the way if you ask macropod he'll probably come up with a better way than the one in my example, he's a whiz at that sort of thing—but I think that for this sort of application, this is better than writing a UDF.