Formula to split a value over 3 columns based on conditions

[ScotIain76]

Good morning

I would appreciate any advice on how I can create a formula to split a value up into 3 separate columns.

I want to calculate the amounts due as I need to pay the first 10 metres @£10, the next 90 metres @ £10 and the remaining (if any) @ £1 with the maximum anyone can receive being £3000

Some values are <10m, <100m etc.

Hopefully my attached example makes sense

 

[hrayani]

I am sure there must be an easier way of doing it but this is what I have come up with

see if this helps provided I have understood your requirements correctly

Book5
	A	B	C	D
1	Meters	10 @10	90 @75	Remaining M @ 1
2	100.18	100	6,750	0
3	1515.9	100	6,750	1,416
4	27508.27	100	6,750	3,000
5	100	100	6,750	-
6	2.13	21		-
7	0	-		-
8	28.13	100	1,360	-
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=IF(A2<=10,A2*10,10*10)
C2:C8	C2	=IF(A2-10<=0,"",IF(A2-10>90,90*75,(A2-10)*75))
D2:D8	D2	=IF(A2-100>3000,3000,IF(A2-100<0,0,A2-100))

 

[Dave Patton]

Why do you want the calculation over 3 columns?
Which rates are correct? Your narrative rates are different from the rates in the image.
N.B. You can post an extract of your sheet with the forum's tool XL2BB.
For just the result, use the SumProduct or arithmetic.

T202110b.xlsm
	E	F	G	H	I	J	K	L
2		1-10	11-100		Total		0	75
3	Meters	75	10	1			10	10
4	100.18	750	900	0.18	1,650.18	1,650.18	100	1
5	1515.9	750	900	1350	3,000.00	3,000.00
6	27508.27	750	900	1350	3,000.00	3,000.00
1e

Cell Formulas
Range	Formula
F4:F6	F4	=MIN(10,E4)*$F$3
G4:G6	G4	=(E4>10)*MIN(E4-10,100-10)*$G$3
H4:H6	H4	=(E4>100)*MIN(3000-SUM(F4:G4),(E4-100)*$H$3)
I4:I6	I4	=SUM(F4:H4)
J4:J6	J4	=MIN(3000,SUMPRODUCT(--(E4>$K$2:$K$4),E4-$K$2:$K$4,$L$2:$L$4-$L$1:$L$3))

 

[Dave Patton]

F4 does not require the Table of brackets and rates.
The Bracket data and Rate Differentials are named; see Formulas Name Manager.
Brackets are named aB Refers To: ={0;10;100}
Rate Differentials are named aR Refers To: ={75;-65;-9}

T202110b.xlsm
	E	F
1
2
3	Meters	Payment
4	100.18	1,650.18
5	1,515.90	3,000.00
6	27,508.27	3,000.00
7	1,450.00	3,000.00
8	6.00	450.00
1e

Cell Formulas
Range	Formula
F4:F8	F4	=MIN(3000,SUMPRODUCT(--(E4>aB),E4-aB,aR))