Is there a solution to using the same function multiple times?

[deniztopcu]

Hello, is there a shorter way to do such repetitive calculations?


ı havent office 365.

[#2]

@deniztopcu 

Somewhat shorter:

=TOPLA(TOPLA.ÇARPIM((B9<J9:AK9*{1;1,2;1,4;1,6;1,8;2})*(($J$8:$AK$8="QoQ")+($J$8:$AK$8="QoQ TTM"))*(J9:AK9<>""))-TOPLA.ÇARPIM((B9>J9:AK9*{1;1,2;1,4;1,6;1,8;2})*(($J$8:$AK$8="QoQ")+($J$8:$AK$8="QoQ TTM"))*(J9:AK9<>""))+
TOPLA.ÇARPIM((C9<J9:AK9*{1;1,2;1,4;1,6;1,8;2})*(($J$8:$AK$8="YoY")+($J$8:$AK$8="YoY TTM"))*(J9:AK9<>""))-TOPLA.ÇARPIM((C9>J9:AK9*{1;1,2;1,4;1,6;1,8;2})*(($J$8:$AK$8="YoY")+($J$8:$AK$8="YoY TTM"))*(J9:AK9<>"")))

[#3]

I was excited when I saw the fiction. But he made an error.
#NO

[#4]

@Hans Vogelaar 

Just started with it the same way, but didn't receive correct result. Could you please repeat formula in English?

[#5 · Accepted solution]

@deniztopcu 

Workbook attached. Old formula in column D, new formula in column E

[#6]

Thanks for your efforts.
It was very stylish.

[#7]

@Hans Vogelaar 

Thanks, found my mistake. Here is just formatting

=SUM(
    SUMPRODUCT(
        (B9<J9:AK9*{1;1.2;1.4;1.6;1.8;2})*
        (($J$8:$AK$8="QoQ")+($J$8:$AK$8="QoQ TTM"))*
        (J9:AK9<>"")
    )-
    SUMPRODUCT(
        (B9>J9:AK9*{1;1.2;1.4;1.6;1.8;2})*
        (($J$8:$AK$8="QoQ")+($J$8:$AK$8="QoQ TTM"))*
        (J9:AK9<>"")
    )+

    SUMPRODUCT(
        (C9<J9:AK9*{1;1.2;1.4;1.6;1.8;2})*
        (($J$8:$AK$8="YoY")+($J$8:$AK$8="YoY TTM"))*
        (J9:AK9<>"")
    )-
    SUMPRODUCT(
        (C9>J9:AK9*{1;1.2;1.4;1.6;1.8;2})*
        (($J$8:$AK$8="YoY")+($J$8:$AK$8="YoY TTM"))*
        (J9:AK9<>"")
    )
)

[#8]

Thank you too.

[#9]

@deniztopcu alternatively:

=SUMPRODUCT(SIGN(J9:AK9*{1;1.2;1.4;1.6;1.8;2}-
                 SWITCH(LEFT($J$8:$AK$8,3),"QoQ",B9,"YoY",C9))
            *(J9:AK9<>""))

[#10]

Congratulations folks.  I am hugely impressed by the pattern recognition skills and the manner in which the monster formula has been reduced.  I have not got the remotest idea what the result signifies but if it is meaningful to the OP, that's OK.

I realised I haven't used the old workhorse SUMPRODUCT for many years now, either as a SUMIFS predecessor or as an array wrapper function.  I did start on the problem this morning and got as far as

before coming to the conclusion that there was going to be no great meeting of minds ever were I to produce the results.  

[#11]

Thank you for your answer, but I do not have Office 365. I don't have the opportunity to try it, but I think this should be the shortest formula.

[#12]

Like you, I think there were very good reactions. Thanks

[#13]

@deniztopcu Actually this should work going back to 2019 but if you have older maybe try this:

=SUMPRODUCT(SIGN(J9:AK9*{1;1.2;1.4;1.6;1.8;2}-
                 IF(LEFT($J$8:$AK$8,3)="QoQ",B9,C9))
            *(J9:AK9<>""))

because of SUMPRODUCT I don't think you should need to use CTRL-SHIFT-ENTER.  Unfortunately I can't test on older versions.

[#14]

@mtarler 

I realized there was a logical error in my question.

@Hans Vogelaar 's
I come to the same conclusion as the answer but it's your calculation. It came out wrong because of my mistake.

[#15]

@deniztopcu alternatively:

=IF($A9="","", SUMPRODUCT(($J9:$AK9<>"")*IF(LEFT($J$8:$AK$8,3)="QoQ",
         ($B9<$J9:$AK9*{1;-0.9;-0.8;-0.7;-0.6;-0.5})-($B9>$J9:$AK9*{1;1.1;1.2;1.3;1.4;1.5}),
         ($C9<$J9:$AK9*{1;-0.9;-0.8;-0.7;-0.6;-0.5})-($C9>$J9:$AK9*{1;1.1;1.2;1.3;1.4;1.5}))))

however I wonder why the <J9:AK9 starts with 1 instead of -1

[#16]

@mtarler 

I noticed when you said there was another mistake.
1,5 \ 1,4 \ 1,3 \ 1,2 \ 1,1 \ 1 \ -0,1 \ -0,2 \ -0,3 \ -0,4 \ -0,5

The numbers had to be in this arrangement logic.

[#17]

Sorry but that logic still doesn't make sense to me but I really don't even know what that logic is trying to find. Maybe if you explain what you are trying to find/calculate we could help with that and not just how to make your formula more concise. Just as a shot in the dark I would expect something more like:
1,5 \ 1,4 \ 1,3 \ 1,2 \ 1,1 \ 1 and then -1\ -1,1 \ -1,2 \ -1,3 \ -1,4 \ -1,5

EDIT:  so looking at the numbers I see positive numbers going >100% while negative numbers do not so I'm guessing this a measure of change and the actual value can't go (or extremely unlikely to go) negative to a negative % change would never exceed -100% (i.e. goes to 0).  I still don't know what values/cases you are trying to 'pull out' but be < case might then be 0\ -0,1\ -0,2\ ...

[#18]

@mtarler 

I should compare the price change in the relevant period with each growth percentage and if it increased by more than the growth percentage I should get -1 (for each rate) point, if it increased less than the growth percentage I should get +1 (for each rate) point. This is the logic. 

In its simplest form, it checks > and < for each growth percentage.

(TOPLA.ÇARPIM((B9<K9:AL9)*(($K$8:$AL$8="QoQ")+($K$8:$AL$8="QoQ TTM"))*(K9:AL9<>""))-TOPLA.ÇARPIM((B9>K9:AL9)*(($K$8:$AL$8="QoQ")+($K$8:$AL$8="QoQ TTM"))*(K9:AL9<>""))+
TOPLA.ÇARPIM((C9<K9:AL9)*(($K$8:$AL$8="YoY")+($K$8:$AL$8="YoY TTM"))*(K9:AL9<>""))-TOPLA.ÇARPIM((C9>K9:AL9)*(($K$8:$AL$8="YoY")+($K$8:$AL$8="YoY TTM"))*(K9:AL9<>""))

I then want to do an additional +/- point check to check how much the price is above/below the growth percentage.
And as you said, here is the real problem. Things get complicated when negative numbers are involved.

[#19]

this 'analysis' is still a mystery to me. I think you are trying to give 'points' for being above or below threshold and 1pt for just above/below and 2pts for a bit more and so on for a max of 5pts being really far extreme and then taking the difference between the above and below. but please correct or clarify if you could.
that all said the whole -.9 or -.1 products are still concerning as I don't know they are doing what you think. remember multiplying by -1 on each side of an inequality reverses the inequality to consider if -B9>K9:AL9*{-1, +0.1, +0.2 ....} makes sense to what you want.

[#20]

@mtarler 
Restructuring is what I want to do. I hope I can explain what I want to do this time. with 3 different ways

I made the calculations only with the "QoQ and QoQ TTM" headings.
If everything is correct, I will need to add the "YoY and YoY TTM" headers to the calculation.