Forum Discussion

simonjudd's avatar
simonjudd
Copper Contributor
Feb 21, 2020
Solved

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 total116,391.62
Week 129,097.91
Week 229,097.91
Week 329,097.90
Week 429,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.

2 Replies

  • Savia's avatar
    Savia
    Steel Contributor
    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.