test if cell contains a matrix formula

**erikpelgrum** · 09-15-2009, 07:09 AM

I would like to check ( for instance through conditional formatting) if a cell contains a matrix formula or not.

Sometimes it happens that you forget to press Ctrl+shift+enter after editing a formula.
This doesn't really show in your sheet and it makes it easy to get unreliable results.

Therefore I would like to create an alerter

Thanks for your help!!

**Ron Coderre** · 09-15-2009, 08:46 AM

Excel can't determine if you "need" an array formula or not.
Consequently, I would suggest one of these approaches:

Try to avoid array formulas by using one of the usual alternatives:
Examples:
This array formula:
=SUM(IF(C2:F5>2,1,0))

can be replaced with this regular formula:
=SUMPRODUCT(--(C2:F5>2))

and...
This array formula:
=MAX(MATCH(B2:B5,H2:H5,1))

can be replaced with this regular formula:
=MAX(INDEX(MATCH(B2:B5,H2:H5,1),0))

But, if you must have an array formula that others may edit
...here's a method I use to remind them:
=SUM(IF(C2:F5>2,1,0))+N("remember to Ctrl Shift Enter")
=MAX(MATCH(B2:B5,H2:H5,1))+N("remember to Ctrl Shift Enter")

Does that help?

**erikpelgrum** · 09-24-2009, 03:54 AM

Thanks Ron,

What I really need is just to be able test if a cell currently contains an arry formula which has been activated with shift-ctrl-enter.

When I am making some reports on a large list of data I might just accidentely forget to press those keys after editing a formula.

This does not always show in the result but it would be really nice to just see at a glance which cells contain an active array formula just by the color of them for instance.

Any suggestions?

Erik

**DonkeyOte** · 09-24-2009, 04:20 AM

it would be really nice to just see at a glance which cells contain an active array formula just by the color of them for instance.

One approach would be to use a UDF... eg:

Please Login or Register  to view this content.

Then create a Conditional Format rule, highlighting your selection and applying the rule of

Please Login or Register  to view this content.

**sweep** · 09-24-2009, 04:23 AM

Or you could do it with conditional formatting.

Define a "named range" as

=GET.CELL(49,INDIRECT("rc",FALSE))

and call it something like ArrayPresent

Then use conditional formatting to highlight cells where the formula

=ArrayPresent

is true

**DonkeyOte** · 09-24-2009, 04:29 AM

Just quick note to add that GET.CELL is XLM4 (VBA).... per Chip Pearson quicker than UDF.

**erikpelgrum** · 10-26-2009, 05:39 AM

Thanks all,

the "hasarryformula" works like a charm!

Taking it one step further it would be helpfull to test hom many cells ( if any):
Do not have an array formula in them
but
do have a formula that starts with " sum(if( "

Thus testing if there are any cells in a sheet where "shift-ctrl-enter" should have been pressed but was not....

Thanks again!!

Erik