If formula

[Johnboy28]

Hi,

I'm require a formula to deduct available from total required. If the value is greater than Option 2, then it will equal the value of Option 2?

Thanks for any help.



[TABLE="width: 466"]
<tbody>[TR]
[TD="width: 136, bgcolor: transparent"]Total Required
[/TD]
[TD="width: 136, bgcolor: transparent"]Available
[/TD]
[TD="width: 106, bgcolor: transparent"]Option 2
[/TD]
[TD="width: 93, bgcolor: transparent"]Total Sum
[/TD]
[TD="width: 149, bgcolor: transparent"]Answer
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]293
[/TD]
[TD="bgcolor: #FCD5B4"]85
[/TD]
[TD="bgcolor: transparent"]164
[/TD]
[TD="bgcolor: #E6B9B8"]??
[/TD]
[TD="bgcolor: transparent"]164
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]293
[/TD]
[TD="bgcolor: #FCD5B4"]157
[/TD]
[TD="bgcolor: transparent"]218
[/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: transparent"]136
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]329
[/TD]
[TD="bgcolor: #FCD5B4"]109
[/TD]
[TD="bgcolor: transparent"]183
[/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: transparent"]183
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]329
[/TD]
[TD="bgcolor: #FCD5B4"]111
[/TD]
[TD="bgcolor: transparent"]99
[/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: transparent"]99
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]384
[/TD]
[TD="bgcolor: #FCD5B4"]100
[/TD]
[TD="bgcolor: transparent"]44
[/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: transparent"]44
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]366
[/TD]
[TD="bgcolor: #FCD5B4"]111
[/TD]
[TD="bgcolor: transparent"]115
[/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: transparent"]115
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]329
[/TD]
[TD="bgcolor: #FCD5B4"]117
[/TD]
[TD="bgcolor: transparent"]54
[/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: transparent"]54
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]366
[/TD]
[TD="bgcolor: #FCD5B4"]132
[/TD]
[TD="bgcolor: transparent"]71
[/TD]
[TD="bgcolor: #E6B9B8"][/TD]
[TD="bgcolor: transparent"]71
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Excel 2013 32 bit
	A	B	C	D	E
1	Total Required	Available	Option 2	Total Sum	Answer
2	293	85	164	164	164
3	293	157	218	136	136
4	329	109	183	183	183
5	329	111	99	99	99
6	384	100	44	44	44
7	366	111	115	115	115
8	329	117	54	54	54
9	366	132	71	71	71
Week38

Cell Formulas
Range	Formula
D2	=IF(A2-B2>C2,C2,A2-B2)

 

[Johnboy28]

Thanks for your help. That works great.

 

[Fluff]

Glad to help & thanks for the feedback

 

[jtakw]

Hi,

You can also do this:

Book1
	A	B	C	D
1	Total Required	Available	Option 2	Total Sum
2	293	85	164	164
3	293	157	218	136
4	329	109	183	183
5	329	111	99	99
6	384	100	44	44
7	366	111	115	115
8	329	117	54	54
9	366	132	71	71
Sheet224

Cell Formulas
Range	Formula
D2	=MIN(A2-B2,C2)

 

[Johnboy28]

Thank you. Will try this.

 

[jtakw]

You're welcome.