Force a list that has rounding issues to equal a set amount

Hi folks,

I was pleasantly surprised to see some quick responses to my last post and I hope the community doesn't mind if I ask for help with a second issue.
I am often given amounts from a set of accounts and then a separate breakdown of expenses. The total of these expenses must equal the amount given in the accounts, regardless of whether the figure is correct or not (it is a matter of materiality and also client-introduced rounding errors etc). As such I would greatly appreciate a formula that takes the figure I need as correct, adds or reduces the last expense in the list by 1 and then leaves me with a total that agrees which I could then tap into a calculator and know the list adds up to the correct amount. I have gone into detail on the sheet below and I imagine there is a simple way of fixing this, I am just unaware of it. Thank you very much!

Hi JieJenn,

Thanks for the quick reply! I presume that formula should go in my total section. Unfortunately it produces the number 43 which isn't right. The two conditions of this are that the expense list should add up (by hand/calculator) to 316 (C3) and that the total should also equal 316.

If the list doesn't add up properly because of rounding issues etc then I would like one of the expenses, in this case the last, to be altered so that the total is the correct amount, the same as C3.

Hope you don't mind revisiting that and finding what needs changing for me - thank you

Hi FDibbins,

Thanks for your suggestion - the problem there is that when I've tried that it produces a rounded amount again that is incorrect. In my example, scenario 1 - the total amount is correct but the list doesn't actually add up properly. In scenario 2, the same numbers (without any rounded decimals in exp 1+2) add up to 315. So you can see that the numbers are off due to rounding and I can't change the format in which they are expressed (that would be too easy!) :D

Well I'm glad you don't think it's impossible! Unfortunately I don't know how to set up what you have just said, would it be too much to ask you to attach the fixed file? If you can do this then I will be over the moon!

Hi FDibbins, I've tried what you said and unfortunately it still doesn't do what I need it to. It just still gives me 316 and doesn't change the last figure so it adds up manually I blame the rounding again! Is there any other way that you can think of getting this to work? I'm starting to think that you might have been right in the first place with your suggestion about formatting - is it possible to get the total to only take into account the numbers that I can see, not the hidden decimals? So grateful for your help and happy new year! :D

Firstly, thank you both for your responses
Secondly, FDibbins, you're spot on that I want it to be 316, it's just that I'm restricted to not using decimal places when I present this information so unfortunately I can't use your solution as it stands without rounding up that last figure (which would change the total if I didn't have that helper column - as is likely given that I need this formula to work on client-facing documents).

I can see that both you and Shg have done the best thing which is to find the missing amount to get me to 316 but annoyingly I just can't use the decimals. Is there a way to use a SUM function that only takes into account integers and not decimals? That would sort this all out and I wouldn't need a helper column.

I am incredibly grateful for your continued help

Ah - as is the case with these things as soon as you realise what you need precisely, you can go about hunting it on the ever knowledgeable Google One quick search for integer sum got me this http://www.youtube.com/watch?v=4VscDxRErNs perfect! :D

Thank you all again, I'll mark this solved!

That's the least I could do :D

The array formula I've used is {=SUM(INT(O3:O7))}

Thank you again for all your help.