Google sheets, summing over a formula?

user_6092721 0
Viewed 55

Apologies if this is a little unclear, or this question has been asked already. It's a little difficult to explain, but I've bolded my question - it's essentially about shortening formulas.

I'm running a payment plan waterfall model. My code works, but, it's er, you know...

IF($K2=Q$1,Assumptions!$E$56*($N2+$O2),IF(AND($K2<Q$1,$M2>Q$1),Assumptions!$E$56*$P2/$L2,0)) + IF(edate($K2,1)=Q$1,Assumptions!$F$56*($N2+$O2),IF(AND(edate($K2,1)<Q$1,edate($M2,1)>Q$1),Assumptions!$F$56*$P2/$L2,0)) +  IF(edate($K2,2)=Q$1,Assumptions!$F$56*($N2+$O2),IF(AND(edate($K2,2)<Q$1,edate($M2,2)>Q$1),Assumptions!$F$56*$P2/$L2,0)) +  IF(edate($K2,3)=Q$1,Assumptions!$F$56*($N2+$O2),IF(AND(edate($K2,3)<Q$1,edate($M2,3)>Q$1),Assumptions!$F$56*$P2/$L2,0)) +  IF(edate($K2,4)=Q$1,Assumptions!$F$56*($N2+$O2),IF(AND(edate($K2,4)<Q$1,edate($M2,4)>Q$1),Assumptions!$F$56*$P2/$L2,0)) +  IF(edate($K2,5)=Q$1,Assumptions!$F$56*($N2+$O2),IF(AND(edate($K2,5)<Q$1,edate($M2,5)>Q$1),Assumptions!$F$56*$P2/$L2,0)) + IF(edate($K2,6)=Q$1,Assumptions!$F$56*($N2+$O2),IF(AND(edate($K2,6)<Q$1,edate($M2,6)>Q$1),Assumptions!$F$56*$P2/$L2,0))

...pretty long.

Essentially what's going on is: the assumption is, when we launch a product, we sell say 80% in the first month, and 2.5% every subsequent month until we each 100%.

I'd like the 80% and the 2.5% to be variables (listed as Assumptions!$E$56 and Assumptions!$E$56) here.

Obviously a little long. But noticed after the first IF clause, the subsequent ones are actually identical, the only difference being the number inside edate(__,2), edate(__,3)...

So my question is - can this code be tidied up into some sort of for loop? Python would make it pretty simple to increment over the variable edate(__,i) and sum over i = 1:6.

google-sheets
1 Answers

Sure, there is. Usually looping is emulated by Sequence(N), which makes an array of numbers from [1,N] vertically, which is somewhat like your Python range. Then you can do stuff to it as an ArrayFormula.

In your case, you end up with two terms: your initial term using $E, and all the looped stuff using $F. I see 6 terms, so I will use Sequence(6):

=IF(
    $K2=Q$1,
    Assumptions!$E$56*($N2+$O2),
    IF(
        AND(
            $K2<Q$1,
            $M2>Q$1
        ),
        Assumptions!$E$56*$P2/$L2,
        0
    )
) + ArrayFormula(SUM(
    IF(
        edate($K2,SEQUENCE(6))=Q$1,
        Assumptions!$F$56*($N2+$O2),
        IF(
            (edate($K2,SEQUENCE(6))<Q$1)*
            (edate($M2,SEQUENCE(6))>Q$1),
            Assumptions!$F$56*$P2/$L2,
            0
        )
    )
))

And if you want, you can give your Assumption values names using named ranges.

user_42943990
Related