Excel array with Quartile function from different sheets

Question

I'm trying to calculate the 1st and 3rd Quartile values for an array of values I have across a couple of different sheets. The formula I am using now is

QUARTILE((C15,'Week 05-13 to 05-19'!C15,'Week 05-06 to 05-12'!C15,'week 04-29 to 05-04'!C15), 1)

where "Week 05-13 to 05-19", "Week 05-06 to 05-12", and "week 04-29 to 05-04" are different sheets and "C15" refers to the cell C15 on the current sheet.

This worked fine when I was calculating MAX, MIN, and MEDIAN, but now trying to compute QUARTILE, I get a "#VALUE!" error even though I know all the values are numbers because it worked for MAX, MIN, and MEDIAN.

I feel like my array syntax is messed up and that is what is causing me problems, but I can't seem to figure it out. Thanks for any help!

Answer 1

You can't use the multiple range syntax on different sheets. If your sheets are consecutive you can try entering a 3D reference as the first argument either by entering directly as below or by selecting a cell on one sheet and then selecting another tab with shift.

=QUARTILE('week 04-29 to 05-04:Week 05-20 to 05-26'!C15, 1)