Change ARRAY formula to minimize excel processing

**swampedindata** · 02-17-2012, 09:33 AM

={MAX(IF('IDT Data'!C:C='2STAR'!C2,IF('IDT Data'!I:I="P",'IDT Data'!F:F)),IF('IDT Data'!C:C='2STAR'!C2,IF('IDT Data'!I:I="R",'IDT Data'!F:F)))}

Above formula does a nice job of pulling the lastest date a person was piad, but is there a way to not use an ARRAY formula, as it is totally locking up excel anytime you change something on the spreadsheet.

**Andrew-R** · 02-17-2012, 09:39 AM

Can you post an example workbook?

It might help to stop using entire columns as your references as well.

**swampedindata** · 02-17-2012, 09:57 AM

Won't let me upload my file for some reason.
This is an example of the page that the formula is referencing.
RANK NAME UIC "FiscalYear" "EventDate" "DrillCode" "UTAPeriod" "Attend.Code"
SGT xxxxxxxx WZN7A3 2011 1-Feb-11 61 1 P
SGT xxxxxxxx WZMSA2 2011 1-Feb-11 91 1 P
COL xxxxxxxx WR79AA 2011 2-Feb-11 11 2 R

**OnErrorGoto0** · 02-17-2012, 10:18 AM

I think you would be better off:
1. Creating a key column combining columns C and I and using that in one IF test
2. Restricting the ranges as Andrew said
3. Posting in the right forum as this is neither tip nor tutorial.

**swampedindata** · 02-17-2012, 10:54 AM

I apologize for being in wrong forum, will place all future posts in General Forum.

I don't want to combine any columns as I have to download the data from another source, and the workbook is so large now, any changes usually lock it up.

I will turn into a TABLE, and name the ranges, that might help, but not sure my CPU will let me?
Thanks.