question driving me nuts
 

Okay...I never thought I would ever have to use excel again. Lo and behold, I
am now the owner of a business and have forgotten how to do some things. I
know how to create formaulas for adding and stuff. I forgot how to make my
calculatons so that my deposit would be broken down. For example say I have 5
of each dollar denomination (1,2,5,10,20,50,100) and coin (1,5,10,25,50,$1).
My deposit has to be 790 (5 of each denomination-150).

What I want my sheet to do is tell me how much of each denomination I should
pull out in terms of numbers instead of dollar amount. For exaple instead of
telling me to pull $790 I want it to tell me to pull 5-$100 bills 5-$50 bills
and 4-10 bills. Basically breaking down my cash with the largest possible
denomination until there can be no more. Any help would be deeply appreciated.

question driving me nuts
 

Please do not duplicate posting in multiple groups. If
you "must", do so only if your news reader allows you
to crosspost. (I do not see any way to do that with
Microsoft "community newsgroups".)

See responses in the Microsoft group Excel General
Questions, aka the newsgroup microsoft.public.excel.misc.

question driving me nuts
 

Try code like the following:

Dim Amount As Integer
Dim Num100 As Integer
Dim Num50 As Integer
Dim Num20 As Integer
Dim Num10 As Integer
Dim Num5 As Integer
Dim Num1 As Integer

Amount = 790 ' <<<< CHANGE
Num100 = Amount \ 100
Amount = Amount - (100 * Num100)
Num50 = Amount \ 50
Amount = Amount - (50 * Num50)
Num20 = Amount \ 20
Amount = Amount - (20 * Num20)
Num10 = Amount \ 10
Amount = Amount - (10 * Num10)
Num5 = Amount \ 5
Amount = Amount - (5 * Num5)
Num1 = Amount
Debug.Print Num100, Num50, Num20, Num10, Num5, Num1


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Esaam" wrote in message
...
Okay...I never thought I would ever have to use excel again. Lo
and behold, I
am now the owner of a business and have forgotten how to do
some things. I
know how to create formaulas for adding and stuff. I forgot how
to make my
calculatons so that my deposit would be broken down. For
example say I have 5
of each dollar denomination (1,2,5,10,20,50,100) and coin
(1,5,10,25,50,$1).
My deposit has to be 790 (5 of each denomination-150).

What I want my sheet to do is tell me how much of each
denomination I should
pull out in terms of numbers instead of dollar amount. For
exaple instead of
telling me to pull $790 I want it to tell me to pull 5-$100
bills 5-$50 bills
and 4-10 bills. Basically breaking down my cash with the
largest possible
denomination until there can be no more. Any help would be
deeply appreciated.

question driving me nuts
 

i was trying it out, do ihave to precede eac statement with an equal sign. i
would appreciate it if you could tell me how to enter this. i.e, in one cell,
across multiple cells

"Chip Pearson" wrote:

Try code like the following:

Dim Amount As Integer
Dim Num100 As Integer
Dim Num50 As Integer
Dim Num20 As Integer
Dim Num10 As Integer
Dim Num5 As Integer
Dim Num1 As Integer

Amount = 790 ' <<<< CHANGE
Num100 = Amount \ 100
Amount = Amount - (100 * Num100)
Num50 = Amount \ 50
Amount = Amount - (50 * Num50)
Num20 = Amount \ 20
Amount = Amount - (20 * Num20)
Num10 = Amount \ 10
Amount = Amount - (10 * Num10)
Num5 = Amount \ 5
Amount = Amount - (5 * Num5)
Num1 = Amount
Debug.Print Num100, Num50, Num20, Num10, Num5, Num1


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Esaam" wrote in message
...
Okay...I never thought I would ever have to use excel again. Lo
and behold, I
am now the owner of a business and have forgotten how to do
some things. I
know how to create formaulas for adding and stuff. I forgot how
to make my
calculatons so that my deposit would be broken down. For
example say I have 5
of each dollar denomination (1,2,5,10,20,50,100) and coin
(1,5,10,25,50,$1).
My deposit has to be 790 (5 of each denomination-150).

What I want my sheet to do is tell me how much of each
denomination I should
pull out in terms of numbers instead of dollar amount. For
exaple instead of
telling me to pull $790 I want it to tell me to pull 5-$100
bills 5-$50 bills
and 4-10 bills. Basically breaking down my cash with the
largest possible
denomination until there can be no more. Any help would be
deeply appreciated.

question driving me nuts
 

That is VB code that needs to be entered into the VB Editor within Excel
ToolsMacrosVisual Basic Editor

Chip has just given you the basis of what you need for you to tweak etc.
(I think). Entering this into cells will not help. What you are trying
to do would require code as far as I can tell rather than formulas
entered into the cells..

Esaam wrote:
i was trying it out, do ihave to precede eac statement with an equal sign. i
would appreciate it if you could tell me how to enter this. i.e, in one cell,
across multiple cells

"Chip Pearson" wrote:


Try code like the following:

Dim Amount As Integer
Dim Num100 As Integer
Dim Num50 As Integer
Dim Num20 As Integer
Dim Num10 As Integer
Dim Num5 As Integer
Dim Num1 As Integer

Amount = 790 ' <<<< CHANGE
Num100 = Amount \ 100
Amount = Amount - (100 * Num100)
Num50 = Amount \ 50
Amount = Amount - (50 * Num50)
Num20 = Amount \ 20
Amount = Amount - (20 * Num20)
Num10 = Amount \ 10
Amount = Amount - (10 * Num10)
Num5 = Amount \ 5
Amount = Amount - (5 * Num5)
Num1 = Amount
Debug.Print Num100, Num50, Num20, Num10, Num5, Num1


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Esaam" wrote in message
...

Okay...I never thought I would ever have to use excel again. Lo
and behold, I
am now the owner of a business and have forgotten how to do
some things. I
know how to create formaulas for adding and stuff. I forgot how
to make my
calculatons so that my deposit would be broken down. For
example say I have 5
of each dollar denomination (1,2,5,10,20,50,100) and coin
(1,5,10,25,50,$1).
My deposit has to be 790 (5 of each denomination-150).

What I want my sheet to do is tell me how much of each
denomination I should
pull out in terms of numbers instead of dollar amount. For
exaple instead of
telling me to pull $790 I want it to tell me to pull 5-$100
bills 5-$50 bills
and 4-10 bills. Basically breaking down my cash with the
largest possible
denomination until there can be no more. Any help would be
deeply appreciated.