Average with coefficients

tinytim

New Member
Joined
Oct 28, 2008
Messages
4
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,222
Messages
6,177,219
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top