Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4726
  • Last Modified:

Formula to tell how many currency note of different denominations are present in any given amount

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
0
masmatc
Asked:
masmatc
  • 6
  • 5
  • 4
  • +1
1 Solution
 
HugoHiaslCommented:
Use the function

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.
0
 
HugoHiaslCommented:
ups... I meant

FLOOR (A2/1000;3)
0
 
HugoHiaslCommented:
*argh* my notebook keyboard is driving crazy today...

Last correction:

FLOOR(A2/1000;1)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
andrewssd3Commented:
As it's not just a simple formula, here is a modified workbook - I have added a row (which you could hide) that contains the denominations at the top. This solution is based on using SUMPRODUCT, but I'm sure there are other ways. Formula-to-Split-any-Amount-1-.xlsx
0
 
masmatcAuthor Commented:
HugoHiasl:

if possible...can you please apply the formula in the attached Excel file

andrewssd3:

I am checking your provided modified workbook and will let you know
0
 
HugoHiaslCommented:
This is much to complicated. I implemented my version also in the workbook

 Copy-of-Formula-to-Split-any-Amo.xlsx
0
 
masmatcAuthor Commented:
andrewssd3:

thanks so far looks good to me...will test it more

The dollar sign is for absolute reference or some right? and i guess it has to be like this(excuse my excel skills)

And what about the pennies or cent...formula is not showing it?
0
 
andrewssd3Commented:
Hi - apologies - I had not taken the INT() formula off the last column, so it was not showing the fractional part.  Yes the dollar signs make the references absolute, so that you can safely drag the formulas down to new rows and they will still work Formula-to-Split-any-Amount-1-.xlsx
0
 
masmatcAuthor Commented:
HugoHiasl:

Looks good...however have a list of amount in the column A...like first amount in A2 then second
amount is in A3 and other amount to split is in A4 so on and so forth...and the provided solution is simple but spread on two rows...any suggestion for that please!
0
 
andrewssd3Commented:
The reason the formulas look more complicated in my solution is that I have put everything on one row, rather than maintaining a running total in another row. You could change the formulas in HugoHiasl's solution to do it all in one row, but I think the result would look pretty much like the one I suggested, but maybe using the FLOOR formula instead.
0
 
masmatcAuthor Commented:

andrewssd3:

your solution is working fine and i need to have it in one row...so that is awesome...however a little complex depending on where you are on the Excel skill.

How to change the formulas in HugoHiasl's  solution to do it all in one row (using FLOOR or both MOD and FLOOR)...it seems this way that FLOOR and MOD are little simple to understand and explain to others as compared to Array concept.

Can you please use MOD and FLOOR to give the same result? appreciate your help
0
 
barry houdiniCommented:
Not for Points

Hello masmatc,

In my opinion andrewssd3 has suggested the simplest generic solution available if you need the calculations on a single row. A simplified approach (with MOD) would only be possible if each denomination is a factor of the previous denomination....and that isn't the case here because 20 is not a factor of 50 and 2 is not a factor of 5.

SUMPRODUCT is a useful function to learn - useful for many things but used here in it's simplest form, multiplying pairs of numbers from two ranges and summing those products.

regards, barry
0
 
andrewssd3Commented:
I'm not sure that it can be done more simply in one row - you are always left with the problem of how to keep the running total going without having an extra 'helper' row.  If you look at the formula, it's not actually so complicated: Col B has a different formula as it's the first, so we just divide the initial amount by the first denomination.  The denominations are in Row2 so they would be easy to change if you wanted to this for another currency system (e.g. in the UK we don't have 1000 or 500 notes).

Columns C through J have actually the same formula.  That's the reason for the dollar signs, so you can enter the formula in col C and drag it across to row J. Each of those formulas takes the initial amount, and subtracts the sum of the previous columns so far (SUMPRODUCT multiplies the number of notes by the denomination, and adds this up for each column).  The formulas look a little different because each one sums an extra column. The INT function just throws away the fractional part. If you did not use SUMPRODUCT here you'd probably have to use an array formula entered with ctrl-shift-enter, which would be less easy to understand.

Then the final column is just the same, but without the INT, so that we keep the fractional part.

I really like simple solutions, but I can't see a better way of doing this.  It's flexible so you can add or change denominations, and you can add new rows just by dragging down.
0
 
andrewssd3Commented:
Barry - thanks for the backup!  Our posts crossed over so I hadn't seen yours when I was typing my long explanation.

Stuart
0
 
masmatcAuthor Commented:
EXCELLENT!! WORK in designing and explaining the solution

Many thanks to all and specially to andrewssd3:
0
 
masmatcAuthor Commented:
Timely, detailed and brilliant!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now