Solved Changing a ms excel pass or fail formula in a gradebook

Microsoft Office 2010 professional
July 3, 2013 at 11:45:02
Specs: Windows 7
As previously developed by one of your volunteers, the following grade book formula correctly returns a “PASS or FAIL” response based on inputs to the respective cells. The formula does NOT return “PASS or FAIL” response until all the required cells have data entered in them.

=IF(COUNTA(M7,S7,Q7)=3,IF(AND(M7>1, S7="Y", Q7>79),"PASS", "FAIL"),"")

I no longer need the S7 column and plan to delete it from my spreadsheet.

I made the following modifications to the above formula to remove the S7 cell for “Y” or “N”.

=IF(COUNTA(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

However, with this change, the response returns “PASS” when I enter data into cell M7 and before the entry in cell Q7 is complete.

What changes can I make to the initial formula to ignore cell S7 but not provide a “PASS or FAIL” response until all entries are complete in cells M7 and Q7?

Thanks for your help!


See More: Changing a ms excel pass or fail formula in a gradebook

Report •


✔ Best Answer
July 5, 2013 at 13:29:08
You have me totally confused now.

I was under the impression that the formula in S7 was:

=IF(COUNT(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

Is this correct?

I was also under the impression that the formula in Q7 was:

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,"")

Is this correct?

MIKE

http://www.skeptic.com/



#1
July 3, 2013 at 11:59:20
I am not seeing the behavior you described. The cell with your new formula remains blank until I enter data in both M7 and Q7.

Are you sure that Q7 is empty?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 3, 2013 at 12:17:04
Hi,

M7 is a cell with a drop-down list of required briefs by number, e.g., 1.1, 2.2, etc.

Q7 is a calcuated cell that sums the total course points per the following formula:

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7," ")

The initial formula you helped me with worked great. I would have thought simply deleting the unneeded column (or removing references to the cell in the formula) would have made the changes to the formula, but alas...I'm stuck.

I seem to remember that I had a similar problem with a formula that was not workong properly and I had to reboot my computer to have it work. I'll try that, while I'm waiting for your follow up.

Thanks.


Report •

#3
July 3, 2013 at 12:46:43
Hi,

A reboot of my computer did not solve the problem. I also attempted to upload a screen capture; however, the "Submit" application does not appear to work in my IT environment. Is there another way to submit a screen capture and/or the file?

Thanks.


Report •

Related Solutions

#4
July 3, 2013 at 12:51:54
Not sure if this will help,
but you explain what is going on with M7 and Q7

But the formula that is used in Q7 references cell L7 & P7......

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7," ")

MIKE

http://www.skeptic.com/


Report •

#5
July 3, 2013 at 13:03:58
Hi Mike,

That is correct. Q7 has the sum total points for the course as entered/calcuated in cells L7 and P7. It only shows in Q7 when there are entries in the respective cells.

There is something amiss with my M7 cell. Keeping all other cells blank, I can enter data in M7 and it will force the "PASS" response in S7. That should NOT be happening until all the other cells have data entered.

Thanks,
Andrew


Report •

#6
July 3, 2013 at 13:51:06
It only shows in Q7 when there are entries in the respective cells.

If L7 and P7 are Blank
the first condition of your IF statement Fails,
but
If L7 and P7 are NOT blank,
the first condition of your IF statement will come back as TRUE

=IF(COUNTA(M7,Q7)=2

and if the total for L7 and P7 is greater than 79
you will get PASS, since we already know that
M7 has a minimum value of 1.1

IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

Would seem to me your problem is with Q7 or L7 and P7, no?

MIKE

http://www.skeptic.com/


Report •

#7
July 3, 2013 at 14:10:35
Hi Mike,

Yes, I agree. But, I don't see why.

The following formula worked fine:

=IF(COUNTA(M7,S7,Q7)=3,IF(AND(M7>1, S7="Y", Q7>79),"PASS", "FAIL"),"")

If I no longer want to this formula to consider the S7 data, shouldn't I simply be able to delete any reference to S7 as follows:

=IF(COUNTA(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

In theory, shouldn't this work the same way sans the S7 data?

Thanks,
Andrew



Report •

#8
July 3, 2013 at 14:53:15
In theory, shouldn't this work the same way sans the S7 data?

That would be dependent on what is going on with Q7.

Originally you had: =IF(COUNTA(M7,S7,Q7)=3

So,
M7 must have data
S7 must have data, it does not matter if it was Y or N or X or Y it just matters that it was not blank
Q7 must have data

It was in the second part of the formula, the AND() function, that S7 had to have a specific value of Y
If all the AND conditions were true you got PASS
If only ONE of the conditions was not true you got FAIL.

Now, since your getting a PASS response, we know the COUNTA() part of the formula is returning TRUE.

=IF(COUNTA(M7,Q7)=2

So M7 is not blank
and Q7 is not blank,

The second part of the formula is the AND() function

IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

we already know from the COUNTA() that both M7 and Q7 are NOT blank and since with the AND() function ALL the tests must return true,


M7 must be greater than 1
Q7 must be greater than 79

Now, since M7 is a drop down and has a minimum value of 1.1 which is Greater than 1, the problem should be in Q7, which is the formula:

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7," ")

So this would mean that both L7 and P7 are NOT blank and the total is greater than 79.

So, as far as I can tell, your problem is with Q7 or L7 & P7.

EDIT: Just noticed I had written M1 in several spots.
It should have been M7.
Corrected the error.

MIKE

http://www.skeptic.com/


Report •

#9
July 3, 2013 at 16:40:28
From: http://office.microsoft.com/en-us/e...

Syntax

COUNTA(value1,value2,...)

Value1, value2, ... are 1 to 30 arguments representing the values you want to count.

Remarks

A value is any type of information, including error values and empty text (""). A value does not include empty cells.

First, this formula will either place a number or a space in Q7, so Q7 is never empty.

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7," ")

Try this with nothing in L7 or P7:

=COUNTA(Q7) (Hint: It will return 1)

Second, even if you eliminate the space and use...

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,"")

...you are still going to get something in Q7 for COUNTA to count because "A value is any type of information, including error values and empty text ("")

Use something like this instead of COUNTA:

=IF(AND(M7<>"",Q7<>""),IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
July 3, 2013 at 19:05:49
DerbyDad03,

I just did an Evaluate Formula on:

=IF(COUNTA(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

In M7 I entered 1.1

Both L7 & P7 were blank, so the formula:

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7," ")

returns a Space Character for Q7

But now in the first formula the AND() function:

IF(AND(M7>1, Q7>79

Both M7 and Q7 return TRUE.....

I understand about the COUNTA() function accepting a Space Character

But, how can a Space Character be greater than the number 79?

I also changed the formula to return a null:

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,"")

and the AND() still returns TRUE.

I see it when I do the Evaluate Formula, but don't understand why.
How can a Space Character or a "" be greater than the
number 79?

What am I missing here?

MIKE

http://www.skeptic.com/


Report •

#11
July 3, 2013 at 19:46:31
You are not missing anything. However, what I am missing is a site to link you to that explains this. Can't find one tonight for some reason, but I'll keep looking.

For now, just take it on faith that Excel considers any text string, including 'empty text' ("") to be greater than any number.

With your Q7 formula returning the empty text string, or any text string, try this formula:

=Q7>9.9E+307

9.9E+307 is a pretty big number, right? Yet "" is greater than 9.9E+307

That's just how Excel works.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
July 3, 2013 at 21:02:44
Learn something new every day.....

Thanks

MIKE

http://www.skeptic.com/


Report •

#13
July 4, 2013 at 07:02:29
How's this...

The first AND checks that M7 is not empty and that Q7 contains a number. This eliminates the issue with Excel considering "" to be greater than 79 since the formula won't evaluate IF(AND(M7>1, Q7>79) unless Q7 is a number. The only way Q7 can be a number is if there are numbers in L7 and P7.

=IF(AND(M7<>"",ISNUMBER(Q7)),IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
July 4, 2013 at 07:45:14
Both of your solutions work.

=IF(AND(M7<>"",ISNUMBER(Q7)),IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

and

=IF(AND(M7<>"",Q7<>""),IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")


It was just the serendipitous occurrence of both the COUNTA()
which will accept a empty text ("")
and
the AND() functions which, as you pointed out, will accept any text string, including 'empty text' ("") to be greater than any number.

Interesting problem.

MIKE

http://www.skeptic.com/


Report •

#15
July 4, 2013 at 09:14:57
Actually, this formula only works if Andrew changes the original formula that he had in Q7:

=IF(AND(M7<>"",Q7<>""),IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

If he uses his original formula, the space will make both Q7<>"" and Q7>79 TRUE, therefore just entering a value >1 in M7 will return PASS.

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7," ")

This is probably the best combination, since putting a space in Q7 not only causes problems, but isn't very elegant anyway:

=IF(AND(M7<>"",Q7<>""),IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

In Q7: =IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
July 4, 2013 at 09:32:55
I think perhaps the best would be:

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,0)

Then even the COUNTA() solution works.

MIKE

http://www.skeptic.com/


Report •

#17
July 4, 2013 at 12:21:32
Thanks Gents. I feel bad to put you both through this, but I assume you guys live for this kinda stuff.

So, this is where I am:

For ease of reference, my formula in S7 remains:

=IF(COUNTA(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

Per Mike's recommendation, I pasted =IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,0) into Q7 and it appears to calculate PASS/FAIL in S7 for the most part.

However, if I enter data in M7 > 1.0 it automatically returns a FAIL response in S7 until such time as P7 is > 79 and then S7 returns a PASS response. I need S7 to remain BLANK until the required data is entered in both M7 and Q7.

Alternatively, when M7 does not have data yet, S7 remains BLANK (as it should) even if I have P7 > 79.

Is there a way to ensure S7 remains BLANK until the required data is entered in both M7 and Q7.

Thanks,
Andrew


Report •

#18
July 4, 2013 at 12:21:33
I thought of that, but that would depend on whether Andrew wants to see zeros or not

I have sheets where I don't want to see zeros, so I have Show Zero Values off. I have others where I want to see zeros, so the option is set to on. The vast, vast majority have zeros off.

Since you can't mix and match, I guess it all depends on how the user wants the sheet to look.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
July 4, 2013 at 12:28:42
Hi DerbyDad,

The above entries do result in "0" being displayed in Q7 until data in the required cells has been entered. I can live with it, but my preference is that the "0" is not displayed (unless the actual score is zero based on the input scores).

Andrew


Report •

#20
July 4, 2013 at 15:58:47
If your preference is No Zero, then I would go with DerbyDad03's

=IF(AND(M7<>"",ISNUMBER(Q7)),IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

MIKE

http://www.skeptic.com/


Report •

#21
July 4, 2013 at 17:03:55
OK, we should all probably not over think these things,
try this formula:

=IF(COUNT(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

Look familiar?

COUNTA()
This function counts the number of numeric or text entries in a list.

COUNT()
This function counts the number of numeric entries in a list.
It will ignore blanks, text and errors.

MIKE

http://www.skeptic.com/


Report •

#22
July 5, 2013 at 08:37:20
Hi Mike,

I put the following into S7:

=IF(COUNT(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

Now, it returns "FAIL" in S7 when I have M7 > 1 and until I have a score over 79 in Q7. Using this new formula, S7 correctly is blank when I have Q7 > 79 and only turns to "PASS" when I entere M7 > 1.

This seems just the opposite of the problem I was still having with the last formula.

Is there anything that can be fixed to keep S7 blank until all entries in M7 and Q7 are complete?

Andrew


Report •

#23
July 5, 2013 at 08:44:05
You indicated your preference is No Zero so using COUNT() allows you to use your original Q7 formula of:

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,"")

Which will return a blank until L7 and P7 have data.

MIKE

http://www.skeptic.com/


Report •

#24
July 5, 2013 at 08:52:57
That seems to correct the problem with the blanks, yet it now returns a numerical score in Q7 rather than a "PASS" or "FAIL." It is also no longer considering M7 > 1, which is part of the Q7 "PASS" or "FAIL."

Report •

#25
July 5, 2013 at 09:09:08
numerical score in Q7 rather than a "PASS" or "FAIL."

Q7 never displays PASS or FAIL
Q7 displays the sum of L7 & P7

In Q7

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,"")

Q7 will remain blank until numeric data is entered in both L7 and P7
Q7 does no checking on cell M7.
So if you enter data in both L7 and P7, but NOT M7
then Q7 will still display a value.

In S7

=IF(COUNT(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

This is where the PASS / FAIL is displayed.

Is this how you have your data set up, or am I missing something?

MIKE

http://www.skeptic.com/


Report •

#26
July 5, 2013 at 13:07:17
Sorry! Meant to state:

That seems to correct the problem with the blanks, yet it now returns a numerical score in S7 (NOT Q7) rather than a "PASS" or "FAIL." It is also no longer considering M7 > 1, which is part of the S7 "PASS" or "FAIL."

Apologies,
Andrew


Report •

#27
July 5, 2013 at 13:29:08
✔ Best Answer
You have me totally confused now.

I was under the impression that the formula in S7 was:

=IF(COUNT(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

Is this correct?

I was also under the impression that the formula in Q7 was:

=IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,"")

Is this correct?

MIKE

http://www.skeptic.com/


Report •

#28
July 5, 2013 at 13:50:47
Hi Mike,

Yes, I have the following:

S7: =IF(COUNT(M7,Q7)=2,IF(AND(M7>1, Q7>79),"PASS", "FAIL"),"")

Q7 had: =IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,0); however, I just changed it to: =IF(AND(ISNUMBER(L7), ISNUMBER(P7)),L7+P7,"")

Looks like it WORKS!

Thank you so much,
Andrew


Report •


Ask Question