Simplify formula

I have this long formula with repeating expressions. Can I save an expression as a variable and use it to shorten the formula?


This expression repeats twice.


Please attach a sample file, maybe someone finds a better formula to do it. OFFSET() it’s a volatile function if it is used a lot, it can slowdown a bit the spreadsheet.




I am getting Err 502. I have looked at the VLOOKUP help page. It says “column number doesn’t exist in the selected table, you would get Error 502.” I am not sure to what column number they refer.

This might mean that your configuration uses different array column and array row separators than ; and | used in my sample.

@Math3333: The formula given by @mikekaganski has the ‘InlineConstantArray’ {1;1|2;0|3;0|4;0|5;1|6;0|7;0} using the semicolon as the column separator inside of a row and the “pipe” character “|” as the separator between rows. This is a good setting, but it may be in conflict with your own settings. Check >Options>LibreOffice Calc>Formula>>Separators…

Localization beyond the absolutely necessary is an absolute evil!
See also: Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?

@Lupp, yes! It was the separators setting mismatch. thank you!