PDA

View Full Version : Rewriting/Improving Formula



defcon_3
04-12-2012, 09:54 PM
Hi guys,

Can you please help me rewriting this formula because it give me error stating "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format" when i try to use it in 97-2003 format. Originally it was used in xlsx format.

=IF(J4>=120000,35,IF(J4>=117750,34,IF(J4>=115500,33,IF(J4>=113250,32,IF(J4>=111000,31,IF(J4>=80000,30,IF(J4>=72000,29,IF(J4>=64000,28,IF(J4>=58000,27,)))))))))


Thanks

georgiboy
04-12-2012, 10:29 PM
You will need to create a custom function in vba then call it from the spreadsheet. Something like this...
Function CalcValue(pVal As String) As Long

If pVal = 1 Then
CalcValue = 111111

ElseIf pVal = 2 Then
CalcValue = 222222

ElseIf pVal = 3 Then
CalcValue = 333333

' and so on

Else
CalcValue = ""
End If

End Function

Then call it with "=CalcValue(A1)" in the spreadsheet.

Hope this helps

mikerickson
04-12-2012, 10:39 PM
You could use
=36-MATCH(J4, {120000,117750,115500,113250,111000,80000,72000,64000,58000}, -1)

defcon_3
04-12-2012, 10:58 PM
Thanks for the both of you.