Sum Product and Division

[thbutterflycollector_]

Hi,

I am trying to calculate the change between indices on a perioidlcal basis but for several criteria and then add all of these together:

2		E	F	G	H	I	J	K	L	M
3	Base Date (B)	303	208	184	202	333	277	214	415
4	Latest (L)	303	208	184	202	333	277	214	415

In Column M4 I have worked out the following formuila:

=SUMPRODUCT((E4:L4-$E$3:$L$3)/$E$3:$L$3) I have #VALUE! message (notwithstanding the fact I need to have an IFERROR at the start of that. Can anyone help / solve?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

That formula works fine for me, do you have any blank cells with a formula that returns ""

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			2		E	F	G	H	I	J	K	L	M
3			3	Base Date (B)	303	208	184	202	333	277	214	415
4			4	Latest (L)	303	208	184	202	333	277	214	415	0
Master

Cell Formulas
Range	Formula
M4	M4	=SUMPRODUCT((E4:L4-$E$3:$L$3)/$E$3:$L$3)

 

[thbutterflycollector_]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

That formula works fine for me, do you have any blank cells with a formula that returns ""

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			2		E	F	G	H	I	J	K	L	M
3			3	Base Date (B)	303	208	184	202	333	277	214	415
4			4	Latest (L)	303	208	184	202	333	277	214	415	0
Master

Cell Formulas
Range	Formula
M4	M4	=SUMPRODUCT((E4:L4-$E$3:$L$3)/$E$3:$L$3)

Sorry for late reply (I am on 365). This worked. After some investigation there was some corruption in the excel file as it was converted from a PDF document. I checked for some random blanks in the cells before posting but pleased to say that after pasting these values into another sheet it worked perfectly. Thanks

 

[Fluff]

Glad it's sorted & thanks for letting us know.