Formula to auto fill denominations of money

[skmerrey]

Hi Everyone,

I'm working on a salary spreadsheet for my wife's new job, once a month she has to add up the salary's and then calculate the denominations of money she needs,so she can get the exact amount per employee from the bank. I have attached a sample sheet, to show what I need. Column A has the employee name, column B has there wages which she copies from another sheet, then C-G has the number of cash notes she requires for each person, and then a sum at the bottom to tell her what she needs. at the minute she counts and inputs this number manually, can anyone come up with a formula that would count the denominations based on each wage automatically.

Thanks for any help on this.
Best Regards
Shaun

 

[Chihiro]

Something like attached. Note that Col C has different formula compared to rest.

 

[bosco_yip]

Try this one formula way,

C8, enter array formula ( Confirm enter with SHIFT+CTRL+ENTER instead of just ENTER) copy across and down :

=INT(($B8-SUM(IFERROR(--$B$7:B$7,0)*$B8:B8))/C$7)

Regards
Bosco

 

[skmerrey]

Perfect, that was quick lol.

Thanks Chihiro

 

[skmerrey]

Thanks Bosco, that works great too