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)))

 

[MrSheen]

Hi AlanY,

Sorry, my brain is switched off, I blame the birth of my twin boys!

Solution in the end was this.

E2 =IF(IF(C2=D2,C2-(B2/2),IF(C2 < D2,MIN(C2:D2)-B2,MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0))) < 0,0,IF(C2=D2,C2-(B2/2),IF(C2 < D2,MIN(C2:D2)-B2,MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0))))
F2 =

IF(IF(C2=D2,C2-(B2/2),IF(D2 < C2,MIN(C2:D2)-B2,MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0))) < 0,0,IF(C2=D2,C2-(B2/2),IF(D2 < C2,MIN(C2:D2)-B2,MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0))))​

 

[AlanY]

Hi AlanY,

Sorry, my brain is switched off, I blame the birth of my twin boys!

Solution in the end was this.

E2 =IF(IF(C2=D2,C2-(B2/2),IF(C2 < D2,MIN(C2:D2)-B2,MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0))) < 0,0,IF(C2=D2,C2-(B2/2),IF(C2 < D2,MIN(C2:D2)-B2,MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0))))
F2 =

IF(IF(C2=D2,C2-(B2/2),IF(D2 < C2,MIN(C2:D2)-B2,MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0))) < 0,0,IF(C2=D2,C2-(B2/2),IF(D2 < C2,MIN(C2:D2)-B2,MAX(C2:D2)+MIN(MIN(C2:D2)-B2,0))))​

your formula in E2 yielded 0, 30 take away the min of (20, 25) equal to 10, and minus by the max of (20, 25). shouldn't that equal to 15?
i'm still not sure the criteria for F2 etc

Excel 2013/2016
	A	B	C	D	E	F
1		n	a	b	aa	bb
2		30	20	25	0	15
3		30	17	14	1	0
4		30	100	99	100	69
Sheet1

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