Maybe I'm missing something but could't you just use the below (and dispense with I & J)
H2: =IF(ISNUMBER($G2),$G2&"-"&1+COUNTIF($G$2:$G$7,">"&$G2),"Fail")
copied down
EDIT: perhaps you're saying you want to generate H without need for G, I or J ?
Maybe I'm missing something but could't you just use the below (and dispense with I & J)
H2: =IF(ISNUMBER($G2),$G2&"-"&1+COUNTIF($G$2:$G$7,">"&$G2),"Fail")
copied down
EDIT: perhaps you're saying you want to generate H without need for G, I or J ?
Last edited by DonkeyOte; 07-02-2009 at 04:39 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
If I'm correct and you want to dispense with all but A:F and present results in G then perhaps:
Note however that this is Volatile Sumproduct and as such performance is not going to be great - you would IMO be best served forgoing elegance for the sake of efficiency and using your existing setup of G and previously provided solution for H.![]()
G2: =IF(COUNTIF($B2:$F2,"<60"),"Fail",SUM($B2:$F2)&"-"&1+SUMPRODUCT(--(SUM($B2:$F2)<(SUBTOTAL(9,OFFSET($B$2:$F$2,ROW($B$2:$F$7)-ROW($B$2:$F$2),0))*(SUBTOTAL(5,OFFSET($B$2:$F$2,ROW($B$2:$F$7)-ROW($B$2:$F$2),0))>=60)))))
You could probably do the above with a FREQUENCY Array but these aren't my strong point - I will take a look though.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks