Excel 2007 : Extracting Largest Variances

[MJDown]

On the attached spreadsheet I have encountered a problem that I am struggling to resolve. In cells G4 and G14 I have variances of 585,000 & (585,000). In column W I am ranking the largest variances regardless if the variance is positive or negative. In cells W6 & W7 the formula is referencing both cells to cell G4 instead of one delivering the value from G4 and the other giving the value of G14

Any help would be gratefully appreciated !!

Many thanks

Mike

[Haseeb Avarakkan]

Hello Mike, try this,

W3, copy down.

=IFERROR(INDEX(M:M,SMALL(IF(M$3:M$133=LARGE(ABS(M$3:M$133),SUM(COUNTIF(M$3:M$133,W$2:W2))+1)*{1,-1},IF(ISNA(MATCH(M$3:M$133,W$2:W2,0)),ROW(M$3:M$133))),1)),"")

Confirmed with CTRL+SHIFT+ENTER

[MJDown]

Hi Haseeb,

Thank you for the above formula. Unfortunately it has not worked due to a circular reference

Regards,

Mike

[MJDown]

Hi Haseeb,

The formula is working ... ignore my previous message.

Thanks again for your help !!!