August Challenge of the Month Discussion

[MrExcel]

What an excellent discussion! I would guess my vote is to award IOANNIS and Sharad Kothari as co-winners for the August challenge and to send a message board CD to them both.

There were some private entries that I need to review and I will post anything new from those entries here in the next day.

While I do that, does anyone have an idea for a September Challenge of the Month?

Bill

 

[IOANNIS]

An idea for a September Challenge of the Month

When the Euro become the currency of most European Countries (as my, Greece), one problem occurs when my company wants to pay parttime Employees in cash two times a month.

The salaries was exact amounts, 150000 grd 140000 grd etc ..

150000= 15 x 10000 (banknote of 10000 grd)
140000= 14 x 10000 (banknote of 10000 grd)

But in Euro not

150000 grd=440,21 Euro
140000 grd=410,86 Euro

440,21 = 8x50 + 2x20 +1x0,20 + 1x0,01
410,86 = 8x50 + 1x10 +1x1 +1x0,50 + 1x0,20 + 1x0,10 +1x0,05 + 1x0,01

(if we convert them to dollars the problem is the some)

The Company wants to enclose to an envelope the salary with the minimum banknotes and coins

Euro banknotes

1x500 euro
1x200 euro
1x100 euro
1x500 euro
1x20 euro
1x10 euro
1x5 euro

Coins

1x5 euro
1x2 euro
1x1 euro
1x0,50 cents
1x0,20 cents
1x0,10 cents
1x0,05 cents
1x0,02 cents
1x0,01 cents

The problems are 3 :

1:If we don't have any banknotes and coins, how many banknotes and coins must get it from the Bank to make the pays ?

2:if we have some of them how many envelopes we can make ?

3:If we want not to pay with banknotes of 500 or (and) 200 etc ? or the BANK do not have any banknotes of 500 or (and) 200 etc ?

The salaries are change every 15 days since they are parttime

We want to make a macro too split the salaries to minimum banknotes and coins and solve the three problems above .(Use random salaries, use local currency)



(sorry if my english is not so good)

 

[Juan Pablo González]

I used a Solver model for this:

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Salary	500	200	100	50	20	10	5	5	2	1	0.5	0.2	0.1	0.05	0.02	0.01	Difference	Totalbanknotes	Totalcoins	Total
2	440.21	0	2	0	0	2	0	0	0	0	0	0	1	0	0	0	1	0	4	2	6
3	410.86	0	2	0	0	0	1	0	0	0	0	1	1	1	1	1	0	0	3	5	8
Hoja1

I solved each row at a time, but, that can be changed. Solver model:

Target cell: U2
Minimum cell value

Changing cells: B2:Q2
Restrictions

B2:Q2 = Integer
R2 = 0

And in "Options", I checked "Assume non negatives". Using more restrictions you can answer all your questions...

 

[IOANNIS]

Thanks Juan
I have also solved it without solver

Do you intersting to show you a screen shot ?



Regards

 

[Juan Pablo González]

Sure ! learning is always welcome...

 

[IOANNIS]

A JavaScript error occur when i try to post the screen shot in testing forum, too much code i think ...

 

[IOANNIS]

I change a little bit my macro and the speed now is 69.443 per hour, 10 hours & 43 minites

As i said before the macro does not use any Excel's features and is easy to tranfered to another programming language sush as c++ for optimum speed.
Since I have not a c++ compiler but an old fashion Quick basic Advanced Compiler Version 7.00 (edition 1989 !!!), i copy-paste the code, make the proper changes and after a few minutes the brute.exe was ready to run.

THE RESULTS:
Total time 3 hours & 39 minites
Final speed 204.000 per hour

If an old basic compiler (1989)is so fast what about an c++ (2002) ?

(Remarks for WIN XP users for speeding up their computer:
..go to System Properties =>Advanced => Performance Settings and check the "adjust for best performance", thats was when i said that "something has my computer at home", it speed ups my macro up to 50%)

 

[DUTCHMAN]

Help

Hi ,

I'm working in the bookkeeping and i have this problem to solve every day.

Can some body explaine how to put this macro in an excel file, or send me a excel ark with the macro in place.

 

[IOANNIS]

..Comments ........

The macro on the http://www.mrexcel.com/pc09.shtml dont work without the original worksheet ......

If someonne wants to run the macro just email me to sent him the original workbook

.......

 

[opeyemi1]

..Comments ........

The macro on the http://www.mrexcel.com/pc09.shtml dont work without the original worksheet ......

If someonne wants to run the macro just email me to sent him the original workbook

.......

Helloooo,

Could you please provide me with workbook you mentioned so I can see if the macro will work for me, my email is: opeyemi1*removethispart*@hotmail.com

Also, wondering if you have any inputs in the following:


I have 2 spreadsheets, and they are not linked together by formulas. One of them has various lines (an income statement) that sum different lines from the other spreadsheet, e.g.

Sales = 200100
Expenses = -300345
Other Income = 120000
Selling and Admin = 35000

The other spreadsheet has a lot of details (a trial balance) that make up this first spreadsheet.

e.g.

Paper 100000
Cardboard 50100
bottles 30000
cups 20000

These add up to the sales number for example.

Is there a way that I can use the macro or something similar to get all the cells that relate to the first spreadsheet in one shot, and so I don't have to first find what the sales number is made up of, and then what the expenses number is made up of..can I look for the relating cells to all the items in the 1st spreadsheet in one go. Is there a code or something that I can use.

The 2nd spreadsheet has a lot of rows, and it's quite difficult to manually analyze. I tried using the solver in excel, but I find that when I look for the sum of the sales number and try looking for the expenses number, it gives me cells that are related to the sales number as well.

Thanks for your help!!!