Hi,

I am trying to break down a given currency amount into the relevant Notes and Coins to make up the amount. The Currency is British Pound (£20, £10, £5, £2, £1, £0.50, £0.20, £0.10, £0.05, £0.02, £0.01).

My sheet is setup with the amount running down the left (A2 - A20) and the different Notes/Coins are across the top B1- L1.

I have tried the follow formula, but it's not quite working out:

In B2 i have

=INT(A2/B$1)

=INT(($A2-SUMPRODUCT(($B$1:B$1)*($B2:B2)))/C$1)

This is copied into the remaining cells to the left.

I'm not sure if I'm calculating the Pounds and the Pence correctly, if I enter £41.40 it calculates:

£20 = 1

£10 = 0

£5 = 0

£2 = 0

£1 = 1

0.50 = 0

0.20 = 1

0.10 = 1

0.05 = 1

0.02 = 2

0.01 = 1

Which isn't quite right.

Can anyone see where I going wrong in my formulas?

Thanks,

attached is the excel file with formulas

Book1exch.xlsx