PDA

View Full Version : User Defined Function - Payment(mode, price)



clarksonneo
04-15-2011, 06:03 AM
Hi,

I want to create an User Defined Function.

Payment(mode, price)

The function is:

mode: m or a - a text
price: any number

if mode is m, then
payment = price / 12

if mode is a, then
payment = price

I have tried to create the function.
However, it doesn't work.
Please see below for the code.



Function Payment(mode, price)

If mode = m Then

Payment = price / 12

ElseIf mode = a Then

Payment = price

End If

End Function



Thanks

mikerickson
04-15-2011, 06:05 AM
What do you mean by "doesn't work"?
Does it return the wrong results or does putting =PRICE("m", 24) in a cell result in and error value or ... ?

clarksonneo
04-15-2011, 06:33 AM
What do you mean by "doesn't work"?
Does it return the wrong results or does putting =PRICE("m", 24) in a cell result in and error value or ... ?

when I type =payment(m, 120),
the result is #VALUE!

However, I expect the result is 120 / 12 = 10

mikerickson
04-15-2011, 06:42 AM
An text literal has to be inside quotes.
Try =Payment("m", 120)

Also, your test is case sensitive (i.e. =Payment("M",120) would return 0). This version is not case sensitive.
Function Payment(mode As String, price As Double) As Double

mode = LCase(mode)

If mode = "m" Then
Payment = price / 12
ElseIf mode = "a" Then
Payment = price
End If
End Function

clarksonneo
04-15-2011, 07:06 AM
An text literal has to be inside quotes.
Try =Payment("m", 120)

Also, your test is case sensitive (i.e. =Payment("M",120) would return 0). This version is not case sensitive.
Function Payment(mode As String, price As Double) As Double

mode = LCase(mode)

If mode = "m" Then
Payment = price / 12
ElseIf mode = "a" Then
Payment = price
End If
End Function

hi, thank you for your reply

it is possible to amend the code so that we don't need to use quotes - " "
?

ie, Payment(m, 120) will give 12

thanks

mikerickson
04-15-2011, 07:10 AM
No, its not.

Note, if the single character m is in A1, =Payment(A1, 120) will return 10. A1 need not contain the three characters "m". (Actualy if A1 held those three characters, = Payment(A1, 120) would return 0 )