A single currency note of value one is divided into 1000 units. If one enters a value at B1 the formula will calculate numbers of note of 20, 10, 5, 1, ½ and ¼ as well as number of coins for 100, 50, 20, 10 and 1.
I tried to calculate through a formula. But I can’t understand why excel is not showing the correct result for coin 100.
Please help me to complete this exercise.
Thanking you .
Breakdown Of Currency Denominations
-
-
-
Re: Currency Denominations
Quote from ismailA single currency note of value one is divided into 1000 units. If one enters a value at B1 the formula will calculate numbers of note of 20, 10, 5, 1, ½ and ¼ as well as number of coins for 100, 50, 20, 10 and 1.
I tried to calculate through a formula. But I can’t understand why excel is not showing the correct result for coin 100.
Please help me to complete this exercise.
Thanking you .Do not use the int function in the formula.
make it
=(b1-B10)/.1
-
Re: Currency Denominations
Thanks for the quick reply.
Pls enter 188.166 at B1 and see the formula at b11 as (b1-b10)/.1 give result as 2 which not correct. -
Re: Currency Denominations
Try the following and see if it works:
=VALUE(INT(TEXT(((b1-B10)/0.1),"#.##")))
-
Re: Currency Denominations
What result are you expecting? You say 2 in your sample, but then say "give result as 2 which not correct."
-
-
Re: Breakdown Of Currency Denominations
Ismail,
I think you've hit on one of the limits of Excel. It simply cannot do the thousands, It's an interesting problem and I'm working on it... no promises.
Cheers,
dr
anybody?
-
Re: Breakdown Of Currency Denominations
Ismail,
The problem lies with the Integer formula. The formula states that it rounds down to the nearest integer. And when we evaluate that against the result of your original amount minus the amount already accounted for with "notes" it results in a whole number (integer) and it then rounds it down.In the case of the original amount being changed to 188.166 the result would not be a whole number, therefore rounding it down to the next integer which will make it correct in the case of the 100 coin column. The same issue would have appeared for the 1 coins, the result should be 1 but it returns 0.
I have no solution, at least with formulas. Maybe someone can help with code.
Later
-
Re: Breakdown Of Currency Denominations
Ismail,
Did you try and test the other formula I posted. It worked for the two examples you had (188.7 & 188.166)
-
Re: Breakdown Of Currency Denominations
Hi Ismail,
Base 1000, of course! The attached solves your problem I think. I tested it fairly well using .001 to 1.999.
Since the note values are non decimal I simply used Quotient on them.
The coin values however are .### so I used *1000 to bring them back from the decimal side <g>, Int function to cover Excels' rounding, and Quotient to get the remaining values.
Works for me...
Cheers,
dr
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!