I have this long formula with repeating expressions. Can I save an expression as a variable and use it to shorten the formula?
=(E2-(SUMPRODUCT(WEEKDAY(G2:OFFSET(G$33,$F$37,0))=1)+SUMPRODUCT(WEEKDAY(G2:OFFSET(G$33,$F$37,0))=5)))
This expression repeats twice.
WEEKDAY(G2:OFFSET(G$33,$F$37,0))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.
=SUMPRODUCT(VLOOKUP(WEEKDAY(G2:OFFSET(G$33,$F$37,0));{1;1|2;0|3;0|4;0|5;1|6;0|7;0};2))
or
=SUMPRODUCT(NOT(ISNA(VLOOKUP(WEEKDAY(G2:OFFSET(G$33,$F$37,0));{1|5};1;0))))
@mikekaganski
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?