# Difference in SUM

Options
Overachievers Alumni

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?

«1

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• Overachievers Alumni
Options

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

• ✭✭✭✭✭✭
Options

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)?

• Overachievers Alumni
Options

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.....)

• ✭✭✭✭✭✭
Options

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.

• Overachievers Alumni
Options

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?

• ✭✭✭✭✭✭
Options

"............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?

• Overachievers Alumni
Options
• ✭✭✭✭✭✭
Options

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.

• Overachievers Alumni
Options

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.

• ✭✭✭✭✭✭
Options

Happy to help!

Yes, keep us posted!

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.

• Overachievers Alumni
Options

Smartsheet support assisted me. We changed the ) and it fixed the few pennies difference. Thank you so much!

• ✭✭✭✭✭✭
Options

Excellent!

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!