i have table of data (below) "w"='wins' , "t"='target'. each category based on win-type.
the objective calculate average "score" in right-most column across categories based on person's goals. 2 observations: (1) instances "w"=0 , "t"=0, category should ignored in average calculation--both in numerator , denominator; (2) instances there win no target (e.g. bob's category 7) win should counted--i.e. bob have 200% cat 7
i took shot @ it, seen in image below, kept getting #value! message.
in formula, "" in if() functions resolves empty text , cannot added without error.
so change "" '0'
or can use array formula:
=iferror(sum((mod(column(f5:r5),2)=0)*iferror($e5:$q5/f5:r5,e5:q5))/sumproduct((mod(column(e5:q5),2)=1)*(f5:r5<>0)),0) being array formula needs confirmed ctrl-shift-enter. if done correctly excel put {} around formula.
