Why does this not work (2)

[Alex Hammerstein]

Hi apologies for the earlier post, I posted the wrong formula and the wrong
question.

Ok Can some kin d person tell me why the following formula works Ok in
Office 2007 on a PC but when running on Office 2008, doesn't work properly.
All I get is 'Distinction, no matter what marks I have.

Thanks


=IF(TASK2!$G6="completed",(IF(TASK2!$G6<40,"fail",
IF(TASK2!$G6>69,"distinction", "pass"))),"")

 

[Bob Greenblatt]

Hi apologies for the earlier post, I posted the wrong formula and the wrong
question.

Ok Can some kin d person tell me why the following formula works Ok in
Office 2007 on a PC but when running on Office 2008, doesn't work properly.
All I get is 'Distinction, no matter what marks I have.

Thanks


=IF(TASK2!$G6="completed",(IF(TASK2!$G6<40,"fail",
IF(TASK2!$G6>69,"distinction", "pass"))),"")

If G6 ="completed", then the formula evaluates the TRUE condition which is
the parentheses bound If statement. Excel forces the values 40 and 69 to
text, thus "completed" is always greater than any numeric value resulting in
"distinction".

I have no clue as to why it works correctly in Office 97. It should NOT, but
should work the same way as in Excel 2008. I suggest that it is not really
working correctly in Excel 2007, or you are not recording the formula
correctly as I suspect due to your original post with a different formula.

 

[Laroche J]

Alex Hammerstein wrote on 2009-03-17 12:35:

Hi apologies for the earlier post, I posted the wrong formula and the wrong
question.

Ok Can some kin d person tell me why the following formula works Ok in
Office 2007 on a PC but when running on Office 2008, doesn't work properly.
All I get is 'Distinction, no matter what marks I have.

Thanks


=IF(TASK2!$G6="completed",(IF(TASK2!$G6<40,"fail",
IF(TASK2!$G6>69,"distinction", "pass"))),"")

In your first post the formula was:
=IF(TASK2!$G6="completed",(IF(TASK2!$I6<40,"fail",
IF(TASK2!$I6>69,"distinction", "pass"))),"")

while in the second it was:
=IF(TASK2!$G6="completed",(IF(TASK2!$G6<40,"fail",
IF(TASK2!$G6>69,"distinction", "pass"))),"")

Despite your claim, I don't see how the second one is right, since it
assumes that the same cell (G6) can contain "completed", and if true then
the grade. It's either the word or the grade, not both.

In your first formula, the grade is in a separate cell (I6) and for me it
works perfectly.

It's a matter of taste, but personally I prefer to use a lookup table rather
than nested IFs. The biggest advantage is that if you change you criterions
for pass/fail/distinction you don't have to modify all the formulas, and
adding finer categories is also much easier. All you need is a little
two-columns by three-rows table defined as a range which I named Rating:
0 fail
40 pass
70 distinction

Then the formula to use it is simply:
=IF(TASK2!$G6="completed",VLOOKUP(TASK2!$I6,Rating,2,TRUE),"")

JL
Mac OS X 10.4.11, Office v.X 10.1.9