Need help with the formulas, please

[AceyWolf]

Manual Handpyaments V1.4.xlsx (62.4 KB)

I recently created a post asking how I could have the figures automatically calculated using a set amount, I was given the answer but now I am stuck with trying to add another value to the sheet.

Been testing the formula on the “USEME 1” worksheet

When a number is inputted into the “ticket” cell, I need it so the “20s, 10s, 1s, 0.10s” are calculated automatically from the Float Amounts.

I thought I had managed to figure it out, however in the 0.10 (10p) cell, it doesn’t always show the correct figure and will sometimes miss out 10p.

The formula that I’m using was provided by another member of the community, so I don’t really understand how it works just yet, I mean, I understand it, I just don’t understand it, if that makes sense!

Also, this is done in an excel workbook because my workplace uses excel.
I personally use LibreCalc.

Any help with this would be greatly appreciated.
Things out work keep breaking so this would be a huge relief for staff

[keme1]

Your formulas seem to use proper rounding as required, so as long as the initial amount in your spreadsheet is not subject to any rounding error (and even if it were), the results should be consistent.

I’d use the FLOOR() function instead. It gives more compact formulas, but it hides some of the math so you may find it less intuitive. Both approaches should yield identical results.

For an example of using of the FLOOR() function, see attached file. optimalchange.ods (9.0 KB)

I cannot see or provoke the error you describe. Can you attach a spreadsheet which actually displays the problem you are having? If the original upload does show the error, please point it out for the dense among us (such as myself).

[keme1]

Nevermind! I see a case.

To circumvent the error, which probably is a case of accumulated rounding error, more elaborate rounding seems to be necessary. See my "FLOOR() sample updated:
optimalchange.ods (10.2 KB)

For the formula in your column H, I believe the following adjustment should fix it.

=IF(INT((ROUND(D2-E2-F2-G2;2)/H$1)*H$1)>$N$11;$N11;INT(ROUND(D2-E2-F2-G2;2)/H$1)*H$1)

You may need a similar addition to the formulas in F and G columns to give consistent results, but I don’t think so.

[AceyWolf]

Manual Handpyaments V1.4.xlsx (61.9 KB)

So I managed to figure out the problem, with the help your formulas, the pounds figure was being rounded when it didn’t need to be, causing an error in the math, I’ve altered the formula to match with how the 20/10s work and used round function on the 0.1(10p) figure, it all works a treat now.

This, however, has lead to another issue which I hope you can help me with!
I’ve attached an updated spreadsheet to this reply.

So basically, I’m trying to get this sheet to have the values worked out automatically, so my colleagues don’t have to keep manually counting and inputting the numbers.

The formulas setup in E2 to H2 pull from the static number in N8 initially, then further inputs would pull from the “remaining figure”, however if I try to apply this same formula to the next row (I.E E3 to H3) and change the pull cell number from N8 to O8 (Reference to sheet), it keeps giving me a circular reference error.

I hope this makes sense!