[Not an issue, just feedback] Am I being efficient with my formulas?

[svalentine91]

I didn't want to post this in general because it's really not an issue. I just want to see if maybe I can improve on some formulas I just changed. Feel free to critique or ignore. sample workbook included for second one.

Summing the trailing 12 months if it is November(not including November):

=IF(MONTH($E$3:$CO$3)=11,SUMPRODUCT((DATE(YEAR($E$3:$CO$3),MONTH($E$3:$CO$3),1)>=(DATE(YEAR(CN$3),MONTH(CN$3)-12,1)))*(DATE(YEAR($E$3:$CO$3),MONTH($E$3:$CO$3),1)<(DATE(YEAR(CN$3),MONTH(CN$3),1)))*($E7:$CO7)*$C9),0)


If a date is 13 months past the current month and the row is equal to a string multiply the cell by (1/(1-percentage)), then multiply by a second, and third percentage. Then subtract the number without "(1/(1-percentage))":

=(SUMPRODUCT(((DATE(YEAR($C$5:$C$16),MONTH($C$5:$C$16)+12+1,1)>L$3)*L$5:L$16)*($A$5:$A$16=$A18)*(1/(1-$F$5:$F$16)),$G$5:$G$16,$E$5:$E$16)-SUMPRODUCT(((DATE(YEAR($C$5:$C$16),MONTH($C$5:$C$16)+12+1,1)>L$3)*L$5:L$16)*($A$5:$A$16=$A18),$G$5:$G$16,$E$5:$E$16))

[TMS]

You can re-write the second formula as:

=

SUMPRODUCT(((DATE(YEAR($C$5:$C$16),MONTH($C$5:$C$16)+12+1,1)>L$3)*L$5:L$16)*($A$5:$A$16=$A18)*($G$5:$G$16)*($E$5:$E$16)*(1/(1-$F$5:$F$16)))

-

SUMPRODUCT(((DATE(YEAR($C$5:$C$16),MONTH($C$5:$C$16)+12+1,1)>L$3)*L$5:L$16)*($A$5:$A$16=$A18)*($G$5:$G$16)*($E$5:$E$16))

And then, in theory, you can rationalise that to be:

=SUMPRODUCT(((DATE(YEAR($C$5:$C$16),MONTH($C$5:$C$16)+12+1,1)>L$3)*L$5:L$16)*($A$5:$A$16=$A18)*($G$5:$G$16)*($E$5:$E$16)*  (1/(1-$F$5:$F$16)-1)  )

Now that returns the same value, give or take a digit at the thirteenth decimal place for some values. But, if you subtract the two values, that difference disappears. So, unless you actually compare the values from the two formulae, you'd never know.

Have a look at the updated example ...


Regards, TMS