CODEWITHSUNDEEP
excel
Rex
Rex

Reputation: 2147

How to overcome the max length limit of a formula in Excel? a bug of excel?

There are at least two forms of formula in Excel. One is used for cells, and the other is used for data validation. The max length limit for the formula to do data validation is about only 210 chars.

Here is a problem I am trying to solve. Given a data set with categories and values

category   value1   value2
a           1.0     ...
a           2.0
a           1.0
a           3.0
b           1.0
b           5.0
b           2.0     ...
...

I want to validate these values by checking if the value change from the row above is within one sigma deviation of its category. That means we need to skip the first row of each category.

Here is what I tried:

The following formula works for cells of each category beginning from the second row to the last row of its category.

=INDIRECT(ADDRESS(ROW(), COLUMN())) - INDIRECT(ADDRESS(ROW()-1, COLUMN())) < 
1.0*STDDEV.P(INDIRECT(ADDRESS(MATCH(INDIRECT("A" & ROW()), $A:$A, 0), COLUMN()) & ":" &ADDRESS(MATCH(INDIRECT("A"&ROW()),$A:$A, 1), COLUMN())))

However, the following doesn't work because of the max length limit of formula in excel - just add IF(formula_above, True, False):

=IF(INDIRECT(ADDRESS(ROW(), COLUMN())) - INDIRECT(ADDRESS(ROW()-1, COLUMN())) < 
1.0*STDDEV.P(INDIRECT(ADDRESS(MATCH(INDIRECT("A" & ROW()), $A:$A, 0), COLUMN()) & ":" &ADDRESS(MATCH(INDIRECT("A"&ROW()),$A:$A, 1), COLUMN()))), TRUE, FALSE)

This can work if one input the formula into a cell, but it doesn't work for the refer-to formula of Data Validation.

To work with all cells all rows (without need to manully skip the first row of each category), I wrote following formula for data validation. But it gives "The FOrmula currently evaluates to an error..." because of the max length limit of the excel. 

=IF(MATCH(INDIRECT("A" & ROW()), $A:$A, 0) = ROW(), TRUE,
INDIRECT(ADDRESS(ROW(), COLUMN())) - INDIRECT(ADDRESS(ROW()-1, COLUMN())) < 
1.0*STDDEV.P(INDIRECT(ADDRESS(MATCH(INDIRECT("A" & ROW()), $A:$A, 0), COLUMN()) & ":" &ADDRESS(MATCH(INDIRECT("A"&ROW()),$A:$A, 1), COLUMN()))))

Although Microsoft claims that the length limit of formula of excel is "8192", it seems not true: https://support.office.com/en-nz/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

Update:

Here is another try for the formula of "Data Validation":

=IF($A2<>$A1, TRUE, abs(B2-B1)<Stdev.P(offset(indirect(address(match($A2,$A:$A,0), column())), 0,0,countif($A:$A, $A2,1)))

It is strange that the True/False value of the formula above is correct when I input in a cell, but when I input it in the "Data Validation" ->Custom -> Formula. The result is completely wrong. It always gives False.

Upvotes: 3

Views: 25317

Answers (1)

BrakNicku
BrakNicku

Reputation: 5990

In Excel 2010 you can enter max 255 chars in data validation formula.

You can simplify your formula to fit the limits.

Instead of using:

INDIRECT(ADDRESS(ROW(), COLUMN()))

You can simply use cell relative address.

Select e.g. B2:B50 and enter validation formula:

=IF($A2<>$A1,TRUE,B2-B1<STDEV.P(IF($A:$A=$A2,B:B)))

This should be equivalent to your formula.

within one sigma deviation of its category

I think you may need to use ABS(B2-B1) instead of B2-B1.

EDIT:

Your screenshot to prove that my formula is not working as expected:

screenshot

Please enter the same formula, but confirm it with Ctrl+Shift+Enter. Now it's an array formula, with different result (10).

You can't confirm formula entered in data validation field with Ctrl+Shift+Enter, but you don't need to do it. Excel parses every formula in data validation as an array formula.

Below is the screenshot of sample data with data validation applied:

enter image description here

On the right you can see a table with standard deviation of all cells, and each category. Circle invalid cells option highlights cells that do not meet criteria. As you can see different deviation is applied to each category.

Upvotes: 4

Related Questions