SUMPRODUCT: dividing the formula of one cell by another.

[SW]

I have a formula in column W that will count the number of goals set. In
column X the count is for the number of times that the person missed goal.
In column V I've divided column X by column W. Why can I not just use one
cell with the numerator being the being the formula in column X and the
denominator being the formula W?


W: =SUMPRODUCT(--($C$2:$C$100="B
Brad"),--($E$2:$E$100>0))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($I$2:$I$100>0))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($M$2:$M$100>0))

X: =SUMPRODUCT(--($C$2:$C$100="Bill
Brad")*(--($F$2:$F$100<$E$2:$E$100)+(--($J$2:$J$100<$I$2:$I$100))+(--($N$2:$N$100<$M$2:$M$100))))

 

[Bernard Liengme]

As in
=(SUMPRODUCT(--($C$2:$C$100="B

Brad"),--($E$2:$E$100>0))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($I$2:$I$100>0))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($M$2:$M$100>0))) / (=SUMPRODUCT(--($C$2:$C$100="B
Brad"),--($E$2:$E$100>0))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($I$2:$I$100>0))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($M$2:$M$100>0)))

Wish I could use colour to show the parentheses

=( w formula ) / ( x formula )


You need these two sets of ( ) as both the denominator and numerator have
parts in them joined by +
We need to get the addition done before the division (think of Mrs Murphy's
algebra class)
best wishes

--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme