Sterling breakdown

[wiggo]

Hi

Could anyone point me in the right direction please?

I am after a spreadsheet that will work out the cash denominations for our staff.

Ideally all I would need to do is enter the amount they are due, and then the sheet would work out how many £20 notes are needed and so on.

If anyone could help me, I would appreciate it.

Regards

Wiggo

 

[adam087]

Try this

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Wage	20	10	5	1	0.50	0.20	0.10	0.05	0.02	0.01
2	Name1	806.57	40	0	1	1	1	0	0	1	1	0	806.57
3	Name2	4,262.59	213	0	0	2	1	0	0	1	1	1	4262.58
4	Name3	3,192.33	159	1	0	2	0	1	1	0	1	1	3192.33
5	Name4	3,553.92	177	1	0	3	1	2	0	0	0	1	3553.91
6	Name5	4,951.08	247	1	0	1	0	0	0	1	1	0	4951.07
Sheet1

Excel 2002

Cell Formulas
Range	Formula
C2	=ROUNDDOWN($B2/C$1, 0)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[wiggo]

Try this

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Wage	20	10	5	1	0.50	0.20	0.10	0.05	0.02	0.01
2	Name1	806.57	40	0	1	1	1	0	0	1	1	0	806.57
3	Name2	4,262.59	213	0	0	2	1	0	0	1	1	1	4262.58
4	Name3	3,192.33	159	1	0	2	0	1	1	0	1	1	3192.33
5	Name4	3,553.92	177	1	0	3	1	2	0	0	0	1	3553.91
6	Name5	4,951.08	247	1	0	1	0	0	0	1	1	0	4951.07
Sheet1

Excel 2002

Cell Formulas
Range	Formula
C2	=ROUNDDOWN($B2/C$1, 0)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

Thanks for the quick response Adam.

I am a bit of a newbie to formulas like the ones you've posted.

So with than in mind, do you think you could explain to me what you mean by 'Ctrl+Shift+Enter' please? Is it a way of pasting the formulas outlined into the sheet?

Thanks

Wiggo

 

[adam087]

Yeah. Just means that when you've typed/copied in the formula, instead of presing just Enter to confirm as your normally would, hold down CTRL+SHIFT as you press it.

This will automatically add the {} brackets which you shouldn't type yourself. You only need to enter the formulae into C2 & D2 then you can drag the formula from D2 across; and then down. M2 was just a checker column.

Regards
Adam

 

[wiggo]

Yeah. Just means that when you've typed/copied in the formula, instead of presing just Enter to confirm as your normally would, hold down CTRL+SHIFT as you press it.

This will automatically add the {} brackets which you shouldn't type yourself. You only need to enter the formulae into C2 & D2 then you can drag the formula from D2 across; and then down. M2 was just a checker column.

Regards
Adam

Fantastic

I've got it working.

Thanks again Adam.

Wiggo

 

[adam087]

No worries.