# 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?

=(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))  edit retag close merge delete ## Comments 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. ( 2020-04-03 10:09:20 +0200 )edit ## 1 Answer Sort by » oldest newest most voted =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))))

more

@Mike Kaganski 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.

( 2020-04-03 12:29:22 +0200 )edit

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

( 2020-04-03 13:19:45 +0200 )edit
2

@Math3333: The formula given by @Mike Kaganski 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..

( 2020-04-03 13:21:43 +0200 )edit
2

Localization beyond the absolutely necessary is an absolute evil!

( 2020-04-03 13:23:14 +0200 )edit

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

( 2020-04-03 14:56:26 +0200 )edit