Please help me with this formula
I need a formula to find the average of the numbers in column C: of Oeste's (cell B1) games within the table below.
If Oeste appears in column A, take number in C
If Oeste appears in column B, take the number in C multipled by -1
pls see attachment
Thanks
One quick and easy way would be to use a helper column.
Put the below formula in E11 and copy down to E42.
=IF(A11=B$1,C11,IF(B11=B$1,C11*-1,""))
Then use =AVERAGE(E11:E42) to get the result you're looking for.
BSB
Thanks BSB.
Yes that would help, however ultimately I planned to create a table with all the teams showing these averages within a range.
pls see the new attachement, so here I have all teams listed B1:B20 where i'd like the averages next to them .
I don't think the helper column can help to allow this
Thanks
Or, all in one:
=SUMPRODUCT(--(($A$11:$A$42=$B$1)+($B$11:$B$42=$B$1))*$C$11:$C$42*{1,1})/COUNTIF($A$11:$B$42,$B$1)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Ah that's it, brilliant..
Thanks Glenn
Hold fire. It's incorrect. It's coincidentally similar. Look back in five mins.
Last edited by Glenn Kennedy; 01-14-2021 at 04:37 AM.
Ok thanks. I was actually noticing some issues, and still am even with the newest formula hmm.
The average of Oeste should be coming up exactly -0.26
(0.06 + 0.76 - 1.02 - 0.84)/4
but the formula is actually returning -0.2615. When I put a different team in B1, the numbers don't seem to be correct.
Have to pop out now but will test again in a while to give more examples
#4's shorter version
Please Login or Register to view this content.
Quang PT
This array formula works:
=SUM(IF($A$11:$A$42=$B$1,$C$11:$C$42,IF($B$11:$B$42=B1,-$C$11:$C$42,"")))/COUNTIF($A$11:$B$42,$B$1)
These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Column C itself with 3-4 decimals, so you wish to round it?
Try again:
Please Login or Register to view this content.
Bebo got it.... I haven't had my coffee yet!!
Thanks guys. That definitely works well.
One more question. How would the formula need to change to work if I introduced a gap (another column, empty or not) between columns A and B?
the formula is now returning #N/A
Seems like it would just be a case of changing the {1,-1} to {1,0,-1}.
Thanks all.
Based on the screen shot in post #12, try modifying bebo's formula to read:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
That's great, thanks
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks