I have data sheets that I am giving more data set area for future data if need be. I have multiple formulas that work when its using only one column thats in the range. If the range is more than one column but data is in the first column it gives an error. If data is in a second or more column within the range defined, it'll only calculate the first column.
NOTE:I have data compressed as a value and the quantity notated by a value as shown: VALUE (X) and I want to keep it that way as the data sheet is extremely large and all the values are input.
Example formulas work with a single range column:
Median
=ARRAYFORMULA(MEDIAN(TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(B18:B="",,SPLIT(IF(ISNUMBER(B18:B), B18:B&" 1", B18:B), " ()"))*1,,1)&"♦", INDEX(IF(B18:B="",,SPLIT(IF(ISNUMBER(B18:B), B18:B&" 1", B18:B), " ()"))*1,,2)),,999^99), "♦"))))
If I attempt to increase the range B18:F the formula will only work with two columns or more are filled. It will also only calculate on the first column.
The only working solution is to add each transposed column within that range MEDIAN( data here )
Example below:
=ARRAYFORMULA(MEDIAN(
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(B18:B="",,SPLIT(IF(ISNUMBER(B18:B), B18:B&" 1", B18:B), " ()"))*1,,1)&"♦", INDEX(IF(B18:B="",,SPLIT(IF(ISNUMBER(B18:B), B18:B&" 1", B18:B), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(C18:C="",,SPLIT(IF(ISNUMBER(C18:C), C18:C&" 1", C18:C), " ()"))*1,,1)&"♦", INDEX(IF(C18:C="",,SPLIT(IF(ISNUMBER(C18:C), C18:C&" 1", C18:C), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(D18:D="",,SPLIT(IF(ISNUMBER(D18:D), D18:D&" 1", D18:D), " ()"))*1,,1)&"♦", INDEX(IF(D18:D="",,SPLIT(IF(ISNUMBER(D18:D), D18:D&" 1", D18:D), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(E18:E="",,SPLIT(IF(ISNUMBER(E18:E), E18:E&" 1", E18:E), " ()"))*1,,1)&"♦", INDEX(IF(E18:E="",,SPLIT(IF(ISNUMBER(E18:E), E18:E&" 1", E18:E), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(F18:F="",,SPLIT(IF(ISNUMBER(F18:F), F18:F&" 1", F18:F), " ()"))*1,,1)&"♦", INDEX(IF(F18:F="",,SPLIT(IF(ISNUMBER(F18:F), F18:F&" 1", F18:F), " ()"))*1,,2)),,999^99), "♦"))))
This works for Median, Mode, Standard Deviation, and Mean Deviation. However, it absolutely will not work on IQR. I am unsure why.
IQR
=ARRAYFORMULA(QUARTILE(TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(B18:B="",,SPLIT(IF(ISNUMBER(B18:B), B18:B&" 1", B18:B), " ()"))*1,,1)&"♦", INDEX(IF(B18:B="",,SPLIT(IF(ISNUMBER(B18:B), B18:B&" 1", B18:B), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(C18:C="",,SPLIT(IF(ISNUMBER(C18:C), C18:C&" 1", C18:C), " ()"))*1,,1)&"♦", INDEX(IF(C18:C="",,SPLIT(IF(ISNUMBER(C18:C), C18:C&" 1", C18:C), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(D18:D="",,SPLIT(IF(ISNUMBER(D18:D), D18:D&" 1", D18:D), " ()"))*1,,1)&"♦", INDEX(IF(D18:D="",,SPLIT(IF(ISNUMBER(D18:D), D18:D&" 1", D18:D), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(E18:E="",,SPLIT(IF(ISNUMBER(E18:E), E18:E&" 1", E18:E), " ()"))*1,,1)&"♦", INDEX(IF(E18:E="",,SPLIT(IF(ISNUMBER(E18:E), E18:E&" 1", E18:E), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(F18:F="",,SPLIT(IF(ISNUMBER(F18:F), F18:F&" 1", F18:F), " ()"))*1,,1)&"♦", INDEX(IF(F18:F="",,SPLIT(IF(ISNUMBER(F18:F), F18:F&" 1", F18:F), " ()"))*1,,2)),,999^99), "♦")), 3)-QUARTILE(TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(B18:B="",,SPLIT(IF(ISNUMBER(B18:B), B18:B&" 1", B18:B), " ()"))*1,,1)&"♦", INDEX(IF(B18:B="",,SPLIT(IF(ISNUMBER(B18:B), B18:B&" 1", B18:B), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(C18:C="",,SPLIT(IF(ISNUMBER(C18:C), C18:C&" 1", C18:C), " ()"))*1,,1)&"♦", INDEX(IF(C18:C="",,SPLIT(IF(ISNUMBER(C18:C), C18:C&" 1", C18:C), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(D18:D="",,SPLIT(IF(ISNUMBER(D18:D), D18:D&" 1", D18:D), " ()"))*1,,1)&"♦", INDEX(IF(D18:D="",,SPLIT(IF(ISNUMBER(D18:D), D18:D&" 1", D18:D), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(E18:E="",,SPLIT(IF(ISNUMBER(E18:E), E18:E&" 1", E18:E), " ()"))*1,,1)&"♦", INDEX(IF(E18:E="",,SPLIT(IF(ISNUMBER(E18:E), E18:E&" 1", E18:E), " ()"))*1,,2)),,999^99), "♦")),
TRANSPOSE(SPLIT(QUERY(REPT( INDEX(IF(F18:F="",,SPLIT(IF(ISNUMBER(F18:F), F18:F&" 1", F18:F), " ()"))*1,,1)&"♦", INDEX(IF(F18:F="",,SPLIT(IF(ISNUMBER(F18:F), F18:F&" 1", F18:F), " ()"))*1,,2)),,999^99), "♦")), 1))
I've attempted to try to make the range smaller by using {B18:B,C18:C,D18:D,E18:E,F18:F} with neither working unless its a single column B18:B.
Can the formula look inside a range that is more than one column where the rest of the columns are blank? Essentially making the formula smaller.
Why does IQR not use all the data in each range as a hole to calculate the IQR. (The data returns 6 values instead of 2)
Thank you.
https://docs.google.com/spreadsheets/d/1byR6Aw3lTImxNHDynTm5S1MWjpxmCuMyNl5WT9PcC1E/edit?usp=sharing
