i am making a result in excel with the condition that out of 5 subjects, the students has to get>=50 in all the subjects to get pass, if fail in two or less subjects he can reappear but fail more than 2 subjects he is failed. how to construct the formula in excel? help please

i tried

=if(and(counta( range of subjects)=5,min(range of subject)>=50,"pass",if(and(counta(rangeof subject)>=3,min(rangeof subject)>=50,"reappear",if(and(counta(rangeof subject)>=3,min(range of subject)<50,fail,"")))

but is not working

Sounds like a homework assignment. We can offer suggestions but we won't do your homework for you. Try using COUNTIF instead of MIN and COUNTA.

=IF(COUNTIF(...etc.))

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

Derbydado3,

thank u for your prompt reply. i would i to inform you that your assumption is wrong that i am shrugging off my assignment to others. in fact it is my self interest to learn the features in excel. as for your suggestion, i am bit not clear how i should actually go about it. one condition here is that the person can fail any two subjects to reappear exam and is considered fail if failed in any 3 or more subjects. ( how to nest different functions all in one cell, so that my conditions are fulfilled)

1 - When using a Nested IF, you should keep in mind that the function is evaluated from left to right and that it stops evaluating as soon as one of the IF conditions is TRUE. 2 - COUNTIF can be used to count the number of cells within a range that meet the specified condition.

Combining 1 & 2, you can use a Nested IF with COUNTIF to count the number of cells that are >=50 within a range.

By checking for the highest number (5) first, the function below will return Pass if the first COUNTIF returns 5, making the first IF TRUE. If that condition is FALSE, it will continue on to the next IF and check for at least 3 values of >=50. If that condition is TRUE, it will return Reappear. If that condition is FALSE, it will default to Fail.

Assuming the scores are in A1:A5, this is how the COUNTIF function might work for you:

=IF(COUNTIF(A1:A5,">=50")=5, "PASS", IF(COUNTIF(A1:A5,">=50")>=3, "Reappear", "Fail"))

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

Thank you for your effort.I tried it but its not working. it says there is error in the formula....can you still suggest something

Derby thank you You! i got it...i think instead of (A1:A5,">=50")>=3, it has to be (A1:A5,"<50")<=2 so that it will look for 3 or more which is more than 50. anyway thank you so much....i have spent almost 2 and half days struggling on it

re: "i think instead of (A1:A5,">=50")>=3, it has to be (A1:A5,"<50")<=2 "I disagree that is

has to be(A1:A5,"<50")<=2. Itcan be, but it doesn'thave to be.In your situation, these 2 conditions are equivalent:

(A1:A5,"<50")<=2

(A1:A5,">=50")>=3

Think about it. If there are 2 or fewer cells that are less than 50, then there must be 3 or more cells that are 50 or greater.

Both of these formulas will return the same results:

=IF(COUNTIF(A1:A5,">=50")=5, "PASS", IF(COUNTIF(A1:A5,">=50")>=3, "Reappear", "Fail"))

=IF(COUNTIF(A1:A5,">=50")=5, "PASS", IF(COUNTIF(A1:A5,"<50")<=2, "Reappear", "Fail"))

Copy and paste them directly into a spreadsheet and tell me what happens.

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

Ask Your Question

Weekly Poll