Page 1 of 1
Excel question - plz help
Posted: Wed Sep 12, 2018 6:35 pm
by Rocsteady
I feel like this is easy as but I'm nearing a murder spree at this point as I can't figure out how to do it.
I've got values in A1 to A5 where people have rated something 1-5.
In b1 to b5 I have one person who gave a 3, marked as a '1' in b3. Another a person who rated it a 4 (thus a '1' in b4). The others are 0.
What formula would give me the result of 3.5 that I desire?
I need you
please God help
Re: Excel question - plz help
Posted: Wed Sep 12, 2018 6:47 pm
by Death's Head
Not very elegant, but =((A1*B1)+(A2*B2)+(A3*B3)+(A4*B4)+(A5*B5))/SUM(B1:B5)
Re: Excel question - plz help
Posted: Wed Sep 12, 2018 6:51 pm
by rinks
((A1*B1)+(A2*B2)+(A3*B3)+(A4*B4)+(A5*B5))/SUM(B1:B5)
DH: letter-for-letter what I was going to post, right down to saying it was inelegant!
Re: Excel question - plz help
Posted: Wed Sep 12, 2018 6:56 pm
by Sandy
If you want to do it through functions so don't have to change your calcs later then you want to use a cell to =SUMPRODUCT(A1:A5,B1:B5) use another cell to =SUM(B1:B5) then in the cell you want to answer to appear you need to divide the SUMPRODUCT cell by the SUM cell.
Re: Excel question - plz help
Posted: Wed Sep 12, 2018 7:09 pm
by Rocsteady
Works like a charm. Thanks guys really appreciate that, had a total shagger of a day and just couldn't engage my brain anymore.
Re: RE: Re: Excel question - plz help
Posted: Wed Sep 12, 2018 7:16 pm
by Death's Head
Rocsteady wrote:Works like a charm. Thanks guys really appreciate that, had a total shagger of a day and just couldn't engage my brain anymore.
Just the matter of settling our invoices and then you are sorted.