Excel formula; all negative values return with 0

  • Follow


Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I created a formula that counts up elective courses in a spread sheet row that someone needs to take. <br><br>Example: =3-COUNTA(O26:W26) <br><br>Problem is that they can take many different ones, but are only required to take three. When I get negative numbers, is there a way I can build them formula to return all negative numbers to zero?
0
Reply ranthny 2/2/2010 9:55:46 PM

I finally figured out the formula: <br><br>=IF(3-COUNTA(N2:V2)<0,0,3-COUNTA(N2:V2)) <br><br>equals <br><br>Total required electives - # of electives taken is less than zero; then return the number to zero; if not then it is Total required electives - # of electives taken
0
Reply ranthny 2/3/2010 12:21:30 AM

> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

--B_3348045621_72543827
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit

On 2/2/10 4:55 PM, in article 59bb2203.-1@webcrossing.JaKIaxP2ac0,
"ranthny@officeformac.com" <ranthny@officeformac.com> wrote:

> Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I
> created a formula that counts up elective courses in a spread sheet row that
> someone needs to take.
> 
> Example: =3-COUNTA(O26:W26)
> 
> Problem is that they can take many different ones, but are only required to
> take three. When I get negative numbers, is there a way I can build them
> formula to return all negative numbers to zero?
Sure. =if(counta(026:w26)>3,0,3-counta(o26:w26))

-- 
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom


--B_3348045621_72543827
Content-type: text/html;
	charset="US-ASCII"
Content-transfer-encoding: quoted-printable

<HTML>
<HEAD>
<TITLE>Re: Excel formula; all negative values return with 0</TITLE>
</HEAD>
<BODY>
<FONT FACE=3D"Lucida Grande"><SPAN STYLE=3D'font-size:10pt'>On 2/2/10 4:55 PM, =
in article <a href=3D"59bb2203.-1@webcrossing.JaKIaxP2ac0">59bb2203.-1@webcros=
sing.JaKIaxP2ac0</a>, &quot;<a href=3D"ranthny@officeformac.com">ranthny@offic=
eformac.com</a>&quot; &lt;<a href=3D"ranthny@officeformac.com">ranthny@officef=
ormac.com</a>&gt; wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Lucida Grande"><SPAN STYLE=3D'font-size=
:10pt'>Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: In=
tel I created a formula that counts up elective courses in a spread sheet ro=
w that someone needs to take. <BR>
<BR>
Example: =3D3-COUNTA(O26:W26) <BR>
<BR>
Problem is that they can take many different ones, but are only required to=
 take three. When I get negative numbers, is there a way I can build them fo=
rmula to return all negative numbers to zero?<BR>
</SPAN></FONT></BLOCKQUOTE><FONT FACE=3D"Lucida Grande"><SPAN STYLE=3D'font-siz=
e:10pt'>Sure. =3Dif(counta(026:w26)&gt;3,0,3-counta(o26:w26))<BR>
</SPAN></FONT><FONT SIZE=3D"2"><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN S=
TYLE=3D'font-size:9pt'><BR>
-- <BR>
Bob Greenblatt [MVP], Macintosh<BR>
bobgreenblattATmsnDOTcom</SPAN></FONT></FONT><FONT FACE=3D"Lucida Grande"><SP=
AN STYLE=3D'font-size:10pt'><BR>
</SPAN></FONT>
</BODY>
</HTML>


--B_3348045621_72543827--

0
Reply Bob 2/3/2010 5:40:21 PM

2 Replies
1375 Views

(page loaded in 0.035 seconds)


Reply: