Weighting to change if #div/0 error

[Ghost141]

Hey,

INV Acc

87.50%​

50.00%​

75.00%​

83.33%​

100.00%​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

Reporting

100.00%​

#DIV/0!​

#DIV/0!​

100.00%​

100.00%​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

Case Creat

42.86%​

60.00%​

75.00%​

71.43%​

100.00%​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

Prod

72%​

75%​

67%​

89%​

88%​

0%​

0%​

0%​

0%​

0%​

0%​

0%​

SCORE

74.39%​

#DIV/0!​

#DIV/0!​

86.96%​

94.00%​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

#DIV/0!​

SCORE is based on INV Acc, Reporting and Case Creation (weighing 16.66% each) and Prod (weighing 50%). What formula would i use for the #DIV/0! errors in Reporting so INV Acc and Case Creat are weighted as 25% each when this occurs.

Current formula is =SUMPRODUCT(B45:B48,$N$27:$N$30)/SUM($N$27:$N$30)

 

[Ghost141]

The closest i got to was =SUMPRODUCT(IF(ISNUMBER(B45:B48*$N$27:$N$31),B45:B48*$O$27:$O31))/SUM(IF(ISNUMBER($N$27:$N$31),$O$27:$O31)) but doesnt seem to do the trick

 

[StephenCrump]

Welcome to the Forum!

This would be the usual approach:

	A	B	C	D	E	F	G
1		Weight
2	INV Acc	16.67%	87.50%	50.00%	75.00%	83.33%	100.00%
3	Reporting	16.67%	100.00%	#DIV/0!	#DIV/0!	100.00%	100.00%
4	Case Creat	16.67%	42.86%	60.00%	75.00%	71.43%	100.00%
5	Prod	50.00%	72%	75%	67%	89%	88%
6	SCORE		74.39%	67.00%	70.20%	86.96%	94.00%
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=1/6
C6:G6	C6	=SUM(IFERROR($B2:$B5*C2:C5,0))/SUM(ISNUMBER(C2:C5)*$B2:$B5)

In columns C and D, the weights are all scaled proportionally, i.e. to 20% for Inv Acc and Case Creat, and to 60% for Prod.

If you want to customise the weights to something different, you'll need to specify the various possibilities. For example, what if Inv Acc and Reporting are both #DIV/0! Should the weights be 50/50 for Case Creat and Prod?

 

[Ghost141]

Thanks Stephen, yes the weightings are split between the first three catagories (50% made up up 16.66% each) and PROD (50%), so in theory if INV Acc, Reporting and Case Creat were all #DIV/0! all 100% would be weighted to PROD. At this stage the most likely #DIV/0! would be in reporting which is why i suggested that formula of INV acc and Creat picking up the excess weighting. Thanks heaps for your help!

 

[StephenCrump]

How is this?

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Weight
2	INV Acc	equal	60%	#DIV/0!	60%	60%	#DIV/0!	60%	#DIV/0!	#DIV/0!	60%	60%	#DIV/0!
3	Reporting	equal	70%	70%	#DIV/0!	70%	#DIV/0!	#DIV/0!	70%	#DIV/0!	70%	70%	#DIV/0!
4	Case Creat	equal	80%	80%	80%	#DIV/0!	80%	#DIV/0!	#DIV/0!	#DIV/0!	80%	#DIV/0!	#DIV/0!
5	Prod	50%	90%	90%	90%	90%	90%	90%	90%	90%	#DIV/0!	#DIV/0!	#DIV/0!
6	SCORE		80.0%	82.5%	80.0%	77.5%	85.0%	75.0%	80.0%	90.0%	70.0%	65.0%	n/a
Sheet1

Cell Formulas
Range	Formula
C6:M6	C6	=LET(a,AGGREGATE(1,6,C2:C4)*(1-$B5*ISNUMBER(C5)),IF(ISERROR(a),IFERROR(C5,"n/a"),a+$B5*IFERROR(C5,0)))

 

[Ghost141]

Incredible. Thanks so much!