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!

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

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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)