A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Formula help - Sum results Pass or Fail



 
 
Thread Tools Display Modes
  #1  
Old March 4th 08, 08:54 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 216
Default Formula help - Sum results Pass or Fail

Need help with a formula.

Want to sum the total of a range of cells, then if it is less then a
specific number the value should be "Fail", if greater then that number then
value should be "Pass"

I've used the And function True or False results. But they want the value
in the cells to say either Pass or Fail.

current formula: =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)

Thanks
Ads
  #2  
Old March 4th 08, 09:02 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 2,118
Default Formula help - Sum results Pass or Fail

Try this:

=IF(SUM(J8:R8)<=8,"Fail","Pass")

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Nadine" > wrote in message
...
> Need help with a formula.
>
> Want to sum the total of a range of cells, then if it is less then a
> specific number the value should be "Fail", if greater then that number
> then
> value should be "Pass"
>
> I've used the And function True or False results. But they want the value
> in the cells to say either Pass or Fail.
>
> current formula: =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)
>
> Thanks



  #3  
Old March 4th 08, 09:48 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 216
Default Formula help - Sum results Pass or Fail

Tried that originally, but at the bottom of the column want a count for all
that say Fail
Problem is when copy the formula down the column it sets all to Fail because
there isn't any data entered yet, so the count value is wrong. As data will
be entered daily, but there will be blank rows until the entire month is
filled in. But need the count to reflect current count for any day that
they look at it. So if the entire column says Fail, the count will be wrong.

Not too sure how to work around that unless I put in default values in all
the rows and columns, not wanting to do that as it will throw off the count
for all the other columns. And not giving true totals.
Is there a way to leave the pass/fail value blank until it has numbers in
the cells to sum?

"Ron Coderre" wrote:

> Try this:
>
> =IF(SUM(J8:R8)<=8,"Fail","Pass")
>
> Is that something you can work with?
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
>
> "Nadine" > wrote in message
> ...
> > Need help with a formula.
> >
> > Want to sum the total of a range of cells, then if it is less then a
> > specific number the value should be "Fail", if greater then that number
> > then
> > value should be "Pass"
> >
> > I've used the And function True or False results. But they want the value
> > in the cells to say either Pass or Fail.
> >
> > current formula: =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)
> >
> > Thanks

>
>
>

  #4  
Old March 4th 08, 09:52 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 2,118
Default Formula help - Sum results Pass or Fail

Perhaps this?
=IF(COUNT(J8:R8),IF(SUM(J8:R8)<=8,"Fail","Pass")," no data")

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Nadine" > wrote in message
...
> Tried that originally, but at the bottom of the column want a count for
> all
> that say Fail
> Problem is when copy the formula down the column it sets all to Fail
> because
> there isn't any data entered yet, so the count value is wrong. As data
> will
> be entered daily, but there will be blank rows until the entire month is
> filled in. But need the count to reflect current count for any day that
> they look at it. So if the entire column says Fail, the count will be
> wrong.
>
> Not too sure how to work around that unless I put in default values in all
> the rows and columns, not wanting to do that as it will throw off the
> count
> for all the other columns. And not giving true totals.
> Is there a way to leave the pass/fail value blank until it has numbers in
> the cells to sum?
>
> "Ron Coderre" wrote:
>
>> Try this:
>>
>> =IF(SUM(J8:R8)<=8,"Fail","Pass")
>>
>> Is that something you can work with?
>> --------------------------
>>
>> Regards,
>>
>> Ron
>> Microsoft MVP (Excel)
>> (XL2003, Win XP)
>>
>>
>> "Nadine" > wrote in message
>> ...
>> > Need help with a formula.
>> >
>> > Want to sum the total of a range of cells, then if it is less then a
>> > specific number the value should be "Fail", if greater then that number
>> > then
>> > value should be "Pass"
>> >
>> > I've used the And function True or False results. But they want the
>> > value
>> > in the cells to say either Pass or Fail.
>> >
>> > current formula: =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)
>> >
>> > Thanks

>>
>>
>>



  #5  
Old March 4th 08, 10:08 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 216
Default Formula help - Sum results Pass or Fail

YES :-) that did the trick perfectly. Thank you thank you.. Been at this
for awhile now. I'll be adding this to my list of formula tips.

"Ron Coderre" wrote:

> Perhaps this?
> =IF(COUNT(J8:R8),IF(SUM(J8:R8)<=8,"Fail","Pass")," no data")
>
> Does that help?
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
> "Nadine" > wrote in message
> ...
> > Tried that originally, but at the bottom of the column want a count for
> > all
> > that say Fail
> > Problem is when copy the formula down the column it sets all to Fail
> > because
> > there isn't any data entered yet, so the count value is wrong. As data
> > will
> > be entered daily, but there will be blank rows until the entire month is
> > filled in. But need the count to reflect current count for any day that
> > they look at it. So if the entire column says Fail, the count will be
> > wrong.
> >
> > Not too sure how to work around that unless I put in default values in all
> > the rows and columns, not wanting to do that as it will throw off the
> > count
> > for all the other columns. And not giving true totals.
> > Is there a way to leave the pass/fail value blank until it has numbers in
> > the cells to sum?
> >
> > "Ron Coderre" wrote:
> >
> >> Try this:
> >>
> >> =IF(SUM(J8:R8)<=8,"Fail","Pass")
> >>
> >> Is that something you can work with?
> >> --------------------------
> >>
> >> Regards,
> >>
> >> Ron
> >> Microsoft MVP (Excel)
> >> (XL2003, Win XP)
> >>
> >>
> >> "Nadine" > wrote in message
> >> ...
> >> > Need help with a formula.
> >> >
> >> > Want to sum the total of a range of cells, then if it is less then a
> >> > specific number the value should be "Fail", if greater then that number
> >> > then
> >> > value should be "Pass"
> >> >
> >> > I've used the And function True or False results. But they want the
> >> > value
> >> > in the cells to say either Pass or Fail.
> >> >
> >> > current formula: =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)
> >> >
> >> > Thanks
> >>
> >>
> >>

>
>
>

  #6  
Old March 4th 08, 10:25 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 2,118
Default Formula help - Sum results Pass or Fail

I'm so glad I could help, Nadine.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Nadine" > wrote in message
...
> YES :-) that did the trick perfectly. Thank you thank you.. Been at
> this
> for awhile now. I'll be adding this to my list of formula tips.
>
> "Ron Coderre" wrote:
>
>> Perhaps this?
>> =IF(COUNT(J8:R8),IF(SUM(J8:R8)<=8,"Fail","Pass")," no data")
>>
>> Does that help?
>> --------------------------
>>
>> Regards,
>>
>> Ron
>> Microsoft MVP (Excel)
>> (XL2003, Win XP)
>>
>> "Nadine" > wrote in message
>> ...
>> > Tried that originally, but at the bottom of the column want a count for
>> > all
>> > that say Fail
>> > Problem is when copy the formula down the column it sets all to Fail
>> > because
>> > there isn't any data entered yet, so the count value is wrong. As data
>> > will
>> > be entered daily, but there will be blank rows until the entire month
>> > is
>> > filled in. But need the count to reflect current count for any day
>> > that
>> > they look at it. So if the entire column says Fail, the count will be
>> > wrong.
>> >
>> > Not too sure how to work around that unless I put in default values in
>> > all
>> > the rows and columns, not wanting to do that as it will throw off the
>> > count
>> > for all the other columns. And not giving true totals.
>> > Is there a way to leave the pass/fail value blank until it has numbers
>> > in
>> > the cells to sum?
>> >
>> > "Ron Coderre" wrote:
>> >
>> >> Try this:
>> >>
>> >> =IF(SUM(J8:R8)<=8,"Fail","Pass")
>> >>
>> >> Is that something you can work with?
>> >> --------------------------
>> >>
>> >> Regards,
>> >>
>> >> Ron
>> >> Microsoft MVP (Excel)
>> >> (XL2003, Win XP)
>> >>
>> >>
>> >> "Nadine" > wrote in message
>> >> ...
>> >> > Need help with a formula.
>> >> >
>> >> > Want to sum the total of a range of cells, then if it is less then a
>> >> > specific number the value should be "Fail", if greater then that
>> >> > number
>> >> > then
>> >> > value should be "Pass"
>> >> >
>> >> > I've used the And function True or False results. But they want the
>> >> > value
>> >> > in the cells to say either Pass or Fail.
>> >> >
>> >> > current formula: =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>

>>
>>
>>



  #7  
Old March 4th 08, 11:11 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,911
Default Formula help - Sum results Pass or Fail

For a blank look.

=IF(COUNT(J8:R8),IF(SUM(J8:R8)<=8,"Fail","Pass")," ")

Assumes the "Fail", "Pass" formulas are in column A

To count the occurences of Pass or Fail

=COUNTIF(A:A,"Fail")

=COUNTIF(A:A,"Pass")


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 12:48:06 -0800, Nadine >
wrote:

>Tried that originally, but at the bottom of the column want a count for all
>that say Fail
>Problem is when copy the formula down the column it sets all to Fail because
>there isn't any data entered yet, so the count value is wrong. As data will
>be entered daily, but there will be blank rows until the entire month is
>filled in. But need the count to reflect current count for any day that
>they look at it. So if the entire column says Fail, the count will be wrong.
>
>Not too sure how to work around that unless I put in default values in all
>the rows and columns, not wanting to do that as it will throw off the count
>for all the other columns. And not giving true totals.
>Is there a way to leave the pass/fail value blank until it has numbers in
>the cells to sum?
>
>"Ron Coderre" wrote:
>
>> Try this:
>>
>> =IF(SUM(J8:R8)<=8,"Fail","Pass")
>>
>> Is that something you can work with?
>> --------------------------
>>
>> Regards,
>>
>> Ron
>> Microsoft MVP (Excel)
>> (XL2003, Win XP)
>>
>>
>> "Nadine" > wrote in message
>> ...
>> > Need help with a formula.
>> >
>> > Want to sum the total of a range of cells, then if it is less then a
>> > specific number the value should be "Fail", if greater then that number
>> > then
>> > value should be "Pass"
>> >
>> > I've used the And function True or False results. But they want the value
>> > in the cells to say either Pass or Fail.
>> >
>> > current formula: =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)
>> >
>> > Thanks

>>
>>
>>


  #8  
Old January 14th 13, 05:07 PM posted to microsoft.public.excel.worksheet.functions
Vitaliy Kozachuk
external usenet poster
 
Posts: 1
Default Formula help - Sum results Pass or Fail

What whould be a proper way to make a formula sum a total of pass/ fail results.
Let say i have Total Pass: on C2
and line of pass/fail on b14:b573
Thank you



On Tuesday, March 4, 2008 3:02:39 PM UTC-5, Ron Coderre wrote:
> Try this:
>
> =IF(SUM(J8:R8)<=8,"Fail","Pass")
>
> Is that something you can work with?
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
>
> "Nadine" > wrote in message
> ...
> > Need help with a formula.
> >
> > Want to sum the total of a range of cells, then if it is less then a
> > specific number the value should be "Fail", if greater then that number
> > then
> > value should be "Pass"
> >
> > I've used the And function True or False results. But they want the value
> > in the cells to say either Pass or Fail.
> >
> > current formula: =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)
> >
> > Thanks


  #9  
Old January 14th 13, 05:34 PM
VitaliyK VitaliyK is offline
Junior Member
 
First recorded activity by ExcelBanter: Jan 2013
Posts: 1
Default

What whould be a proper way to make a formula sum a total of pass/ fail results.
Let say i have Total Pass: on C2
and line of pass/fail on b14:b573
Thank you


Quote:
Originally Posted by Nadine View Post
Need help with a formula.

Want to sum the total of a range of cells, then if it is less then a
specific number the value should be "Fail", if greater then that number then
value should be "Pass"

I've used the And function True or False results. But they want the value
in the cells to say either Pass or Fail.

current formula: =AND(SUM(J8:R8)>0,SUM(J8:R8)<8)

Thanks
  #10  
Old August 6th 13, 10:20 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Formula help - Sum results Pass or Fail

this one works for school based users

Subject order: Home Lang, Sec Add Lang, Life Orient, Math/Mlit, PHS/ACC/HIST, Bust/Life Sc, Geog/ Econ. use excel.
=IF(AND(D2>39,E2>29,F2>39,OR(G2>29,OR(OR(J2>29,L2> 29,K2>29),OR(I2>29,M2>29),OR(N2>29,H2>29)))),"PASS ","FAIL")...

test with various marks according to the pass requirements. Hope this will be helpful

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Templates Fail Brian Excel Discussion (Misc queries) 0 November 15th 06 08:18 PM
How to calculate pass/fail percentages entered on a spreadsheet? Jenna New Users to Excel 2 August 5th 06 05:29 PM
Pass or Fail (green or red) criteria for cell? Pheasant Plucker® Excel Discussion (Misc queries) 6 January 27th 06 07:47 AM
Formula & functions intermittently fail to calculate LBarnes Excel Worksheet Functions 2 October 20th 05 06:32 PM
pass fail Tricia New Users to Excel 3 January 20th 05 04:45 PM


All times are GMT +1. The time now is 04:33 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©2004-2014 ExcelBanter.
The comments are property of their posters.