Using multipel sum ranges in Sumproduct() & countif() in array

I confess I'm not sure the results you want make a great deal of sense to me (given in the example position 2 is rally the 2nd instance of position 1 and thus does not exist per se) but... I would still be inclined to revert back to an older incarnation of your solution so as to keep as efficient as possible...

G2: =IF(MIN(B2:F2)<60,0,SUM(B2:F2))
copied down as required

Apply format to G2:G7 of: General;;"FAIL"

Using the Custom Format approach you can leave the underlying values in G as numerics to simplify latter calcs but have the cell appear to contain text string.

Then to generate your desired result of Score-SpecialRank / Fail use:

H2: =IF($G2,$G2&"-"&1+SUMPRODUCT(--($G$2:$G$7>$G2),1/COUNTIF($G$2:$G$7,$G$2:$G$7)),"Fail")
copied down as required

This way you're avoiding the use of Volatile Sumproducts etc...

I know others with greater skills than me are looking at this so you might still (if you're lucky) get a humdinger of a solution... the above is based purely on my own opinion & skillset.

Thanks dll - very kind of you to say...

Originally Posted by dll

I can't think of anybody who falls into that category.....

I think we both know to whom I was referring

...and without getting into the mutual backslapping too much you would certainly feature in my personal trinity of formula gurus (in no particular order).. can you guess the other two ?

@Thx DK for your classic approach towards formulas and sorry to force you on the solution in one column. Actually one of my friend works in a school and her principal / (owner of the school ) wants to do every work in MS Excel as she thinks she knows about Excel and no one can cheat with her as databases are harmful and someone can easily deceive him. Now my friend time to time visit me and always come with a unique / headache problem. After scolding her , I always try to get some solution and she promised that she will try to consol her principal to adopt the same approach as advised by me but after few days again ..

Anyway, now they gave these options

• If the student pass then his marks & position will appear (Solved)
• If the student fail in any subject then “Fail” will appear. (Solved)
• If any subject's result is awaited they want RL (Result Later on) in the status but if the candidates passed in all others subjects. (Now I solved”)

To solved the problem 3rd I just used your approach by formatting *General;"RL";"FAIL"* then make slightly change in

G2= IF(MIN(B2:F2)<60,0,IF(COUNTIF(B2:F2,"RL")>=1,-1,SUM(B2:F2)))

And

H2=IF($G2>0,$G2&"-"&1+SUMPRODUCT(--($G$2:$G$7>$G2),1/COUNTIF($G$2:$G$7,$G$2:$G$7)),IF($G2=0,"FAIL","RL"))

I hope above will be acceptable by them. Now I just asking that Is my approach is right or is there any improvement required?

Moreover, I placed the same query on other forum and got a dynamic solution by pgc01 but with lot of advise (almost scolding) to use DK method (now I’ll transfer the same to my friend). You can see exclusive formula by Guru …..



http://www.mrexcel.com/forum/showthread.php?t=400660

Yes I saw PGC01's solution at MrExcel which was as usual of the very highest quality but he too stressed the point that doing in one cell is not really "optimal".
(PGC01 and I are members of both forums - though he operates here under an alternate user name which I won't divulge in case he wishes to remain anom.)

Re: your revisions - all look fine to me though I would reiterate the point we've discussed previously that only 0 equates to FALSE in XL so you could remove some of your tests as they're not truly required... in the below formulae I've highlighted in red those parts that could be removed:

G2= IF(MIN(B2:F2)<60,0,IF(COUNTIF(B2:F2,"RL")>=1,-1,SUM(B2:F2)))

and

H2=IF($G2>0,$G2&"-"&1+SUMPRODUCT(--($G$2:$G$7>$G2),1/COUNTIF($G$2:$G$7,$G$2:$G$7)),IF($G2=0,"FAIL","RL"))
would require swapping RL & FAIL around in the IF

the above changes would have a negligible (to the point of being immeasurable) impact on performance but just highlighting possible opportunities to shorten formulae.

You could also think about removing the IFs and using a CHOOSE approach based on SIGN of G2

H2=CHOOSE(SIGN($G2)+2,"RL","FAIL",$G2&"-"&1+SUMPRODUCT(--($G$2:$G$7>$G2),1/COUNTIF($G$2:$G$7,$G$2:$G$7)))

Originally Posted by DonkeyOte

Re: your revisions - all look fine to me though I would reiterate the point we've discussed previously that only 0 equates to FALSE in XL so you could remove some of your tests as they're not truly required... in the below formulae I've highlighted in red those parts that could be removed:


You could also think about removing the IFs and using a CHOOSE approach based on SIGN of G2

H2=CHOOSE(SIGN($G2)+2,"RL","FAIL",$G2&"-"&1+SUMPRODUCT(--($G$2:$G$7>$G2),1/COUNTIF($G$2:$G$7,$G$2:$G$7)))

Oh its great DK, I never used SIGN but you have shown a new way to use sign with Choose(). In future I'll try to adopt choose.
Thx for your typical explanations ........

Originally Posted by daddylonglegs

Luke, who do you mean? I can't think of anybody who falls into that category.....

At that point I concluded that the best man for the job was already on the case.....great stuff

Agreed with Dll as from the day i joined here, every time i look exceptional replies from DK ........

Originally Posted by DonkeyOte

I confess I'm not sure the results you want make a great deal of sense to me (given in the example position 2 is rally the 2nd instance of position 1 and thus does not exist per se) but... I would still be inclined to revert back to an older incarnation of your solution so as to keep as efficient as possible...

G2: =IF(MIN(B2:F2)<60,0,SUM(B2:F2))
copied down as required

Apply format to G2:G7 of: General;;"FAIL"

I know others with greater skills than me are looking at this so you might still (if you're lucky) get a humdinger of a solution... the above is based purely on my own opinion & skillset.

@DK this part is cool by the use of custom format ....

As i earlier told that i wanted students positions in the class so thats why i couldn't use rank() function because rank and positions are different .... like in a class 1st position can be achieved by 2 students and 2nd can be for 3 but 2nd position can not be vacant ........

I think now this formula can't be shorten but i was just thinking the possibilities of using only column G but please i am 101% agreed with your suggestion and solution of using column G and then H ....... thx for spending time on my solution .....

cheers,