+ Reply to Thread
Results 1 to 15 of 15

Extract correct denomination of bills, money counter, remove large bills,

  1. #1
    Registered User
    Join Date
    02-28-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Extract correct denomination of bills, money counter, remove large bills,

    I need help in extracting the largest bills from avaialble bills to get a set total. I am trying to create atemplate to remove the largest bills from all the avaialble bills.
    In the case below, I want to extract exactly $$1,178.29 in largest available bills from a register which has $1328.29 . THis will leave the reigter with $150 exactly in change every time


    3 $100.00 $300.00
    3 $100.00 $300.00
    $50.00 $0.00
    31 $20.00 $620.00
    3 $10.00 $30.00
    7 $5.00 $35.00
    2 $1.00 $2.00
    16 $0.25 $4.00
    1 $0.10 $0.10
    23 $0.05 $1.15
    4 $0.01 $0.04
    2 $10.00 $20.00
    2 $5.00 $10.00
    2 $2.00 $4.00
    4 $0.50 $2.00
    $1,328.29



    $150.00
    $1,178.29

  2. #2
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Excel 2019
    Posts
    447

    Re: Extract correct denomination of bills, money counter, remove large bills,

    I think you need 2nd largest number from a range, then =LARGE(A1:A100,2)

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,671

    Re: Extract correct denomination of bills, money counter, remove large bills,

    Maybe like this:

    Please Login or Register  to view this content.
    B1: =SUMPRODUCT(A4:A14, B4:B14)

    C1: =B1-150

    D1: =SUMPRODUCT(B4:B14, D4:D14)

    C4 and down: =MIN(A4, INT( CEILING(C$1-SUMPRODUCT(B$3:B3, C$3:C3), 0.01) / $B4))

    D4 and down: =A4-C4

    Row 3 is a helper and can be hidden.
    Last edited by shg; 03-07-2011 at 12:11 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,671

    Re: Extract correct denomination of bills, money counter, remove large bills,

    This is a little more rigorous:
    Please Login or Register  to view this content.

    F3: Input

    C6: =SUMPRODUCT(D9:D25, C9:C25)

    F6: =C6-F3

    G6: =SUMPRODUCT(C9:C25, G9:G25)

    C10 and down: Input

    D10 and down: Input

    F10 and down: =MIN(D10, INT(ROUND(F$6 - SUMPRODUCT(C$9:C9, F$9:F9), 2) / $C10))

    G10 and down: =D10-F10
    Last edited by shg; 03-07-2011 at 02:12 PM.

  5. #5
    Registered User
    Join Date
    02-28-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Extract correct denomination of bills, money counter, remove large bills,

    150


    2929.94 2779.94 0

    hand remove remain
    helper row 0
    bill 100 6 #VALUE!
    tens 100 10
    fives 100 10
    bill 50 3
    bill 20 1
    bill 10 3
    QR 10 4
    Bill 5 5
    DR 5 8
    NR 2 8
    Bill 1 3
    PR 0.5 8
    qaurter 0.25 4
    Dimes 0.1 4
    Nickel 0.05 9
    Pennies 0.01 9

  6. #6
    Registered User
    Join Date
    02-28-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Extract correct denomination of bills, money counter, remove large bills,

    Something is wrong in cell F10 and down...I get a #value ofr MIN (D10, int(....))

  7. #7
    Registered User
    Join Date
    02-28-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Extract correct denomination of bills, money counter, remove large bills,


  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,671

    Re: Extract correct denomination of bills, money counter, remove large bills,


  9. #9
    Registered User
    Join Date
    02-28-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Extract correct denomination of bills, money counter, remove large bills,

    Thanks...I'm going to try it out..
    .Again, thanks a lot !

  10. #10
    Registered User
    Join Date
    02-28-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Extract correct denomination of bills, money counter, remove large bills,

    It does not quiet work. It is removing money that is not there !
    Thanks
    Rama
    Attached Files Attached Files

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,671

    Re: Extract correct denomination of bills, money counter, remove large bills,

    Change the blanks in the Qty on Hand column to zeros.

  12. #12
    Registered User
    Join Date
    02-28-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Extract correct denomination of bills, money counter, remove large bills,

    Thanks again..What does the helper row do?

  13. #13
    Registered User
    Join Date
    02-28-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Extract correct denomination of bills, money counter, remove large bills,

    Thanks for the help in the money denomination.
    Now, I am trying to hide the sheets with only the right user having access to see (hide and unhide) their sheet. The manger should have access to the entire workbook. All with user name and password protection.
    I do not want anyone to be able to see the code. Can you please help me that. I really appreciate it.
    See attached file...Total deposits to be viewed by Manager/Admin only. Niraj can only see and work on his sheet, Umer can only see and work on his sheet etc
    Can the passwords be encrypted?
    Thanks for all your help
    Attached Files Attached Files

  14. #14
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,671

    Re: Extract correct denomination of bills, money counter, remove large bills,

    Please start a new thread. This question is completely unrelated to the original question.

    Thanks.
    Last edited by shg; 03-24-2011 at 11:38 AM.

  15. #15
    Registered User
    Join Date
    02-28-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Extract correct denomination of bills, money counter, remove large bills,

    Can somebody please look at my file. I am not too familiar with VB and I am not sure it is working quite right. I need usernames and passwords, whereby only the admin/manager has access to the entire sheet. The others should only have accessto view/work on their sheets. On save the sheet should hide. On open it should show an empty sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1