Average with coefficients

[tinytim]

I am trying to work out a complicated average. In order to do this i need to multiply each individual cell in a row (B3:E3) by a corresponding row of coefficients (B2:E2) and than divide the sum of these values by the sum of the coefficients (I3). To complicate matters B3 is text and E3 is blank. i then need to apply the formula to subsequent rows.

Have tried the following and much more besides...am excel novice so really just been playing about with things.

=($B$2*B3)+($C$2*C3)+($D$2*D3)+($E$2*E3)/I3) answer #VALUE

=SUMIF(B3:E3,">0",($B$2*B3)+($C$2*C3)+($D$2*D3)+($E$2*E3)/I3) answer #VALUE

always answer = #VALUE

I have pretty much worked out that the problem is with the text but there are other issues as i'm sure will be picked up on. Would appreciate help and a laymans explanation as this has been driving me nuts, i feel like snail trying to crack open a walnut. To top it all off this is being done for my girlfriend on a UK Excel who has then to write down the formula in French so all commas, colons, and commands have then to be translated though this is the least of my worries.
If all this make little sense i can try again.

cheers
Tim

 

[teylyn]

Hi tim

To complicate matters B3 is text and E3 is blank.

You cannot multiply text with a number and multiplying a blank cell with a number will also not give you good results. If B3 is text, can you give an example of what it might be and what you would expect the result of B2*B3 to be? Likewise for E3 - what should the result of E2*E3 be?

Teylyn

 

[barry houdini]

SUMPRODUCT will ignore blanks and text and only multiply numerics, i.e.

=SUMPRODUCT($B$2:$E$2,B3:E3)/I3

Does that work for you?

 

[tinytim]

Barry,

thanks so much works a treat...my girlfriend wants to kiss you !!
To reply to Telyn
B3 is Abs as in absent and E3 is a blank cell in both cases i wanted the formula to ignore these and deal only where numbers appeared, thus for the next row B4 and E4 both have numerical values whereas C4 is Abs.

incidentally can anyone point me to a webpage where i can see a list of all EXCEL formula commands, i have one but it is not comprehensive

thanks again
Tim