If statement for numbers within specific ranges

[Woofy_McWoof_Woof]

Hi I am trying to calculate the results for specific ranges of data, for instance in the example below I would like to split the 15 result into the appropriate bands so that 5 is shared equally amongst the three bands however for 8.5 it will only appear in the first two bands and split accordingly.

I tried to use the formula below but all it does is put the total number into the cell rather than the number according to the range. Any help would be appreciated and if its possible to capture the result in one formula (just have to change the band range) then i could copy that formula into all the cells rather than having three separate formulas.

Cheers

15
<=5=IF(AND(A2>=5,A2<=10),A2,0)
>5<=10=IF(AND(A2>=5,A2<=10),A2,0)
>10=IF(A2>10,A2,0)

Result	<=5	>5<=10	>10
15	5	5	5
8.5	5	3.5	0

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E
1	Result		5	10	9999
2	15		5	5	5
3	8.5		5	3.5	0
Sheet1

Cell Formulas
Range	Formula
C2:E3	C2	=MIN($A2-SUM($B2:B2),C$1-B$1)

I changed the headings, and the empty B column is required. But we can tweak it if you want.

 

[Woofy_McWoof_Woof]

Perfect, thank you

 

[Eric W]

Glad to help.