SOLVED

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

Brass Contributor

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

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

ı havent office 365.

19 Replies

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

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

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

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

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

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

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

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

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

It was very stylish.

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

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

Thank you too.

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

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

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

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.

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

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.

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

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

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

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

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

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.

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

@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

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

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.

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

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

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

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.

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

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.

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

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