Excel formula error - formula almost exactly the same as one that works-CodePudding

I'm having issues with an excel formula which is very frustrating as it's almost exactly the same as a working formula (just referring to different cells/a different number of cells). The formula finds a weighted average scoring from another sheet using the raw scoring and the weighting. This is supposed to create a dynamic sheet that only needs updating manually on a rare occasion (like now)

The formula that works is as follows: =SUM(INDEX('Winter 2022'!V:V,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$V$4,INDEX('Winter 2022'!W:W,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$W$4,INDEX('Winter 2022'!Y:Y,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$Y$4,INDEX('Winter 2022'!AD:AD,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AD$4,INDEX('Winter 2022'!AE:AE,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AE$4)/SUM('Winter 2022'!$V$4,'Winter 2022'!$W$4,'Winter 2022'!$Y$4,'Winter 2022'!$AD$4,'Winter 2022'!$AE$4)

While the formula that doesn't work is: =SUM(INDEX('Winter 2022'!Z:Z,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$Z$4,INDEX('Winter 2022'!AA:AA,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AA$4,'Winter 2022'!AB:AB,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AB$4,INDEX('Winter 2022'!AC:AC,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AC$4)/SUM('Winter 2022'!$Z$4,'Winter 2022'!$AA$4,'Winter 2022'!$AB$4,'Winter 2022'!$AC$4)

The bit excel flags is !$AC$4 just before /SUM. I'm sure it's something obvious I'm missing!

CodePudding user response：

You are missing an INDEX, which would indeed have been obvious, had you typed your formula on several lines (Alt Enter).

=SUM(
INDEX('Winter 2022'!Z:Z,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$Z$4,
INDEX('Winter 2022'!AA:AA,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AA$4,
INDEX('Winter 2022'!AB:AB,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AB$4,
INDEX('Winter 2022'!AC:AC,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AC$4
)/SUM('Winter 2022'!$Z$4,'Winter 2022'!$AA$4,'Winter 2022'!$AB$4,'Winter 2022'!$AC$4)

What you had was:

=SUM(
INDEX('Winter 2022'!Z:Z,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$Z$4,
INDEX('Winter 2022'!AA:AA,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AA$4,
'Winter 2022'!AB:AB,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AB$4,
INDEX('Winter 2022'!AC:AC,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AC$4
)/SUM('Winter 2022'!$Z$4,'Winter 2022'!$AA$4,'Winter 2022'!$AB$4,'Winter 2022'!$AC$4)

EDIT:
By the way, I am noticing you are using MATCH($B3,'Winter 2022'!$A:$A,0) more than once in the formula.

If you are on an up-to-date Excel 365, you can simplify/speed up your formula by using the LET function.

=LET(
MatchRow, MATCH($B3,'Winter 2022'!$A:$A,0),
SUM(
INDEX('Winter 2022'!Z:Z  ,MatchRow)*'Winter 2022'!$Z$4,
INDEX('Winter 2022'!AA:AA,MatchRow)*'Winter 2022'!$AA$4,
INDEX('Winter 2022'!AB:AB,MatchRow)*'Winter 2022'!$AB$4,
INDEX('Winter 2022'!AC:AC,MatchRow)*'Winter 2022'!$AC$4
)/SUM('Winter 2022'!$Z$4:$AC$4)
)

It makes it even easier to read, does it not?

CodePudding user response：

Argument 3 in the SUM in the nominator of the fraction should be INDEX('Winter 2022'!AB:AB,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AB$4 while in your formula that doesn't work it is just 'Winter 2022'!AB:AB,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AB$4.

So the full formula would be =SUM(INDEX('Winter 2022'!Z:Z,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$Z$4,INDEX('Winter 2022'!AA:AA,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AA$4,INDEX('Winter 2022'!AB:AB,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AB$4,INDEX('Winter 2022'!AC:AC,MATCH($B3,'Winter 2022'!$A:$A,0))*'Winter 2022'!$AC$4)/SUM('Winter 2022'!$Z$4,'Winter 2022'!$AA$4,'Winter 2022'!$AB$4,'Winter 2022'!$AC$4)

Debugging such formulas is really difficult. I would suggest computing each argument of the SUM in a separate cell on a separate sheet, say A1,...,A4 for the nominator and B1,..,B4 for the denominator then the result would be just SUM(A1:A4)/SUM(B1:B4).