How to simplify Calc formula?

asked 2018-04-11 02:46:32 +0200

TH gravatar image

updated 2018-04-11 20:11:57 +0200

LibreTraining gravatar image

In Excel, I have one formula:

=IF(AND(F287=G287,G287=H287), "-Ф-","")&
IF(AND(F287<>G287,F287<>H287,G287<>H287,
COUNTIF(F286:AP286,F287)>0,COUNTIF(F286:AP286,G287)>0,
COUNTIF(F286:AP286,H287)>0),A287-A286&"-ONE-","")&
IF(AND(F287<>G287,F287<>H287,G287<>H287,
OR(AND(COUNTIF(F286:AP286,F287)>0,COUNTIF(F286:AP286,G287)>0,
COUNTIF(F286:AP286,H287)=0),
AND(COUNTIF(F286:AP286,F287)>0,COUNTIF(F286:AP286,G287)=0,
COUNTIF(F286:AP286,H287)>0),AND(COUNTIF(F286:AP286,F287)=0,
COUNTIF(F286:AP286,G287)>0,COUNTIF(F286:AP286,H287)>0))),A287-A286&"---b","")&
IF(OR(AND(F287=G287,F287<>H287,COUNTIF(F286:AP286,F287)>0,
COUNTIF(F286:AP286,H287)>0),AND(F287=H287,F287<>G287,
COUNTIF(H286:AP286,F287)>0,COUNTIF(F286:AP286,G287)>0),
AND(F287<>G287,G287=H287,COUNTIF(F286:AP286,F287)>0,
COUNTIF(F286:AP286,G287)>0)),A287-A286&"---m-","-")

I want to shortcut that formula, or change with a shorter. Help me, please! Very thanks!

(Slightly edited for readability by @Lupp.)

edit retag flag offensive close merge delete

Comments

My first reaction was to downvote the question, because it shows lack of respect to those you are addressing your question to. Do you expect people to understand the logic of a complex formula without any plain-text explanation of what does it do, and what data it operates on, etc.? It looks plain rude.

But possibly that is just an oversight, and you would want to clarify your question, and add a sample file with data and use-case for the formula, and explain it in plain words.

Mike Kaganski gravatar imageMike Kaganski ( 2018-04-11 08:18:40 +0200 )edit
Lupp gravatar imageLupp ( 2018-04-11 11:03:33 +0200 )edit

Seems there are three different COUNTIF repeated several times, putting it on other column/row and referencing, can simplified a bit, also using named formulas for them.

m.a.riosv gravatar imagem.a.riosv ( 2018-04-12 00:32:54 +0200 )edit