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

[deniztopcu]

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


ı havent office 365.

İndir

Dosyayı indir

dosya.co

 

[CesarF]

Without macros I would add hidden columns with partial calculations
Also, you can change COLUMN()*2-<first hidden column+1> to use the same algorithm for all, but further maintenance will be a lot harder

 

[Anonymous1378]

Perhaps try this

xy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
8										QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM
9	A	131%	108%	- 62						52%	-59%	-30%	-82%	-12%	71%	10%	107%	-17%	72%	9%	135%	53%	90%	12%	102%	-16%	71%	9%	131%	148%	107%	33%	-76%	-15%	112%	31%	-35%
10	B	271%	542%	- 152						-33%	-10%	-2%	3%	54%	128%	21%	123%	81%	154%	24%	150%	92%	192%	30%	168%	78%	156%	24%	147%	-17%	331%	138%	127%	-17%	416%	139%	128%
Sayfa1

Cell Formulas
Range	Formula
D9:D10	D9	=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<>""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

ı havent office 365.

But you have 2021, so give this replacement formula a try.

deniztopcu.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
8										QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM	QoQ	YoY	QoQ TTM	YoY TTM
9	A	131%	108%	-62						52%	-59%	-30%	-82%	-12%	71%	10%	107%	-17%	72%	9%	135%	53%	90%	12%	102%	-16%	71%	9%	131%	148%	107%	33%	-76%	-15%	112%	31%	-35%
10	B	271%	542%	-152						-33%	-10%	-2%	3%	54%	128%	21%	123%	81%	154%	24%	150%	92%	192%	30%	168%	78%	156%	24%	147%	-17%	331%	138%	127%	-17%	416%	139%	128%
Sayfa1

Cell Formulas
Range	Formula
D9:D10	D9	=LET(s,SEQUENCE(6,,,0.2),Hdr,$J$8:$AK$8,QoQ,(Hdr="QoQ")+(Hdr="QoQ TTM"),YoY,(Hdr="YoY")+(Hdr="YoY TTM"),rw,J9:AK9,nb,rw<>"", SUMPRODUCT((B9<rw*s)*QoQ*nb)-SUMPRODUCT((B9>rw*s)*QoQ*nb)+SUMPRODUCT((C9<rw*s)*YoY*nb)-SUMPRODUCT((C9>rw*s)*YoY*nb))