SUMPRODUCT: dividing the formula of one cell by another.
 

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$1000))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($I$2:$I$1000))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($M$2:$M$1000))

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))))

SUMPRODUCT: dividing the formula of one cell by another.
 

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

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




"SW" wrote in message
...
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$1000))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($I$2:$I$1000))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($M$2:$M$1000))

X: ==SUMPRODUCT(--($C$2:$C$100="B
Brad"),--($E$2:$E$1000))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($I$2:$I$1000))+SUMPRODUCT(--($C$2:$C$100="Bill
Brad"),--($M$2:$M$1000))