MAX Factor

M

MarkDShark

Board Regular
Joined
Jun 10, 2008
Messages
228
Hi, i need to know where in this formula can i add the MAX(0, factor so that this formula will show 0 when the result in the target cell is a negative figure. Thanks for your help on this.

=IF((SMALL(G17:G21,1)=G17),E7-(E13+E14+E15+E16+E17),IF((SMALL(G17:G21,1)=G18),E10-(E9+E13+E14+E15+E16+E17),IF((SMALL(G17:G21,1)=G19),E10-(E9+E13+E14+E15+E16+E17),IF((SMALL(G17:G21,1)=G20),E10-(E9+E13+E14+E15+E16+E17),IF((SMALL(G17:G21,1)=G21),E10-(E9+E13+E14+E15+E16+E17),"")))))
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
I'm fairly sure you can replace your formula with this:
Code: 
=MAX(CHOOSE(MIN(MATCH(MIN(G17:G21),G17:G21,0),2),E7,E10-E9)-SUM(E13:E17),0)

Is that something you can work with?
 
Upvote 0
Hope your able to understand this code. I’m working on another spreadsheet and trying to reverse part of the code that is taking into consideration an amount under $100 into the target cell. What this code does, is determines which amount to pick up based on a $100 tax tolerance that was set. What I need it to do for this example, is for the code to consider the book 1 amount when E26 is ZERO like the example below. But to not be considered like the rest of the code is reading, basically when theres an amount over $100 in E26. I hope you understand what this code is set up for originally and are able to determine what I need to do. I assume a less than greater than sign just needs to be reverse in a certain part of the code, but I can’t figure which part. Thanks in advance!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
=IF((E26-F30>100)*(E26-F30>0)*(F30>0),F28-F31,IF((E26-F30>(100))*(F30>0),F30-F31,IF((E26-F30<100)*(E26-F30>0)*(F30>0),F30-F31,IF((E26-F30<(-100))*(F30>0),F28-F31,F28-F31))))
<o:p> </o:p>
<o:p> </o:p>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 384pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=512 border=0><TBODY><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 123pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=164><o:p> </o:p>
</TD><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 99pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=132><o:p> </o:p>
</TD><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 114pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=152><o:p> </o:p>
</TD></TR><TR style="HEIGHT: 17.25pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 17.25pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 2.25pt double; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 2.25pt double; PADDING-LEFT: 5.4pt; BACKGROUND: #333399; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 123pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 17.25pt" vAlign=bottom noWrap width=164>
​
</TD><TD style="BORDER-RIGHT: windowtext 2.25pt double; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 2.25pt double; PADDING-LEFT: 5.4pt; BACKGROUND: #ffffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 99pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 17.25pt" vAlign=bottom noWrap width=132 x:num="4.4569999999999999E-2">
​
</TD><TD style="BORDER-RIGHT: windowtext 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 2.25pt double; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 114pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 17.25pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: double windowtext 2.25pt" vAlign=bottom noWrap width=152 rowSpan=2>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 14.25pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
E26<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: #333399; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 123pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt" vAlign=bottom noWrap width=164>
Amount x ES Factor<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 2.25pt double; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: #ffffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 2.25pt double; WIDTH: 99pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt" vAlign=bottom noWrap width=132 x:num>
00<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 14.25pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: black 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 336pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=448 colSpan=3>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 14.25pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
F28<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: black 2.25pt double; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: #333399; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 222pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt; mso-border-top-alt: double windowtext 2.25pt" vAlign=bottom noWrap width=296 colSpan=2>
Calculated (If less than $100 =$0)<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: #ffffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 114pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt" vAlign=bottom noWrap width=152 x:num>
0<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 14.25pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: black 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 336pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: double windowtext 2.25pt" vAlign=bottom noWrap width=448 colSpan=3>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 14.25pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
F30<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: black 2.25pt double; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: #333399; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 222pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt; mso-border-top-alt: double windowtext 2.25pt" vAlign=bottom noWrap width=296 colSpan=2>
BOOK 1<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 114pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt" vAlign=bottom noWrap width=152 x:num>
92<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 14.25pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
F31<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: black 2.25pt double; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: #333399; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 222pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt; mso-border-top-alt: double windowtext 2.25pt" vAlign=bottom noWrap width=296 colSpan=2 x:str="BOOK 2 ">
BOOK 2 <o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 114pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt" vAlign=bottom noWrap width=152 x:num>
129.19<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 14.25pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
<o:p> </o:p>​
</TD><TD style="BORDER-RIGHT: black 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 336pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 2.25pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: double windowtext 2.25pt" vAlign=bottom noWrap width=448 colSpan=3>
<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 19.5pt; mso-yfti-irow: 9; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #cedce3; HEIGHT: 19.5pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=64>
TARGET<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: #333399; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 123pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1.5pt solid; HEIGHT: 19.5pt" vAlign=bottom noWrap width=164>
TAX<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: #cedce3; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: aqua; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 2.25pt double; WIDTH: 99pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1.5pt solid; HEIGHT: 19.5pt" vAlign=bottom noWrap width=132>
<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #cedce3; PADDING-LEFT: 5.4pt; BACKGROUND: aqua; PADDING-BOTTOM: 0in; BORDER-LEFT: #cedce3; WIDTH: 114pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1.5pt solid; HEIGHT: 19.5pt" vAlign=bottom noWrap width=152 x:num x:fmla="=IF((C3-D7>100)*(C3-D7>0)*(D7>0),D5-D8,IF((C3-D7<(100))*(D7>0),D7-D8,IF((C3-D7<100)*(C3-D7>0)*(D7>0),D7-D8,IF((C3-D7<(-100))*(D7>0),D5-D8,D5-D8))))">
-37.19<o:p></o:p>​
</TD></TR></TBODY></TABLE>
<o:p> </o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Help please - COUNTA error
Replies
1
Views
134
StephenCrump
S
M
  • Question Question
Parent/Child visual help
Replies
1
Views
330
Peter_SSs
Peter_SSs
T
  • Solved
Years visible only if there's a value in cell
Replies
6
Views
119
tigerzen
T
V
  • Solved
Copy only Value not Formula to new Cell location after ActiveX Macro button pushed.
Replies
2
Views
165
VinnyDoesntKnowExcelCode
V
C
  • Question Question
Only Brings Unique Value Across
Replies
3
Views
105
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,548
Messages
6,179,445
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top