Forum Discussion

deniztopcu's avatar
deniztopcu
Brass Contributor
Sep 12, 2023
Solved

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.
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Sep 12, 2023

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

 

 

 

deniztopcu's avatar
deniztopcu
Brass Contributor
Sep 12, 2023
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.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Sep 12, 2023

    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.