Conditionals for Negative Integers and Zero

[KC2MF]

I need to create a formula that will add a condition if the value of a formula reaches anywhere from 0 below. In other words from 1 and above, one condition will be met and from 0 down another condition will be triggered. I kind of have another qualifier in but it creates negative numbers and 0 which I thought it would not do.

Thanks in advance for anyone who could help me with this though one.

 

[mikerickson]

It would help if we could get more details.
What value should be returned in what conditions?
Can you provide a few examples of input and desired output?

 

[KC2MF]

Sure thing,

Say were measuring group performance by measured output against other Team groups we measure them then by their the days that they've won and the days that they haven't won. Much like a season, eventually team groups will be eliminated from contention from a "playoff" spot. Should this number go negative or reach 0 then that team group is out of contention. Here are some of the standings . . .

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">37</td><td style="font-weight: bold;;">Team Group</td><td style="font-weight: bold;;">W</td><td style="font-weight: bold;;">L</td><td style="font-weight: bold;;">T</td><td style="font-weight: bold;;">DP</td><td style="font-weight: bold;;">DL</td><td style="font-weight: bold;;">Win %</td><td style="font-weight: bold;;">D's Back</td><td style="font-weight: bold;;">Magic #</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style=";">Cherry Hill, NJ</td><td style="text-align: right;;">81</td><td style="text-align: right;;">43</td><td style="text-align: right;;">0</td><td style="text-align: right;;">124</td><td style="text-align: right;;">58</td><td style="text-align: right;;">0.6532</td><td style=";">Leader</td><td style="text-align: right;border-bottom: 1px solid black;;">53</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">Glendale, WI</td><td style="text-align: right;;">75</td><td style="text-align: right;;">52</td><td style="text-align: right;;">0</td><td style="text-align: right;;">127</td><td style="text-align: right;;">55</td><td style="text-align: right;;">0.5906</td><td style="text-align: right;;">7.5</td><td style="text-align: right;border-top: 1px solid black;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style=";">Decatur, GA</td><td style="text-align: right;;">75</td><td style="text-align: right;;">52</td><td style="text-align: right;;">0</td><td style="text-align: right;;">127</td><td style="text-align: right;;">55</td><td style="text-align: right;;">0.5906</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style=";">Covington, OH</td><td style="text-align: right;;">61</td><td style="text-align: right;;">65</td><td style="text-align: right;;">0</td><td style="text-align: right;;">126</td><td style="text-align: right;;">56</td><td style="text-align: right;;">0.4841</td><td style="text-align: right;;">21</td><td style="text-align: right;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style=";">Glenview, IL</td><td style="text-align: right;;">56</td><td style="text-align: right;;">70</td><td style="text-align: right;;">0</td><td style="text-align: right;;">126</td><td style="text-align: right;;">56</td><td style="text-align: right;;">0.4444</td><td style="text-align: right;;">26</td><td style="text-align: right;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style=";">Sugar Land, TX</td><td style="text-align: right;;">42</td><td style="text-align: right;;">84</td><td style="text-align: right;;">0</td><td style="text-align: right;;">126</td><td style="text-align: right;;">56</td><td style="text-align: right;;">0.3333</td><td style="text-align: right;;">40</td><td style="text-align: right;;">18</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">League</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E38</th><td style="text-align:left">=SUM(<font color="Blue">B38:D38</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F38</th><td style="text-align:left">=182-E38</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G38</th><td style="text-align:left">=(<font color="Blue">B38+(<font color="Red">D38/2</font>)</font>)/(<font color="Blue">B38+C38+D38</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H38</th><td style="text-align:left">=IF(<font color="Blue">MAX(<font color="Red">$G$38:$G$43</font>)=G38,"Leader",(<font color="Red">(<font color="Green">MAX(<font color="Purple">$B$38:$B$43</font>)-B38</font>)+(<font color="Green">C38-MIN(<font color="Purple">$C$38:$C$43</font>)</font>)</font>)/2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I38</th><td style="text-align:left">=IF(<font color="Blue">E38=182,"Clinched!",B39+(<font color="Red">182-E38</font>)-B38+1</font>)</td></tr></tbody></table></td></tr></table><br />

where W are the Days won, L are the days lost, T are the days tied, DP are the days performed, DL are the days left, and the magic number. My issue is that when a group reaches zero in the magic number (the formula in I39-43) before 182 days (6 months) it will start reading negative numbers rather then "Eliminated", it's a slight miscalculation but I'm hoping someone give me the better formula for this one. By the way, the elimination number in I39-I43 is =IF(F39=182, "Eliminated",(182+1)-B$38-C39) I've also got a sorting issue with this sheet but I'll leave that for a different post for now.

I hope this helps with the question that you have on my issue.

 

[KC2MF]

So I solved half of the problem, if that makes any sense.

I changed the formula from . . .

=IF(E4=162,"Eliminated",(162+1)-B$3-C4)

to this . . .

=IF((162+1)-B$3-C4<0,"Eliminated",(162+1)-B$3-C4)

My issue is how to use the function when it reaches Zero (since the team will already be out of contention by then. The other half of the problem is what the "Clinched" equation should be. Right now I have it written as . . .

=IF(E3=182,"Clinched!",B4+(182-E3)-B3+1)

Thanks for the help and questions. I'll be happy to field any more if you've got them.

 

[KC2MF]

Just had a "Duh" Moment on this end,

I simply changed the equation by ONE number and I've got the elimination formula in the bag.

By changing this . . .

=IF((182+1)-B$3-C4<0,"Eliminated",(182+1)-B$3-C4)

to this . . .

=IF((182+1)-B$3-C4<1,"Eliminated",(182+1)-B$3-C4)

thus, the instance of Zeros are gone. I still have the issue with the Clinch formula though.

 

[KC2MF]

Took some minutes but the pieces fell together for the second half of the problem that I had.

My clinching problem was . . .

=IF(E3=182,"Clinched!",B4+(182-E3)-B3+1)

but now it's . . .

=IF(B4+(182-E3)-B3+1<1,"Clinched",B4+(182-E3)-B3+1)

therefore, I will no longer have zero or negative numbers.

 

[KC2MF]

Here's the current standings with the formula fixes that I've posted here (going into today).

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">37</td><td style="font-weight: bold;;">Team Group</td><td style="font-weight: bold;;">W</td><td style="font-weight: bold;;">L</td><td style="font-weight: bold;;">T</td><td style="font-weight: bold;;">DP</td><td style="font-weight: bold;;">DL</td><td style="font-weight: bold;;">Win %</td><td style="font-weight: bold;;">D's Back</td><td style="font-weight: bold;;">Magic #</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style=";">Cherry Hill, NJ</td><td style="text-align: right;;">97</td><td style="text-align: right;;">65</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.5988</td><td style=";">Leader</td><td style="text-align: right;border-bottom: 1px solid black;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">Glendale, WI</td><td style="text-align: right;;">92</td><td style="text-align: right;;">70</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.5679</td><td style="text-align: right;;">5</td><td style="text-align: right;border-top: 1px solid black;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style=";">Decatur, GA</td><td style="text-align: right;;">91</td><td style="text-align: right;;">71</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.5617</td><td style="text-align: right;;">6</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style=";">Scottsdale, AZ</td><td style="text-align: right;;">91</td><td style="text-align: right;;">71</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.5617</td><td style="text-align: right;;">6</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style=";">Emeryville, CA</td><td style="text-align: right;;">90</td><td style="text-align: right;;">72</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.5556</td><td style="text-align: right;;">7</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style=";">Webster Groves, MO</td><td style="text-align: right;;">86</td><td style="text-align: right;;">76</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.5309</td><td style="text-align: right;;">11</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style=";">Covington, OH</td><td style="text-align: right;;">83</td><td style="text-align: right;;">79</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.5123</td><td style="text-align: right;;">14</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style=";">Arlington, VA</td><td style="text-align: right;;">80</td><td style="text-align: right;;">82</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.4938</td><td style="text-align: right;;">17</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style=";">Levittown, NY</td><td style="text-align: right;;">80</td><td style="text-align: right;;">82</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.4938</td><td style="text-align: right;;">17</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style=";">Wilkinsburg, PA</td><td style="text-align: right;;">79</td><td style="text-align: right;;">83</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.4877</td><td style="text-align: right;;">18</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style=";">Aurora, CO</td><td style="text-align: right;;">77</td><td style="text-align: right;;">85</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.4753</td><td style="text-align: right;;">20</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style=";">Downey, CA</td><td style="text-align: right;;">76</td><td style="text-align: right;;">86</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.4691</td><td style="text-align: right;;">21</td><td style=";">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style=";">La Mesa, CA</td><td style="text-align: right;;">75</td><td style="text-align: right;;">87</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.4630</td><td style="text-align: right;;">22</td><td style=";">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style=";">Fountainbleau, FL</td><td style="text-align: right;;">69</td><td style="text-align: right;;">93</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.4259</td><td style="text-align: right;;">28</td><td style=";">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style=";">Glenview, IL</td><td style="text-align: right;;">65</td><td style="text-align: right;;">97</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.4012</td><td style="text-align: right;;">32</td><td style=";">Eliminated</td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style=";">Sugar Land, TX</td><td style="text-align: right;;">57</td><td style="text-align: right;;">105</td><td style="text-align: right;;">0</td><td style="text-align: right;;">162</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.3519</td><td style="text-align: right;;">40</td><td style=";">Eliminated</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">League</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E38</th><td style="text-align:left">=SUM(<font color="Blue">B38:D38</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F38</th><td style="text-align:left">=182-E38</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G38</th><td style="text-align:left">=(<font color="Blue">B38+(<font color="Red">D38/2</font>)</font>)/(<font color="Blue">B38+C38+D38</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H38</th><td style="text-align:left">=IF(<font color="Blue">MAX(<font color="Red">$G$38:$G$50</font>)=G38,"Leader",(<font color="Red">(<font color="Green">MAX(<font color="Purple">$B$38:$B$50</font>)-B38</font>)+(<font color="Green">C38-MIN(<font color="Purple">$C$38:$C$50</font>)</font>)</font>)/2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I38</th><td style="text-align:left">=IF(<font color="Blue">B39+(<font color="Red">182-E38</font>)-B38+1<1,"Clinched!",B39+(<font color="Red">182-E38</font>)-B38+1</font>)</td></tr></tbody></table></td></tr></table><br />