Tip Sheet with the "Need change or not?" function

[nutstae]

Hi guys

I am trying to make a tip sheet with a function that tells you whether I need a change or not.

If I enter how much we have for each bills($1bill, $5bill, etc), is it possible for an excel to tell me whether I have enough small bills to be able to distribute all cash to given number of employees?

For ex. if we have only $100.00 bills, it's no brainer that we need a change, but if we have mixture of everything, sometimes it's hard to know in the beginning. it'd be great if it's possible for an excel to tell you which bills to give each employee exactly.

Thank you!

[Kaper]

Althought general idea seems to be clear, some details shall be clarified, like:
* equal division or some groups or other "shares scheme"?
* bills only? So if there is $105.00 and 2 employees what amount shall they receive?
* standard bills only (or rare $2 too?)

Probably best way is by example workbook with few sample cases "processed manually", so let me paste one of our canned replies:
---
Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[nutstae]

Hi,

Please see the attached final version of tip sheet I made.
I kinda made it in a way that everyone will understand what to do.
I want to create a section where it would show whether I would need a change or not based on the information I have entered on the chart I made. Could you take a look at it please?

Thank you!

[shg]

Here's one I made a few years ago ...

	B	C	D	E	F	G	H	I	J	K	L	M
2		Denom >>	$ -	$ 100	$ 50	$ 20	$ 10	$ 5	$ 1	Total		D2:J2: Input
3	Name	Amt	Bills on Hand >>	4	6	36	18	12	40	$ 1,700		E3:J3: Input
4			-	-	-	-	-	-	-			K3 and down: =SUMPRODUCT($E$2:$J$2, $E3:$J3)
5	Alan	$ 171	-	1	1	1	-	-	1	$ 171		D4:J4,D5:D30: =0
6	Barb	$ 193	-	1	1	2	-	-	3	$ 193		E5 and across and down: =MIN(INT(($C5 - SUMPRODUCT($D$2:D$2, $D5:D5)) / E$2), E$3 - SUM(E$4:E4))
7	Cain	$ 36	-	-	-	1	1	1	1	$ 36
8	Dana	$ 40	-	-	-	2	-	-	-	$ 40
9	Eric	$ 34	-	-	-	1	1	-	4	$ 34
10	Fran	$ 39	-	-	-	1	1	1	4	$ 39
11	Gary	$ 368	-	2	3	-	1	1	3	$ 368
12	Hana	$ 94	-	-	1	2	-	-	4	$ 94
13	Ivan	$ 61	-	-	-	3	-	-	1	$ 61
14	Jane	$ 48	-	-	-	2	-	1	3	$ 48
15	Kent	$ 82	-	-	-	4	-	-	2	$ 82
16	Leah	$ 207	-	-	-	10	-	1	2	$ 207
17	Mark	$ 18	-	-	-	-	1	1	3	$ 18
18	Nina	$ 133	-	-	-	6	1	-	3	$ 133
19	Otto	$ 115	-	-	-	1	9	1	-	$ 115
20	Peri	$ 19	-	-	-	-	1	1	4	$ 19
21	Quin	$ 31	-	-	-	-	2	2	1	$ 31
22	Rene	$ 11	-	-	-	-	-	2	1	$ 11
23	Total	$ 1,700	Bills used >>	4	6	36	18	12	40	$ 1,700
24			Bills left >>	-	-	-	-	-	-	$ -

[shg]

Here's what it looks like if it can't complete ...

	B	C	D	E	F	G	H	I	J	K
2		Denom >>	$ -	$ 100	$ 50	$ 20	$ 10	$ 5	$ 1	Total
3	Name	Amt	Bills on Hand >>	4	6	36	18	12	36	$ 1,696
4			-	-	-	-	-	-	-
5	Alan	$ 77	-	-	1	1	-	1	2	$ 77
6	Barb	$ 62	-	-	1	-	1	-	2	$ 62
7	Cain	$ 123	-	1	-	1	-	-	3	$ 123
8	Dana	$ 99	-	-	1	2	-	1	4	$ 99
9	Eric	$ 14	-	-	-	-	1	-	4	$ 14
10	Fran	$ 51	-	-	1	-	-	-	1	$ 51
11	Gary	$ 139	-	1	-	1	1	1	4	$ 139
12	Hana	$ 10	-	-	-	-	1	-	-	$ 10
13	Ivan	$ 66	-	-	1	-	1	1	1	$ 66
14	Jane	$ 19	-	-	-	-	1	1	4	$ 19
15	Kent	$ 91	-	-	1	2	-	-	1	$ 91
16	Leah	$ 263	-	2	-	3	-	-	3	$ 263
17	Mark	$ 29	-	-	-	1	-	1	4	$ 29
18	Nina	$ 33	-	-	-	1	1	-	3	$ 33
19	Otto	$ 55	-	-	-	2	1	1	-	$ 55
20	Peri	$ 69	-	-	-	3	-	1	-	$ 65
21	Quin	$ 426	-	-	-	19	4	1	-	$ 425
22	Rene	$ 70	-	-	-	-	6	2	-	$ 70
23	Total	$ 1,696	Bills used >>	4	6	36	18	11	36	$ 1,691
24			Bills left >>	-	-	-	-	1	-	$ 5

[nutstae]

Thank you but what formula are you using for E5?? I get value error when I used your formula accordingly to mine...

[shg]

Look in cell M6 in post #4.

[nutstae]

Yes, I used the formula as it as, only changed the cells to fit the chart I have, but it's giving me #value! error.. Screen Shot 2017-10-21 at 4.27.03 PM.jpgScreen Shot 2017-10-21 at 4.27.03 PM.jpg

[shg]

Here's the workbook.