mubashiraziz
Board Regular
- Joined
- Apr 2, 2009
- Messages
- 175
I've work hard on this problem and after posting the same problem on below forum got some exceptional answer. I do'nt think so that there can be any more improvements but might be some "Genius" came up with extra-ordinary solution like I am looking for a solution in a single column G:
http://www.excelforum.com/excel-gen...anges-in-sumproduct-and-countif-in-array.html
I want to get students positions in a class like 1st, 2nd, 3rd, etc. There can be two 1st position and 2 2nd positions at a time .............. So to get this I did following steps:
1.In G Column I put logic for Fail and Obtained Marks.
G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
2. Then i made array formula in column H to get the position
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
Some improvement to write fail & position
={IF(COUNTIF(B3:F3,">=60")<>5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
And more work to get total marks & position and Fail at the same time......
={IF(COUNTIF(B2:F2,">=60")<>5,"Fail",SUM(B2:F2)&"-"&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
Now I wounder that is there a way to do all calculations in column H without using column G .... ....
By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
Hope to have a quick & effective solution from experts.
http://www.excelforum.com/excel-gen...anges-in-sumproduct-and-countif-in-array.html
I want to get students positions in a class like 1st, 2nd, 3rd, etc. There can be two 1st position and 2 2nd positions at a time .............. So to get this I did following steps:
1.In G Column I put logic for Fail and Obtained Marks.
G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
2. Then i made array formula in column H to get the position
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
Some improvement to write fail & position
={IF(COUNTIF(B3:F3,">=60")<>5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
And more work to get total marks & position and Fail at the same time......
={IF(COUNTIF(B2:F2,">=60")<>5,"Fail",SUM(B2:F2)&"-"&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
Now I wounder that is there a way to do all calculations in column H without using column G .... ....
By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
Hope to have a quick & effective solution from experts.
| Excel Workbook | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Students Name | Eng | Math | Phy | Chem | Bio | If Pass Then Marks Else Obtained Marks | If Pass Then Student Position in Class | ||
| 2 | John | 85 | 90 | 59 | 75 | 60 | Fail | Fail | ||
| 3 | Maria | 85 | 69 | 60 | 85 | 75 | 374 | 374-2 | ||
| 4 | JANNY | 90 | 60 | 60 | 90 | 60 | 360 | 360-3 | ||
| 5 | Gaughan | 0 | 0 | 0 | 0 | 0 | Fail | Fail | ||
| 6 | Madona | 75 | 75 | 80 | 65 | 80 | 375 | 375-1 | ||
| 7 | Wright | 75 | 75 | 75 | 80 | 55 | Fail | Fail | ||
| 8 | Trying to get solution as "If Pass Then Position Else "Fail" " | I want to skip this column and want the same result in Column G | ||||||||
| 9 | ||||||||||
| 10 | ||||||||||
Positions | ||||||||||
. I feel my self luckiest person to have such informative forums with capable persons like you. Whenever I get some problems I always get a quick response. Sorry to forcing experts to make a formula in one column but you can visit my other link to know the exact story behind this 
.. 
> </o
I can't believe it to see such a cool formula, this is unbelievable.
