Rounding off budget numbers for Dynamics GP Excel import wizard
Over the years certain people in my family have accused me of being lazy. I’m not going to name names but I would imagine it is easy to guess who the accusers might be for those of you who are similar in nature to myself.
My counter argument is I’m just efficient. Why do things that aren’t necessary? Why not do things the easiest way possible?
I’m not sure if what I’m going to show you is necessary or even efficient but I thought it was a fun solution to making sure budget numbers are even on an Excel spreadsheet before importing into GP.
A client came to me with a problem: After importing budget numbers where a total number is divided by twelve to get even numbers throughout all periods this leaves a decimal place on each individual period. When the number is not divisible by twelve GP doesn’t have whole dollar amounts in the total because each period is only allowing for two decimal places.
For example, the numbers circled above all have infinitive decimal ranges and when they imported, because the decimals were cut off at two digits, the totals come out not in whole numbers.
Here’s the solution we came up with. We copied over the spreadsheet to a new worksheet and made the budget numbers a fixed two decimal places. This made the totals go off by around four cents higher or lower for the total.
Formula copied to each cell: =FIXED('2015-1'!D8,2)
That didn’t get us fully what we needed either so we took this same spreadsheet and threw in another couple of columns as shown below:
Column Q has a nice little formula that rounds off the total (column P) to whole numbers and Column R give a new value we replaced Period 12 numbers with.
Column Q formula: =IF(ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))) > 0.95, INT(SUM(C10:O10)) + 1, IF(ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))) < 0.05, INT(SUM(C10:O10)),SUM(C10:O10)))
Column R formula: =IF(ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))) > 0.95, O10 + 0.04, IF(ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))) < 0.05, O10 - ABS(SUM(C10:O10)-TRUNC(SUM(C10:O10))), O10))
These formulas were in the Q10 and R10 cells.
This took a little Excel work but since we could drag down cell references it was way easier than manipulating the period twelve numbers manually.
Now the real confession: Some of you may be saying to yourself “I thought you said you were efficient (Lazy)?” You’re right, It would have taken me a very long time to figure out these formulas myself (or even at all). What I really did was pass this over to my developer and he figured it out for me.
How’s that for being efficient?