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
Sandy
Member
Joined in 2018
AKA: Akuma / Dormin
Location: Surrey, darling

PostRe: Excel question - plz help
by Sandy » 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 Sandy 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”

Who is online

Users browsing this forum: Albert, Godzilla, Grumpy David, Ploiper, Rubix, shy guy 64, wensleydale and 447 guests