Need Result in "F", "T", "I", "TI", "IP", "TP" instead of "PASS" "FAIL"

[vijubhau]

Respected Sir,

There is a Group of subject in the marksheet, which contains "Theory"(Passing Marks=20), "Internal"(Passing Marks=8) & "Practical"(Passing Marks=12) marks

Then How can I insert the formula for the result IN THE CELL contains "Theory"(Passing Marks=20), "Internal"(Passing Marks=8) & "Practical"(Passing Marks=12) marks

I used the following formula for the result:

=IF(OR(COUNT(B2:D2)=0,AND(B2<20,C2<8,D2<12)),"",IF(AND(OR(B2>=20,ISTEXT(B2)),D2>=12),"TP",IF(AND(OR(C2>=8,ISTEXT(C2)),D2>=12),"IP",IF(AND(B2>=20,OR(C2>=8,ISTEXT(C2))),"TI",IF(AND(OR(B2<20,ISTEXT(B2)),C2>=8),"I",IF(AND(B2>=20,OR(C2<8,ISTEXT(C2))),"T",IF(AND(B2>=20,C2>=8,D2>=12),"F")))))))

But it is not working properly... It is showing the result as "TP" if all criteria has a sufficient marks for passing...

I need "F" inspite of "TP"

Thanks in advance..

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[vijubhau]

So.... Sorry Forum Moderator..
Here I am attaching the Excel Sheet for the review of your experts...

[Glenn Kennedy]

Not clear. Please explain the criteria for each of the mark codes, one-by-one. Do not explain your non-working formula. Also, how can rows 2 and 3 have different expected outcomes?

[vijubhau]

Thanks for your valuable reply Sir...

In the Marks codes "F" stands for "Full Exemption" if B1>=20, C1>=08 & D1>=12 marks
"T" stands for "Exemption in theory" if B1=20, C1<08 & D1<12 marks
"P" stands for "Exemption in Practical" if B1<20, C1<08 & D1>=12 marks
So also;
"I" stands for "Exemption in Internal" if B1<20, C1>=08 & D1<12 marks
and other are together with "TP", "TI" & "IP" means "Exemption in Theory & Practical", "Exemption in Theory & Internal" & "Exemption in Internal & Practical" respectively..

[Glenn Kennedy]

... and what is AA?

[vijubhau]

"AA" stands for Absent in the particular respect..

[Glenn Kennedy]

Still here, but I still don't understand the logic. You need to explain IN DETAIL how AA affects the outcome. Does AA count (in effect) as zero, or what.

Double check your expected results and confirm that they are in column F

[Glenn Kennedy]

I have made a guess... and assumed that the expected result shown in orange is incorrect (I raised this back at post 4... but you didn't answer the Q).

[vijubhau]

Thank you Very Very Very Very much Sir... In the ACTUAL RESULT of Cell F4, it has been copy and pasted by mistake... but YOU ARE GENIUS Sir...
You not only MADE THE GUESS BUT SOLVE MY PROBLEM... And So so so SORRY my REPLY.. NOW this thread has been solved... THANKS SIR once again...

[vijubhau]

And So so so SORRY my LATE REPLY..

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.