Data doesn't match the data from Excel when replicating formulas

SteCoxy

Hello,

I'm trying to recreate/transfer a salary/reward solution in Excel over to Smartsheet and I've been able to recreate a good portion of the columns that have complex formulas, but there seems to be a number of discrepencies with some of the data where it is out by a few thousand pounds or a couple of percentage points.

In the screenshot above, I've highlighted the data in red where there's a discrepency and then added an additional column in to show what the data should be as per the Excel.

The columns use the following formula in Excel:

=IFERROR(ROUND(V13+AP13+AW13,0),"Please ensure no issues with inclusion and rationales")

=IFERROR(ROUND(AY13/U13,0),"Please ensure no issues with inclusion and rationales")

=IFERROR(ROUND(AY13+W13+IF(RIGHT(P13,4)="(WB)",0,X13)*(AF13+AY13),0),"Please ensure no issues with inclusion and rationales")

=IFERROR(ROUND(BA13/U13,0),"Please ensure no issues with inclusion and rationales")

=IFERROR((BB13-S13)/(T13-S13),"Please ensure no issues with inclusion and rationales")

=IFERROR(ROUND(AP13+AW13,0),"Please ensure no issues with inclusion and rationales")

=IFERROR(BD13/V13,"Please ensure no issues with inclusion and rationales")

So I think when I've replicated it in Smartsheet, there seems to be something going wrong with the ROUND function? This then has a knock-on effect on the other columns that are getting referenced.

If it's easier to investigate I can share the original Excel and the Smartsheet I've created?

Paul Newcome

What are the Smartsheet equivalent formulas that you used?

SteCoxy

Hi Paul,

Thanks for coming back to me. Here are the Smartsheet equivalents:

=IFERROR(ROUND([CURRENT TTC: Base Salary]@row + [AUTOMATIC AWARD: £ increase to base salary]@row, 0), "Please ensure no issues with inclusion and rationales")

=IFERROR(ROUND([Final Prop Comp: New Actual Base Salary]@row / [CURRENT TARGET TOTAL COMPENSATION (TTC): FTE]@row, 0), "Please ensure no issues with inclusion and rationales")

=IFERROR(ROUND([Final Prop Comp: New Actual Base Salary]@row + [CURRENT TTC: Historic Allowance(s)]@row + IF(RIGHT([CPF & JOB FAMILY RANGE: CPF Job Title]@row, 4) = "(WB)", 0, [CURRENT TTC: Target Bonus %]@row) * ([ADDITIONAL COMPENSATION: Weighting Allowance]@row + [Final Prop Comp: New Actual Base Salary]@row), 0), "Please ensure no issues with inclusion and rationales")

=IFERROR(ROUND([Final Prop Comp: New Actual TTC]@row / [CURRENT TARGET TOTAL COMPENSATION (TTC): FTE]@row, 0), "Please ensure no issues with inclusion and rationales")

=IFERROR(([Final Prop Comp: New FTE TTC]@row - [CPF & JOB FAMILY RANGE: JPR Min]@row) / ([CPF & JOB FAMILY RANGE: JPR Max]@row - [CPF & JOB FAMILY RANGE: JPR Min]@row), "Please ensure no issues with inclusion and rationales")

=IFERROR(ROUND([AUTOMATIC AWARD: £ increase to base salary]@row + [INCREASES: Discr pay prog actual £ increase]@row, 0), "Please ensure no issues with inclusion and rationales")

=IFERROR([Final Prop Comp: Overall pay progression £ incr]@row / [CURRENT TTC: Base Salary]@row, "Please ensure no issues with inclusion and rationales")

Paul Newcome

In the first set you are adding three cell together in Excel but only two in Smartsheet.

=IFERROR(ROUND(V13+AP13+AW13,0),"Please ensure no issues with inclusion and rationales")

=IFERROR(ROUND([CURRENT TTC: Base Salary]@row + [AUTOMATIC AWARD: £ increase to base salary]@row, 0), "Please ensure no issues with inclusion and rationales")

Aside from that, the rest seem to be matching although it is hard to tell for sure since Excel uses "AB" instead of [Column Name]. I am assuming you have definitely verified each cell reference is pointing to the appropriate column?