Solved: Custome Function Returns Wrong Value

[rajkumar]

Hi,

I am learning VBA, trying to write a custom function for me.

I need to calculate productivity per engineer that how many calls he is doing average in a day. The formula i use is
= Total calls + Broken Calls / 23 - Leaves + Holidays

I wrote a function like this ..
[VBA]
Function CALLSPERCE(TotalCalls, BrokenCalls, Leaves, Holidays)
CALLSPERCE = TotalCalls + BrokenCalls / 23 - Leaves + Holidays
End Function
[/VBA]

This returns me a value of 21.2. Whereas if i use native excel functions
=SUM(A2:B2)/(23-SUM(C22)) i am getting correct result (1.8)

I have no idea on what mistake i made here.

Can anybody help to correct this one. I have attached a sample workbook here.

Raj

[brettdj]

It's just an issue of putting the parentheses in the right place, ie

[vba]Function CALLSPERCE(TotalCalls, BrokenCalls, Leaves, Holidays)
Application.Volatile True
CALLSPERCE = (TotalCalls + BrokenCalls) / (23 - (Leaves + Holidays))
End Function[/vba]

[rajkumar]

Thanks Brettdj,

It worked perfectly
Raj