simple subtraction not working

[Zonie32]

I have a ProviderFee, AncillaryFee and PEPMFee.

I want to take the ProviderFee minus AncillaryFee - PEPMFee and come up with a RetentionFee. It doesn't calculate correctly the way I have it.

=Sum([Providerfee]-[AncillaryFee]-[PEPMFee])

example: ProviderFee = 13.00, Ancillary = 5.10, PEPMFee = 4.00. the total Retention should be 3.90, however it is coming up as 11.70?? What is wrong with my formula?

 

[PHV]

Try either this:
=Sum([Providerfee])-Sum([AncillaryFee])-Sum([PEPMFee])
Or this:
=Sum(Nz([Providerfee],0)-Nz([AncillaryFee],0)-Nz([PEPMFee],0))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

You could also try:

=Sum([ProviderFee]-([AncillaryFee]+[PEPMFee]))

Use the Nz function if any of the fields could be Null

Hope this helps.