SOLVED

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

Brass Contributor

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


ı havent office 365.Ekran görüntüsü 2023-09-12 165816.png

19 Replies

@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<>"")))

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

@HansVogelaar 

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

best response confirmed by deniztopcu (Brass Contributor)
Solution

@deniztopcu 

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

Thanks for your efforts.
It was very stylish.

@HansVogelaar 

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

@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<>""))

 

 

 

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

image.png

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

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.
Like you, I think there were very good reactions. Thanks

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

@mtarler 

I realized there was a logical error in my question.

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

deniztopcu_0-1694598629960.png

 

@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

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

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

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

deniztopcu_0-1694616580510.png

 

 

 

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.

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

deniztopcu_0-1694683199330.png

 

1 best response

Accepted Solutions
best response confirmed by deniztopcu (Brass Contributor)
Solution

@deniztopcu 

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

View solution in original post