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
deniztopcu's avatar
deniztopcu
Brass Contributor
Sep 12, 2023
I was excited when I saw the fiction. But he made an error.
#NO
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 12, 2023

    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's avatar
      deniztopcu
      Brass Contributor
      Sep 12, 2023
      Thank you too.
      • 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
    Thanks for your efforts.
    It was very stylish.

Resources