Difference in SUM
Okay I have a spreadsheet with calculations that I am recreating in Smartsheet. Any idea why there would be a difference in the total SUM of the columns when the numbers are the same with the calculations? I am dealing with a % but it is the exact same percentage. Does Smartsheet do something that Excel doesn't behind the scene? Do I need to account for something in my formula? Has anyone else had this experienced this?
Best Answer
-
Ok. To exclude zeroes, you could use a SUMIFS instead. Something along the lines of:
=SUMIFS(CHILDREN(), CHILDREN(), @cell <> 0)
Answers
-
Hi,
Smartsheet handles percent differently. 1 = 100% and 0,1 is 10%.
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I agree with @Andrée Starå. Screenshots with sensitive/confidential data blocked, removed, or replaced with "dummy data" as needed would be very beneficial. It would also help if you could copy/paste your exact formula(s) here as well.
-
The numbers in each column in CAF DUE and VARIANCE match the numbers in the Excel spreadsheet line for line exactly. The CLIN_BILLED_AMT is a provided amount from another system, not a calculated amount. Below are my calculations. The {27398 and 32310 CLIN BILLED AMT ENTITY} is a Pivot chart that Smartsheet created with the totals.
CAF DUE calculation:
=MAX(IF([I/C/A CLIN]@row = "C", [TOTAL_FUNDING]@row, ""), IF([I/C/A CLIN]@row = "C", INDEX({27398 AND 32310 CLIN BILLED AMT}, MATCH([Entity Lookup (HELPER)]@row, {27398 and 32310 CLIN BILLED AMT ENTITY}, 0)) * [I/C/A FEE%]@row / 100))
VARIANCE calculation:
=IF([I/C/A CLIN]@row <> "C", "", [CAF DUE]@row - [CLIN_BILLED_AMOUNT]@row)
Top line calculations for both CAF DUE AND VARIANCE are both:
=SUM(CHILDREN())
SMARTSHEET Totals for CAF Due and Variance:
$7,362,282.23
$3,191,673.15
Excel Totals for CAF Due and Variance:
CAF DUE: $7,362,282.15
VARIANCE: $3,191,673.07
-
Have you manually calculated to see which one was actually correct?
Are there any values that could have more than 2 decimal places (even though only 2 are shown there could be more stored on the back-end which could change the rounding)?
What kind of data is in the {27398 AND 32310 CLIN BILLED AMT} range? I see you are multiplying by [I/C/A FEE%]@row and then dividing by 100. Is it possible that is adjusting how many numbers go beyond 2 decimal places (even though only two are shown)?
-
The data that is in the {27398 AND 32310 CLIN BILLED AMT} is the SUM of CLIN_BILLED_AMT. It's really weird how it has to be calculated. Since you have to calculate the % on the sum of the Total Funded grouped by the CLIN/BATCH # and not include the "C" total funding. Then that total sum is multiplied by the I/C/A Fee %.
I just asked for an unprotected copy of the Excel spreadsheet, and reviewed it based on your recommendation -- and low and behold even though it is displaying 2 decimal points on the Excel spreadsheet it's displaying $0.93, but when you click on it it's actually showing 0.925400000000081. I think I now have discovered where the extra "cents" are coming from. (I think.....)
-
Lets hope that's it. I'll keep my fingers crossed for you! 👍️
Feel free to come back and let us know if that was it.
-
Okay, it's something to do with the 0.00 amounts on the lines. From what I can tell, this is heavily driven by the line items with zero (0) variance. I tested both spreadsheets side by side. I used a range as an example (Smartsheet) and the sum total it calculates is equal to $11.002.95, vs. when I calculate the sum for the same range on my spreadsheet I get $11,002.94. All line items with zero variance are counted on the Smartsheet as part of the sum total as unrounded positive values vs. Excel doesn’t appear to do that. Any thoughts on how to fix that?
-
"............All line items with zero variance are counted on the Smartsheet as part of the sum total .................."
Are you referring to the SM(CHILDREN()) portion?
-
-
Ok. To exclude zeroes, you could use a SUMIFS instead. Something along the lines of:
=SUMIFS(CHILDREN(), CHILDREN(), @cell <> 0)
-
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you Paul and Andrée, but unfortunately I still have a slight discrepancy. It's only a few cents but I have a very detailed accounting department. LOL So, I am just trying to find out the why. Right now I have a ticket in with Smartsheet to see if it's something that I have done or if there is something else. I will post here to let everyone know.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Smartsheet support assisted me. We changed the ) and it fixed the few pennies difference. Thank you so much!
-
Excellent!
Glad you got it working!
✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!