Locking the Curly Brackets on an Array formula

[cmolson0513]

Hello,

I have a spreadsheet that has the electric usage for every hour for the entire year. I am calculating the maximum electric usage for every month and I am using this formula for the month of January (cell K9): {=MAX(IF($C$6:$C$8765=$J9,$G$6:$G$8765))} and then dragging cell K9 down to K20 to autofill the table. This formula calculates the maximum electric value for each month correctly. I tried to recreate the spreadsheet below as a reference. There are 8,760 hours in a year which is how long the selection in for columns C and G.

The problem I am having is that whenever I click any of the cells K9-K20 and click out (without hitting ctrl+Shift+Enter) the brackets go away and the formula no longer works correctly because it is no longer an array formula. Is there a way to lock in those brackets so they don't disappear if I click in and out of the cell without ctrl+shift+enter? This problem isn't really for me, but when other people with less Excel experience try to use my spreadsheet and they are confused when the formula is no longer working. Although this wouldn't be much of an issue for a simple spreadsheet like this example, it has become a common occurrence when sharing much more complicated spreadsheets.

I've tried searching for an answer to this without any luck, so any help would be greatly appreciated!

[TABLE="class: grid, width: 841"]
<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]Date and Time[/TD]
[TD]Month[/TD]
[TD]Day Type[/TD]
[TD]Day[/TD]
[TD]Hour[/TD]
[TD]kW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Elec[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]1/1/2016 1:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]1/1/2016 2:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]1/1/2016 3:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1.2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Month[/TD]
[TD]Monthly Maximum (kW)[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]1/1/2016 4:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]15.0[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]1/1/2016 5:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb[/TD]
[TD]13.0[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]1/1/2016 6:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar[/TD]
[TD]16.6[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]1/1/2016 7:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD]19.1[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]1/1/2016 8:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]0.4[/TD]
[TD][/TD]
[TD][/TD]
[TD]May[/TD]
[TD]16.9[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD]1/1/2016 9:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD]22.8[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]1/1/2016 10:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD]35.3[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]1/1/2016 11:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]11[/TD]
[TD]1.2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Aug[/TD]
[TD]31.1[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD]1/1/2016 12:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sep[/TD]
[TD]31.1[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD]1/1/2016 13:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]13[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Oct[/TD]
[TD]17.5[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD]1/1/2016 14:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]14[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov[/TD]
[TD]30.7[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]1/1/2016 15:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]15[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Dec[/TD]
[TD]13.8[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD]1/1/2016 16:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]16[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD]1/1/2016 17:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]17[/TD]
[TD]1.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD]1/1/2016 18:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]18[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD]1/1/2016 19:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]19[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD]1/1/2016 20:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD]1/1/2016 21:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]21[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD]1/1/2016 22:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]22[/TD]
[TD]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD]1/1/2016 23:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]23[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD]1/1/2016 0:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]1[/TD]
[TD]24[/TD]
[TD]1.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD]1/2/2016 1:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD]1/2/2016 2:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD]1/2/2016 3:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD]1/2/2016 4:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD]1/2/2016 5:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD]1/2/2016 6:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD]1/2/2016 7:00[/TD]
[TD]Jan[/TD]
[TD]Hol[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD="align: right"]8764[/TD]
[TD][/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl66, width: 113"]12/31/2016 22:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Dec[/TD]
[TD]Sun[/TD]
[TD]31[/TD]
[TD]23[/TD]
[TD]0.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8765[/TD]
[TD][/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl66, width: 113"]12/31/2016 23:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Dec[/TD]
[TD]Sun[/TD]
[TD]31[/TD]
[TD]24[/TD]
[TD]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[yky]

Instead of copying down the rows, you select the whole range and apply the array formula. This way, Excel will not allow you to change the formula of just one cell. If you want to change the formula, you'll have to change the formula in the entire range.

 

[cmolson0513]

Instead of copying down the rows, you select the whole range and apply the array formula. This way, Excel will not allow you to change the formula of just one cell. If you want to change the formula, you'll have to change the formula in the entire range.

Thanks for the response, yky. I have tried doing that but I may be doing something wrong. To do it with the method you mentioned, I changed the formula to look like this: {=MAX(IF($C$6:$C$8765=$J$9:$J$20,$G$6:$G$8765))} - (I changed what used to be $J9 to $J$9:$J$20) However, when I select the entire range and apply the array formula (ctrl+shift+enter), this results in a #N/A value for every value in the K9:K20 selection. I am guessing this error is occurring because the arrays in the IF statement are different sizes. Do you see if I am doing something wrong here?

Thanks!

 

[SpillerBD]

To get out of the cell and not alter the Array formula, I use ESC key instead.

 

[cmolson0513]

To get out of the cell and not alter the Array formula, I use ESC key instead.

SpillerBD, that makes sense and that's how I usually get out of it too. The problem I'm having is that I often have to share my spreadsheets with people who don't know much about Excel. So, if they click into and out of an array formula, that will mess up the result, and they won't know how to fix it. Sometimes may not even notice that they changed the formula/value, so when the cell that they changed is referenced by other cells, they may notice that something is wrong but, again, they have no idea where the error is.

Thanks for the response!

 

[Marcelo Branco]

What Excel version are you using?
For me (Excel 2010) by only clicking in a cell with an array formula and, after, clicking in another cell, doesn't change anything. The array formula keeps the curly braces { } and works normally.
Only by clicking, putting the cursor in the Formula Bar and hitting Enter changes the formula.

M.

 

[cmolson0513]

What Excel version are you using?
For me (Excel 2010) by only clicking in a cell with an array formula and, after, clicking in another cell, doesn't change anything. The array formula keeps the curly braces { } and works normally.
Only by clicking, putting the cursor in the Formula Bar and hitting Enter changes the formula.

M.

Thanks for the clarification. When I have been saying clicking in and out of a cell, I mean clicking in a cell, putting the cursor in the Formula Bar, and clicking out of the cell.

 

[Marcelo Branco]

Thanks for the clarification. When I have been saying clicking in and out of a cell, I mean clicking in a cell, putting the cursor in the Formula Bar, and clicking out of the cell.

That's not enough to change the formula. One must also hit the Enter key.
If your users are doing this customarily i think it would be appropriate:
1. Educate them (not easy, i know )
2. Protect the worksheet

M.

 

[cmolson0513]

That's not enough to change the formula. One must also hit the Enter key.
If your users are doing this customarily i think it would be appropriate:
1. Educate them (not easy, i know )
2. Protect the worksheet

M.

In Excel 2013, if you click into the formula bar and click out without ctrl+shift+enter, that will remove the curly brackets and no longer be an array formula.

Ya, protecting the sheet may be the way to go. Thanks for the help!

 

[Marcelo Branco]

As a last alternative (not recommended), for this specific case, use a regular formula, such as:
=SUMPRODUCT(MAX(--($C$6:$C$37=$J9)*$G$6:$G$37))

But this is not always possible, so protecting the worksheet seems to me the best.

M.