Geek asks and answers

Excel array with Quartile function from different sheets

I am trying to calculate the values ​​of the 1st and 3rd quartiles for an array of values ​​that I have on several different sheets. The formula I use

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 cell C15 for the current sheet.

This worked fine when I calculated MAX, MIN and MEDIAN, but now, trying to calculate QUARTILE, I get "#VALUE!". although I know that all values ​​are numbers because they worked for MAX, MIN, and MEDIAN.

It seems to me that my array syntax is flawed, and this is what causes me problems, but I cannot figure out how to understand it. Thanks for any help!

+3
source share
1 answer

You cannot use multiple range syntax on different sheets. If your sheets are consistent, you can try entering a three-dimensional link as the first argument, either by entering directly, as shown below, or by selecting a cell on one sheet, and then selecting another tab with a shift.

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

More articles:


All Articles