Forum Discussion
simonjudd
Feb 21, 2020Copper Contributor
Need to split a figure across multiple rows, but not past 2 decimal places
Hi everyone,
We have a spreadsheet set-up for a financial return but we have an anomaly in it that I need to solve.
Currently, we have a monthly figure (e.g. 116,391.62) that needs to be split across 4 (or sometimes 5) weeks.
If we do that, 116,391.62 / 4 = 29,097.905
Currently, if we use this in subsequent weekly calculations, we now have an errant .005 hanging around (or half a penny). Which of course is no good.
Can someone help me out with a function that would split the figure across the 4 (or 5) weeks, but then distribute the errant penny? So it looks something like this:
| Monthly total | 116,391.62 |
| Week 1 | 29,097.91 |
| Week 2 | 29,097.91 |
| Week 3 | 29,097.90 |
| Week 4 | 29,097.90 |
Thank you
- I'd suggest using =ROUND(B$1/4, 2) or similar for the first three weeks, and then =$B1-SUM(B2:B4) for the bottom one.