Array Forumula Largest Deals Between Date Period

[phil.williams]

I am currently trying to create a summary page from a table of data. My excel file is attached.

My formula has not been pulling the company names correctly which is my problem.

I have been using the following array formula: =IFERROR(INDEX('Master Renewal List'!$A$1:$D$420,MATCH(LARGE(IF('Master Renewal List'!$A$1:$A$420="Pending",IF('Master Renewal List'!$D$1:$D$420<F$23,IF('Master Renewal List'!$D$1:$D$420>=D$23,'Master Renewal List'!$C$1:$C$420))),ROW(1:1)),'Master Renewal List'!$C$1:$C$420,0),3),"")

The formula looks at my list of data - pulls all pending contracts and sorts them largest to smallest between the date period. Once again - all data is attached in the excel file.

The dollar amounts are pulling correctly between the time periods. The formula is finding the pending deals and sorting them largest to smallest.

However, when I use the formula to match up the company name with the dollar amount between the time periods, it is not pulling correctly. I take it the formula is matching up the company name with the first correct value it finds - be it pending, renewed, or terminated - regardless of the time period.

For example, in November 09 - the 2nd largest deal open is 17,000. This is being pulled correctly. However, the Company name the formula associates that with is Company 11 (as that is the first company on the list with a 17k value) when it should in fact be Company 136

Unfortunately, I am somewhat limited to how much freedom i have with this file as it is a shared file being updated by various users with various versions of Excel (2003, 2007, Mac 2008)

Any help would be very appreciated!

[DonkeyOte]

If 2003 compatibility is important you must dispense with use of IFERROR given this is not backwards compatible with versions prior to XL2007.

I confess having looked at this I would approach slightly differently given you must also account for potential for duplicity of values.

If we can assume that the decimals in the Master sheet are of no real consequence in terms of reporting (eg 1802.72) then I would be so bold as to suggest you do the following:

'Master Renewal List'!F2:
=ROUND(C2,0)+(ROWS(C$2:C2)/1000000)
copied down to F420

Then, using the above value we can set up the remainder - hopefully with more ease than before:

Tracking!B24:
=IF(INT(C24),INDEX('Master Renewal List'!$B$2:$B$420,ROUND(MOD(C24,1)*1000000,0)),"")
copied down to B29

Tracking!C24:
=LOOKUP(9.99E+307,CHOOSE({1,2},0,LARGE(IF(('Master Renewal List'!$A$2:$A$420="Pending")*(('Master Renewal List'!$D$2:$D$420-DAY('Master Renewal List'!$D$2:$D$420)+1)=B$23),'Master Renewal List'!$F$2:$F$420),ROWS(C$24:C24))))
confirmed with CTRL + SHIFT + ENTER
copied down to C29

Apply a Custom Number Format to C24:C29 of: $#,##0;;;
(this will hide the default 0's)

B24:C29 can be copied and applied across D24:I29

You can copy B24:I29 to the other blocks but you must adjust the row based values (eg C$24:C24, B$23) accordingly (eg C$36:C36, C$35 etc...)

[sailepaty]

One more option,

B24:=IF(C24,INDEX('Master Renewal List'!$B$2:$B$420,MATCH(C24,INDEX(('Master Renewal List'!$A$2:$A$420="Pending")*('Master Renewal List'!$D$2:$D$420>=B$23)*('Master Renewal List'!$D$2:$D$420<D$23)*('Master Renewal List'!$C$2:$C$420),0),0)),"")

Copy down to B29

C24=LARGE(INDEX(('Master Renewal List'!$A$2:$A$420="Pending")*('Master Renewal List'!$D$2:$D$420>=B$23)*('Master Renewal List'!$D$2:$D$420<D$23)*('Master Renewal List'!$C$2:$C$420),0),ROWS(C$24:C24))

Copy down to C24

Now follow DonkeOte's recomendations.

Apply a Custom Number Format to C24:C29 of: $#,##0;;;
(this will hide the default 0's)

B24:C29 can be copied and applied across D24:I29

You can copy B24:I29 to the other blocks but you must adjust the row based values (eg C$24:C24, B$23) accordingly (eg C$36:C36, C$35 etc...)

Regards

[DonkeyOte]

FWIW - that approach will not handle duplicity of values should they exist hence the suggestion of a "unique" key (of sorts) on Master Renewal Sheet.

(the LARGE would generate #NUM! also if left un-handled)

Given the data is sorted by data I'm sure with more thought a more efficient approach could be utilised.....

[DonkeyOte]

Belatedly, it dawned on me that in terms of making the formula easier to adapt to the various blocks without necessitating edits - risky given Array - I figured you could amend C24 from my original suggestion to:

=LOOKUP(9.99E+307,CHOOSE({1,2},0,LARGE(IF(('Master Renewal List'!$A$2:$A$420="Pending")*(('Master Renewal List'!$D$2:$D$420-DAY('Master Renewal List'!$D$2:$D$420)+1)=LOOKUP(9.99E+307,B$23:B23)),'Master Renewal List'!$F$2:$F$420),ROWS(B$23:B24)-MATCH(9.99E+307,B$23:B23))))

confirmed with CTRL + SHIFT + ENTER

Then copy the above to C29 (B24:B29 as before)

Once done you can simply copy B24:C29 over range D24:I29

Once that block is done you can copy B24:I29 over B36:I41 and B47:I52 etc without need for edits.

[sailepaty]

FWIW - that approach will not handle duplicity of values

To fix that issue.

B24=IF(C24,INDEX('Master Renewal List'!$B$2:$B$420,MATCH(C24,INDEX(('Master Renewal List'!$A$2:$A$420="Pending")*('Master Renewal List'!$D$2:$D$420>=B$23)*('Master Renewal List'!$D$2:$D$420<D$23)*(('Master Renewal List'!$C$2:$C$420)+ROW('Master Renewal List'!$C$2:$C$420)/1000000000),0),0)),"")

C24=LARGE(INDEX(('Master Renewal List'!$A$2:$A$420="Pending")*('Master Renewal List'!$D$2:$D$420>=B$23)*('Master Renewal List'!$D$2:$D$420<D$23)*(('Master Renewal List'!$C$2:$C$420)+ROW('Master Renewal List'!$C$2:$C$420)/1000000000),0),ROWS(C$24:C24))

Sample attached

Regards

[DonkeyOte]

sailepaty, rest assured I wasn't implying you could not do it (either personally or in general ) I was merely trying to illustrate to OP that by adding a very basic helper calc you could halve the number of arrays being processed.
(in theory you could do the same with yours if you extract the appropriate value from the MOD of the LARGE - this is giving you the requisite row for Column B index.)

(edit: you should really still account for the possibility of #NUM! errors however in the initial LARGE call)

[sailepaty]

sailepaty, rest assured I wasn't implying you could not do it (either personally or in general )

That's fine, I understand your point. Sometimes I don't extend my comments because I'm afraid to make a mistake since English is not my first language.

you should really still account for the possibility of #NUM! errors however in the initial LARGE call)

I think in this case there is not risk because LARGE only returns #NUM! when they are not values at all, zeros are ok.

Regards.

[DonkeyOte]

Agreed, if k exceeds the number of numerics in the preceding array of values you will get #NUM! and I would say that's a possibility here, no ?
(eg Aug-09)

[sailepaty]

You're right, then I'll change my formula for this.

A22=COUNT('Master Renewal List'!$D$2:$D$420)

C24=IF(ROWS(C$24:C24)<=$A$22,LARGE(INDEX(('Master Renewal List'!$A$2:$A$420="Pending")*('Master Renewal List'!$D$2:$D$420>=B$23)*('Master Renewal List'!$D$2:$D$420<D$23)*(('Master Renewal List'!$C$2:$C$420)+ROW('Master Renewal List'!$C$2:$C$420)/1000000000),0),ROWS(C$24:C24)),0)


Reagards

[DonkeyOte]

In reality I misread your initial formula - I failed to notice the embedded INDEX as opposed to IF ... so my point wasn't valid.