Note Counter for prize money calculations

[Pljudd]

Hi Everyone,

As a long term user of Excel, I have been asked to see if I can design a spreadsheet for my local Performing Arts Contest.

Each class of the competition has prize monies using $50, $20, $10 and $5 notes, with various amounts according to the class, and 1st, 2nd or 3rd class prizes awarded.

I have used the QUOTIENT and SUMPRODUCT formula as per the excellent video

Mr Excel & excelisfun Trick 30: Count Bills for Money Transaction

This works great for single amounts where you need to work out the notes required from a single amount, but it doesn't take into consideration if you have more than one amount for a class in the competition. I need to count all of the notes required across that class to that each prize category is filled with the correct amount.

A sample of my sandpit spreadsheet is attached.

Thanks in advance

Peter

​

 

[Legacy 143009]

Interesting question..

How to share if only 1x50$ and 3x20$? Like 50, 40, 20?

 

[Pljudd]

The amounts can be from $5 to $100 and are placed in packets to awarded to the prize winners, hence the need to calculate how many notes are needed and for which prize category.

Peter