Trying to calculate percentages on different bands.

[dog biscuit]

Hi, I'm slowly going crazy here! What I thought would be a simple task is turning out to be anything but....for me at least!

All I want to do is to be able to input a number and then split it into bands:
0 to 1100
1100 to 1500
1500 to 1700
1700 and over.
The figure can be any value and can be less than 1500 say.

If the figure was 1750 I want to get
1100
400
200
50
for example.

Any help gratefully appreciated. I am sorry if this has been covered before but I feeling rather stupid and have my husband breathing down my neck - I told him it would take me 2 minutes!

Many thanks.

 

[dog biscuit]

I am using Excel 2007.

 

[barry houdini]

Let's say you have your figure to split in B1.

In A3 down put the lower bound of each range, so in your example that would be 0 in A3, 1100, in A4, 1500 in A5 and 1700 in A6, leave A7 blank.

Then in B3 copied down to B6 use this formula

=IF((A4<>"")*(B$1>A4),A4-A3,MAX(0,B$1-A3))

 

[Scott R]

1750 in C1 for your test value.

0 in A3. 1100 in B3.
1100 in A4. 1500 in B4.
1500 in A5. 1700 in B5.
1700 in A6. 9999999 in B6.

Formula in C3 would be =MAX(0,MIN(B3,C$1)-A3). Copy down as necessary.