Convert a group of formulas to array formulas at once

**coach.32** · 05-15-2015, 11:20 PM

I have a list of formulas going down one column that have a '#VALUE!' error.
I need to change them to Array formulas and I was wondering if there is a way I can highlight the formulas and then change them to array at the same time.
I tried to place the curly brackets at the beginning and end by find and replace but when I had done it, the formula showed with curly brackets, no actual answer.
Thank you for your help.

**FDibbins** · 05-15-2015, 11:46 PM

I have a list of formulas going down one column that have a '#VALUE!' error.
I need to change them to Array formulas

Why do they need to be ARRAY's?

I was wondering if there is a way I can highlight the formulas and then change them to array at the same time.

I dont think so. and you cannot type in the curly brackets, or use F/R, you need to do the 1st formula, using CTRL SHIFT ENTER (Not just enter), then copy/paste down.

I am curious as to why you feel you need array formulas though?

**coach.32** · 05-16-2015, 01:32 AM

Thank you for the answer.
Ithought that was the case.
The formula that needed to be array is as follows
=MAX(FREQUENCY(IF($K2:$K305="W",ROW($K2:$K305)),IF($K2:$K305<>"W",ROW($K2:$K305))))

**Glenn Kennedy** · 05-16-2015, 01:47 AM

If there is only one formula, which intentionally increments the first and last row by one every time it is copied down one row; then: no problem. Select the first one and set it with CTRL SHIFT ENTER. The curly braces will appear if you've done it right. If not, hit F2 and use CTRL SHIFT ENTERagin. Once they're there f=the formula is working and can be dragged down as far as you like.

See cut-down version attached.