Break a number down to smaller bands

[Chryso]

Hi to all!
Two days now I am trying to solve this puzzle so any suggestions/ideas you may have would be greatly appreciated!!
Here is the issue:

I have a series of different numbers that I need to break down into smaller ones (bands)
For example
Numbers
2.447
3.826
4.790
6.428
Need to be broken down to:
1st band = 1.000
2nd band = 1.500
3rd band = 1.500
4th band = 1.500
5th band = 1.500

The end result looks like this:

---

Bands 1.000 1.500 1.500 1.500 1.500

---

2.447 1.000 1.447 0 0 0
3.826 1.000 1.500 1.327 0 0
4.791 1.000 1.500 1.500 791 0
6.429 1.000 1.500 1.500 1.500 929


Thanks!
Chrisso

 

[konew1]

Enter the formula from B5 and copy down. Enter C5 and copy down and across

Excel Workbook
	A	B	C	D	E	F
3	*	1	1.5	1.5	1.5	1.5
4	*	band1	band2	band3	band4	band5
5	2.447	1	1.447	0	0	0
6	3.826	1	1.5	1.326	0	0
7	4.79	1	1.5	1.5	0.79	0
8	6.428	1	1.5	1.5	1.5	0.928
Sheet1

 

[Chryso]

Thank you so much!!!!

 

[konew1]

OOPs
The formula in column B will work, but it is poorly constructed.
=MIN(1,$A5) should be =MIN(B$3,$A5)
Should avoid putting constants into formula, especially if they also are stated on the sheet.

 

[Chryso]

Correct, thank you