Excel 2007 : Largest Variances

in Cell C1, try array formual and confirm with Ctrl+Shift+Enter instead of just enter
=LARGE(ABS(IF($A$1:$A$5000="REV",$B$1:$B$5000)),ROWS(C$1:C1))

and in Cell D1, try array formula and confirm with Ctrl+Shift+Enter instead of just enter

=LARGE(ABS($B$1:$B$5000),ROWS(D$1:D1))

and copy down

Thank you Azam for your help with the array formula. It delivers the numbers that I require but where the variance is a negative variance I need to have the actual number. In the formula above for example it has given me the value of 44917.61 for the 5th largest number. I want the number to appear 5th in the list but to be displayed as (44917.61) as it appears in the source data. I can not use LARGE formula as the top 20 numbers may also include some negative variance so would also need to use the SMALL formula. Can I use if and or functions with this array ?

Hello MJDown,

Try these.

C2, copy down.

=IFERROR(INDEX(B$2:B$5000,LOOKUP(9E300,MATCH(({"-",""}&LARGE(ABS(IF(A$2:A$5000="REV",B$2:B$5000)),ROWS(C$2:C2)))+0,B$2:B$5000,0))),"")

D2, copy down.

=IFERROR(INDEX(B$2:B$5000,LOOKUP(9E300,MATCH(({"-",""}&LARGE(ABS(B$2:B$5000),ROWS(D$2:D2)))+0,B$2:B$5000,0))),"")

Both are ARRAY FORMULAS.

Thank you Haseeb. I think we are getting there. It is pulling through now both positive and negative values. However it is only showing 8 of the top 20 values when I copy the formula down. The 8 values shown are not the top 8 but in what appear to be random places across the top 20 values. The values that are illustrated are in the correct rankings with blank spaces in the cells where the remainder of the answers should be.This is the case for both formulas. Would you know why this is the case? I have experienced this before when using INDEX and MATCH but in the past have managed to resolve the issue. On this occasion I am having no luck !!

If you can attach a sample dummy file, would be helpful. in the bottom click, Go Advanced, then bottom click Manage Attachments & upload your file.

Change H2, formula to ,

=IF($A2="REV",ROUND($B2-C2,2),ROUND($B2-C2,2))

then copy across & down. This will round the numbers to 2 decimel places.

See the attached.

Hi Haseeb,

With your help I am so near to automating my month end reporting now. Can you assist with what will hopefully be the last piece of the jigsaw which I assume may involve an array formula again.

In column A of the tab entitled "Rec 3QF to Actual Month" I have unique codes that match against column V of the "Data Sheet Mth" worksheet. This should then enable me to write an index and match formula that will extract the variance from column Z of the "Data Sheet Mth" tab and drop it in column I of the "Rec 3QF to Actual Month" tab.

My problem is I only want to drop in those variances that meet the following two criteria from the "Data Sheet Mth" worksheet. Revenue variance of £10k or more or cost/overheads variances of £5k or more

Thanks again for your help

Try this,

I8, copy down.

=SUMIFS('Data Sheet Mth'!Z$3:Z$52,'Data Sheet Mth'!V$3:V$52,A8,'Data Sheet Mth'!$X$3:$X$52,"Rev",'Data Sheet Mth'!Z$3:Z$52,">=10000")+SUM(SUMIFS('Data Sheet Mth'!Z$3:Z$52,'Data Sheet Mth'!V$3:V$52,A8,'Data Sheet Mth'!$X$3:$X$52,{"CST","OHD"},'Data Sheet Mth'!Z$3:Z$52,">=5000"))