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.

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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

That is VB code that needs to be entered into the VB Editor within Excel
Tools>Macros>Visual 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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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.
>>
>>
>>