Custom SUM functions are ignoring or rounding up decimals

Matt C · #1 05-14-2022, 07:25 AM

Hi folks

I have a handful of customised "SUM" functions in my spreadsheet, e.g. SUMSUB, SUMCAT, which calculate the values of cells with specific style names in a column.

Today I noticed that these functions are either ignoring the decimal places or rounding them up to the nearest full number.

Example: 0.50 and below is treated as "0", whereas 0.51 and above is treated as "1".

(Any formulas which use the default "SUM" are working correctly.)

Quote:

Function SumCat(WorkRng As Range)
'
' Calculates cells formatted as "Total Category"
' Example: =SUMCAT(A1:C9)
'
Application.Volatile

Dim rng As Range
Dim xSum As Long

For Each rng In WorkRng
' Finds cells with "Total Category" stylename
If rng.Style = "Total Category" Then
xSum = xSum + rng.Value
End If

Next

SumCat = xSum

End Function

Can anyone help? Number formatting is set to two decimal places and I would like my customised Functions to calculate them accurately.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Ignoring #VALUE in formula calculations	NickFazer	Excel	7	11-09-2018 03:26 PM
Custom rounding up	paulkaye	Excel	8	09-03-2016 11:12 PM
Need to restore custom functions from a corrupt ".xlam" file ...	gamouning	Excel	1	11-12-2015 11:03 AM
Custom formatting code - rounding problem	venkys4u	Excel	1	08-14-2012 07:45 PM
Merge Field Ignoring Zeros	Welshie82	Mail Merge	2	05-04-2012 01:56 AM