Share via

Cash denomination

Anonymous
2019-12-21T06:32:04+00:00

sir,

in My company pay Plenty of wage to lab our and staff, when currency note counting manually difficult and closing of cash book with denomination 

is more Difficult when wages comes.

is formula  just put into currency value at the cell, just automatically separated

Example 1 :   Raja is person  and his wage Rs 4789, 

                      Babu   is person and his wage Rs 4500      

just i enter 4500  in to cell, automatically separate    Rs 2000  under 1 notes and Rs 500 - under 3 notes and  Rs 200 under 1 notes and Rs 100 under 10 notes and Rs 50 under 1 notes and  Rs 20 under 1 notes and Rs 10 under 1 Notes,  Rs 5 coin under 1 coin and Rs 2 coin under 1 coin and Rs 1 coin under 1 coin should be  come

                                                             Currency Notes 

    Name                Wage Amount         2000             500        200       100      50         20   10       5     2      1

  Raja                     4589                           1                 3             1          10         1          1       1      1    1        1 

  Babu                     4500                            -                2            10         2         2          5         

 Example 2 : 

                if sufficient money availed    above Formula should work and if  sufficient not money availed for example of Babu, but i have different Notes,  Rs 2000 note not availed and but i have Rs  500 notes - 2 nos, Rs 200 notes - 10 nos and Rs 100 nos -2, Rs 50  - 2 Nos and 

Rs 20 Nos - 5 Nos.

but i well known Total wage - Rs 4589 + Rs 4500 : Rs 9089, 

wage allocation  denomination well know : 

 Rs 2000 x 1  : Rs 2000

Rs  500 x 5     : Rs 2500

Rs 200 x 11    : Rs 2200

Rs 100 x 2     :  Rs 200

Rs 50 x 3      : Rs 150

Rs 20 x 6    ; Rs 180

Rs 10x1,      : RS 10

Rs 5 x 1,     ; Rs 5 

Rs 2 x 1      ; Rs 2

Rs 1 x 1    ;  Rs 1

           --------------

                  Rs 9089       

My Problem is  i know denomination but which person which current Notes allocate ?  based on  Rs 2000 for first preference  for Raja and  Remaining Notes allocate to second person.

is any formula is there.. please tell.. already read cash denomination formula some website,

Rs total wage = total denomination -- auto allocation, i do some changes in formula , if sufficient note not availed and take formula next notes and allocate to that person,

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

8 answers

Sort by: Most helpful
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2019-12-21T09:16:54+00:00

    C2:  =ROUNDDOWN(B2/C$1,0)

    D2:  =ROUNDDOWN(($B2-SUMPRODUCT($C$1:C$1*$C2:C2))/D$1,0)

    drag D2 to the right till L2

    M2:  =SUMPRODUCT($C$1:$L$1*C2:L2)

    Drag C2:M2 down

    File \ Options \ Advanced \ section "Display options for this worksheet" \ un-check "Show a zero in cells that have a zero value"

    Andreas.

    Was this answer helpful?

    7 people found this answer helpful.
    0 comments No comments
  2. Anonymous
    2020-03-26T01:25:21+00:00

    Past 2days before, i asked Hour calculation questions, one person answered by custom vba, but my needs without vba ..doing manual excel function..if you have any idea .. suggest me

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments No comments
  3. Anonymous
    2019-12-21T20:39:38+00:00

    You have to have 3 tables:

    1.number of denominations you have

    2.number of denominations you gave away

    3.number of denominations you have left to give away

    so you'll end up with the following formula:

    table1-table2=table3

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments
  4. Anonymous
    2019-12-21T11:02:15+00:00

    C2:  =ROUNDDOWN(B2/C$1,0)

    D2:  =ROUNDDOWN(($B2-SUMPRODUCT($C$1:C$1*$C2:C2))/D$1,0)

    drag D2 to the right till L2

    M2:  =SUMPRODUCT($C$1:$L$1*C2:L2)

    Drag C2:M2 down

    File \ Options \ Advanced \ section "Display options for this worksheet" \ un-check "Show a zero in cells that have a zero value"

    Andreas.

    sir, 

     your Formula works fine, but i have sufficient cash and but limited denomination, example i have Rs 2000 notes 6 and i allocate first person and second person i could not allocate, formula allocate Rs 500 notes, and once five hundred complete, i could not allocate to Third person and etc,  dynamically allocate based on total denomination and individual person wages, and allocation should be mixed amount, example, i i have Rs 100 x50 ; Rs 5000, i cannot allocate - Rs 5000 to one person, Rs 5000 separted to different person i given, like Raja - 100 x 30, Rs babu - Rs 10 x 10, govind - Rs 100 x 10, 

    According to My denomination and wages net amount should be separated by formula...

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments
  5. Anonymous
    2019-12-21T07:07:07+00:00

    Hi.  If the number of currency notes is limited, then Excel's Solver tool may be a good option for working with these constraints.

    However, the total value that you are showing is $8188.   I don't think you have enough to pay $9089.

    sir,

    Typing mistake... but fact you understand it sir.. i don't know Excel solver tool, please suggest

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments