Ask Your Question
0

simplify formula

asked 2020-04-03 00:22:44 +0200

Math3333 gravatar image

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 flag offensive 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.

m.a.riosv gravatar imagem.a.riosv ( 2020-04-03 10:09:20 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2020-04-03 11:01:48 +0200

updated 2020-04-03 13:26:15 +0200

=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))))

edit flag offensive delete link more

Comments

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

Math3333 gravatar imageMath3333 ( 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.

Mike Kaganski gravatar imageMike Kaganski ( 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..

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

Localization beyond the absolutely necessary is an absolute evil!
See also: https://ask.libreoffice.org/en/questi...

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

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

Math3333 gravatar imageMath3333 ( 2020-04-03 14:56:26 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-04-03 00:22:44 +0200

Seen: 38 times

Last updated: Apr 03