Microsoft Office Forums Custom SUM functions are ignoring or rounding up decimals
 Register FAQ Search Today's Posts Mark Forums Read

#1
Yesterday, 07:25 AM
 Matt C Windows 10 Office 97-2003 Novice Join Date: May 2021 Location: London, UK Posts: 25
Custom SUM functions are ignoring or rounding up decimals

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.
#2
Today, 12:06 PM
 Purfleet Windows 10 Office 2019 Expert Join Date: Jun 2020 Location: Essex Posts: 342

I think the Long Data type is for whole numbers only, try variant or double
#3
Today, 01:19 PM
 Matt C Windows 10 Office 97-2003 Novice Join Date: May 2021 Location: London, UK Posts: 25

Quote:
 Originally Posted by Purfleet I think the Long Data type is for whole numbers only, try variant or double
Thanks, Purfleet. Would you mind posting an example of the code line I need to add/amend? I'm still feeling my way through this area of VBA programming.
#4
Today, 01:30 PM
 Purfleet Windows 10 Office 2019 Expert Join Date: Jun 2020 Location: Essex Posts: 342

This row

Dim xSum As Long

Change to variant

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post NickFazer Excel 7 11-09-2018 03:26 PM paulkaye Excel 8 09-03-2016 11:12 PM gamouning Excel 1 11-12-2015 11:03 AM venkys4u Excel 1 08-14-2012 07:45 PM Welshie82 Mail Merge 2 05-04-2012 01:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:34 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top