Hi there,
Need to design a formula for any given amount that tells how many currency note of the following denominations do exist in it.
1000, 500, 100, 50, 20, 10, 5, 2, 1 and the rest of the amount in pennies or cent or some.
The formula does need to calculate in sequence and then decide what is different denominations with the remaining leftover amount. For example if the amount is "99345.50"
Then the formula should tell that this amount contain 99 currency or banknote of 1000 and now what is left is "345.50" so formula should tell that 0 banknote for 500 and 3 for 100 and now what is left is "45.50" so 0 for 50 and 2 for 20 and 0 for 10 and 1 for 5 and 0 for 2 and 0 for 1 and 0.5 for penny or cent or some...hope i make it clear?
I am trying it but it is getting a little complex...please see the attached MS Excel file.
Thanks
Formula-to-Split-any-Amount.xlsx
MOD(A2;1000)
to find the remaining rest after applying the 1000 division and use
FLOOR(A2;3)
to get the count of 1000 notes.
The same can be done with the rest for all other values.