Sep 12 2023 07:20 AM
Hello, is there a shorter way to do such repetitive calculations?
ı havent office 365.
Sep 12 2023 07:37 AM
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<>"")))
Sep 12 2023 08:04 AM
Sep 12 2023 08:05 AM
Just started with it the same way, but didn't receive correct result. Could you please repeat formula in English?
Sep 12 2023 08:07 AM
SolutionWorkbook attached. Old formula in column D, new formula in column E
Sep 12 2023 08:11 AM
Sep 12 2023 08:14 AM
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<>"")
)
)
Sep 12 2023 08:17 AM
Sep 12 2023 09:02 AM - edited Sep 12 2023 09:07 AM
@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<>""))
Sep 12 2023 11:51 AM
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.
Sep 12 2023 12:29 PM
Sep 12 2023 12:32 PM
Sep 12 2023 01:08 PM
@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.
Sep 13 2023 02:55 AM
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.
Sep 13 2023 05:37 AM
@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
Sep 13 2023 06:47 AM - edited Sep 13 2023 06:52 AM
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.
Sep 13 2023 07:18 AM - edited Sep 13 2023 07:25 AM
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\ ...
Sep 13 2023 07:46 AM - edited Sep 13 2023 07:49 AM
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.
Sep 13 2023 11:57 AM
Sep 14 2023 02:20 AM - edited Sep 14 2023 02:22 AM
@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.