Excel question - plz help

Fed up talking videogames? Why?
User avatar
Rocsteady
Member
Joined in 2008

PostExcel question - plz help
by Rocsteady » Wed Sep 12, 2018 6:35 pm

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 :capnscotty:

please God help

Image
User avatar
Death's Head
Member
Joined in 2009

PostRe: Excel question - plz help
by Death's Head » Wed Sep 12, 2018 6:47 pm

Not very elegant, but =((A1*B1)+(A2*B2)+(A3*B3)+(A4*B4)+(A5*B5))/SUM(B1:B5)

Yes?
User avatar
rinks
Member
Member
Joined in 2008
Location: Aboard the train that goes around the world

PostRe: Excel question - plz help
by rinks » Wed Sep 12, 2018 6:51 pm

((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!

User avatar
Snowy
Member
Joined in 2018
AKA: Akuma / Dormin
Location: Surrey, darling

PostRe: Excel question - plz help
by Snowy » Wed Sep 12, 2018 6:56 pm

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.

Last edited by Snowy on Wed Sep 12, 2018 7:14 pm, edited 1 time in total.
User avatar
Rocsteady
Member
Joined in 2008

PostRe: Excel question - plz help
by Rocsteady » Wed Sep 12, 2018 7:09 pm

Works like a charm. Thanks guys really appreciate that, had a total shagger of a day and just couldn't engage my brain anymore.

Image
User avatar
Death's Head
Member
Joined in 2009

PostRe: RE: Re: Excel question - plz help
by Death's Head » Wed Sep 12, 2018 7:16 pm

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.

Yes?

Return to “Stuff”