CODEWITHSUNDEEP
excelfunction
Pbalazs89
Pbalazs89

Reputation: 168

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?

Upvotes: 1

Views: 278

Answers (2)

CallumDA
CallumDA

Reputation: 12113

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) 

=MOD(B2,A3)

Then place this formula in C3 and drag it down

=IF(B2<>B3,(B2-B3)/A3,0)

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

Note
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.

Upvotes: 3

Rex
Rex

Reputation: 2140

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?

Upvotes: 0

Related Questions