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]

 

[Marcelo Branco]

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

You are welcome. Thanks for the feedback.

M.

 

[SpillerBD]

You could easily get away from the Array formula with a pivot Table using its MAX feature too.

 

[Leo Skywalker]

If stil not late, and your version of Excel is 2010 or later, you can use:

=AGGREGATE(14,6,($G$5:$G$9000)/($C$5:$C$9000=J8),1)