Excel - Way to calculate paying salaries


I work at a restaurant chain with about 170 employees. Currently we pay most of them by cash, and our administration team counts 'what bank notes' to pay them manually.

So for example if one of our waiters gets paid 2152 Euro, then the function should calculate the following:

100 Euro * 21
50 Euro * 1
2 Euro * 1 

Anything like that in excel?

Show source
| function   | excel   2017-01-06 09:01 2 Answers

Answers ( 2 )

  1. 2017-01-06 09:01

    Perhaps you can try to use Truncate & Divide to calculate number of notes for each given note.

    for example, to get number of 100 note:

    NumberOf100Note = Trunc(givenAmount/100,0)

    NumberOf50Note = Trunc((givenAmount - 100 * NumberOf100Note) / 50, 0)

    etc. and etc.

    and you can build these very easily in excel, isn't it?

  2. 2017-01-06 10:01

    This is just one way to do it. I chose to use a helper column to keep the formulas simple for you

    Here is how it looks:
    cash example

    This formula goes in B3 and you should drag it down. This column shows the remainder due after each note. E.g. 52€ remaining after 2100€ in 100€ notes has been accounted for)


    Then place this formula in C3 and drag it down


    That will give you how many of each note to dispense to the worker.

    This method simply assumes you want to issue the fewest notes. It would be a good idea to have a table which allows you to indicate if you are out of a particular note so that the model uses 2x€50 notes rather than 100€ notes, for example.

◀ Go back