Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-14-2022, 07:25 AM
Matt C's Avatar
Matt C Matt C is offline Custom SUM functions are ignoring or rounding up decimals Windows 10 Custom SUM functions are ignoring or rounding up decimals Office 97-2003
Advanced Beginner
Custom SUM functions are ignoring or rounding up decimals
 
Join Date: May 2021
Location: London, UK
Posts: 30
Matt C is on a distinguished road
Question 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.
Reply With Quote
  #2  
Old 05-15-2022, 12:06 PM
Purfleet Purfleet is offline Custom SUM functions are ignoring or rounding up decimals Windows 10 Custom SUM functions are ignoring or rounding up decimals Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I think the Long Data type is for whole numbers only, try variant or double
Reply With Quote
  #3  
Old 05-15-2022, 01:19 PM
Matt C's Avatar
Matt C Matt C is offline Custom SUM functions are ignoring or rounding up decimals Windows 10 Custom SUM functions are ignoring or rounding up decimals Office 97-2003
Advanced Beginner
Custom SUM functions are ignoring or rounding up decimals
 
Join Date: May 2021
Location: London, UK
Posts: 30
Matt C is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
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.
Reply With Quote
  #4  
Old 05-15-2022, 01:30 PM
Purfleet Purfleet is offline Custom SUM functions are ignoring or rounding up decimals Windows 10 Custom SUM functions are ignoring or rounding up decimals Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

This row

Dim xSum As Long

Change to variant
Reply With Quote
  #5  
Old 05-16-2022, 10:30 AM
Matt C's Avatar
Matt C Matt C is offline Custom SUM functions are ignoring or rounding up decimals Windows 10 Custom SUM functions are ignoring or rounding up decimals Office 97-2003
Advanced Beginner
Custom SUM functions are ignoring or rounding up decimals
 
Join Date: May 2021
Location: London, UK
Posts: 30
Matt C is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
This row

Dim xSum As Long

Change to variant
That works. Many thanks.
Reply With Quote
Reply

Thread Tools
Display Modes


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
Custom SUM functions are ignoring or rounding up decimals 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
Custom SUM functions are ignoring or rounding up decimals Merge Field Ignoring Zeros Welshie82 Mail Merge 2 05-04-2012 01:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:45 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft