Subtract n from min value and then remainder of n from max value

[MrSheen]

As the title says, I want to subtract n from min value first and then the remainder of n from the max value. I thought I had it cracked until one of the values exceeds n.

My formula is =IF(C4=D4,C4-(B/2),IF(C4<D4,MIN(SUM(C4:D4)-B4,0),MAX(SUM(C4:D4)-B4,0))) for bot AA and BB.
<d4,min(sum(c4:d4)-b4,),max(sum(c4:d4)-b4,0)))

The expected result in E4 (AA) should be 100 and F4 (BB) should be 69 but as you can see it falls over. Can anyone make a suggestion?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD](A1)[/TD]
[TD](B1) n
[/TD]
[TD](C1) a
[/TD]
[TD](D1) b
[/TD]
[TD](E1) aa
[/TD]
[TD](F1) bb
[/TD]
[/TR]
[TR]
[TD](A2)
[/TD]
[TD]30
[/TD]
[TD]20
[/TD]
[TD]25
[/TD]
[TD]0
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD](A3
[/TD]
[TD]30
[/TD]
[TD]17
[/TD]
[TD]14
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD](A4)
[/TD]
[TD]30
[/TD]
[TD]100
[/TD]
[TD]99
[/TD]
[TD]169 (Exp 100)
[/TD]
[TD]0 (Exp 69)
[/TD]
[/TR]
</tbody>[/TABLE]
</d4,min(sum(c4:d4)-b4,),max(sum(c4:d4)-b4,0)))

 

[AlanY]

you ask for Subtract n from min value and then remainder of n from max value

what is the range for min value? Cols B, C & D?
same question for max value? what is the range for the max?

and how do you get to the 69 in F4

 

[MrSheen]

So, the min and max are in cells C & D, B is the value to subtract.

Looking at row 4.

C4 is the max and D4 is the min. It should subtract 'n' (30) from D4 first as it is the min value and equal 69 and as nothing is remaining in 'n', C4 remains at 100.

Does that make sense?

 

[AlanY]

right, try this

Excel 2013/2016
	A	B	C	D	E
1	(A1)	(B1) n	(C1) a	(D1) b
2	(A2)	30	20	25	15
3	(A3	30	17	14	1
4	(A4)	30	100	99	100
Sheet1

Cell Formulas
Range	Formula
E2	=MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0)

 

[MrSheen]

Sorry AlanY, that's not it. E4 should be 100 and F4 69.

Sorry, just realised my formula had been clipped in post 1. E4 and F4 have =IF(C4=D4,C4-(B4/2),IF(C4<D4,MIN(SUM(C4:4)-B,0),MAX(SUM(C4:D4)-B4,0)))

 

[AlanY]

E4 is 100! is that what you're after for F4?

Excel 2013/2016
	A	B	C	D	E	F
1	(A1)	(B1) n	(C1) a	(D1) b
2	(A2)	30	20	25	15	-10
3	(A3	30	17	14	1	-16
4	(A4)	30	100	99	100	69
Sheet1

Cell Formulas
Range	Formula
E2	=MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0)
F2	=MIN(C2:D2)-B2

 

[MrSheen]

Sorry AlanY, just realised my original post was clipped in the formula. My less than keeps getting clipped.

Formula in E4 and F4 is

=(C4=D4,C4-(B4/2),IF(C4 < D4,MIN(SUM(C4:D4)-B4,0),MAX(SUM(C4:D4)-B4,0)))

<d4,min(sum(c4:d4)-b4,0),max(sum(c4:d4)-b4,0)))[ code]

The idea is, it subtracts B4 from the lowest of the 2 values in C4 or D4 and if anything remains of B4, subtract is from the max in C4 or D4 and both reduce to a minimum of zero.</d4,min(sum(c4:d4)-b4,0),max(sum(c4:d4)-b4,0)))[>

 

[AlanY]

you need to put a space before and after the <, > etc for them to show up properly

 

[MrSheen]

Thanks, edited previous reply.

 

[AlanY]

Sorry AlanY, just realised my original post was clipped in the formula. My less than keeps getting clipped.

Formula in E4 and F4 is

=(C4=D4,C4-(B4/2),IF(C4 < D4,MIN(SUM(C4:D4)-B4,0),MAX(SUM(C4:D4)-B4,0)))

<d4,min(sum(c4:d4)-b4,0),max(sum(c4:d4)-b4,0)))[ code]

The idea is, it subtracts B4 from the lowest of the 2 values in C4 or D4 and if anything remains of B4, subtract is from the max in C4 or D4 and both reduce to a minimum of zero.</d4,min(sum(c4:d4)-b4,0),max(sum(c4:d4)-b4,0)))[>

but, isn't that's exactly the formula in Col E doing?

=MAX(C4:D4)+MIN(MIN(C4:D4)-B4,0)