I am trying to make a formula that is essentially a SUMIF, but based on the criteria the formula adds to cells. Probably an example is best.
For each "5i" in column B, add the value in Column A to the value in column C. Then, divide by the count of "5i" in column B.
Effectively, the formula would automatically calculated ((195+-2)+(200+8)+(205+4))/3 = 203.3
[ A ] [ B ] [ C ]
Yardage Iron -Lft / +Rgt
195 5i -2.0
135 9i 11.0
200 5i 8.0
120 PW -7.0
205 5i 4.0
175 7i -16.0
Here is a solution suitable for Excel 20o7 and newer:
Please try in B3 and copy down:Formula:
Please Login or Register to view this content.
Are you still using Excel 2010?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Here is another solution suitable for Excel 2013 and newer:
Please tryFormula:Please Login or Register to view this content.
@Hans: the SUM/COUNTIF version could be shortened to:
Formula:Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Originally Posted by TMS
@Trevor, The function has not been shortened. Both functions are exactly the same length and both refer equally often to a function or to a cell or a range.
Please Login or Register to view this content..
If you really want it shorter, I think you should omit the reference to column B the second time, but that is not easy in Excel 2013.
Another option:
=AVERAGE(IF($B$13:$B$18=A3,$A$13:$A$18+$C$13:$C$18,""))
Rory
Yes, that one is really shorter Rory..
So it is possible after all.
@Hans: I was comparing it to the first formula. Agreed, it is not shorter than the second formula.
94 =IFERROR(SUM((A$13:A$99)*(B$13:B$99=A3)+(C$13:C$99)*(B$13:B$99=A3))/COUNTIF(B$13:B$99,A3),"-")
81 =IFERROR(SUM(((A$13:A$99)+(C$13:C$99))*(B$13:B$99=A3))/COUNTIF(B$13:B$99,A3),"-")
81 =IFERROR(AVERAGEIF(B$13:B$99,A3,A$13:A$99)+AVERAGEIF(B$13:B$99,A3,C$13:C$99),"-")
Thank you! This seems to work!
You're welcome. Thanks for the rep.
I see an issue with the formula in that it doesn't exclude null values yet incorporates the null shot's distance. As you can see the formula in cell C3 incorporates Shot #1, 3, and 5. However, there wasn't a value for Shot 5 and thus the cell (D11) was null. How can null cells be excluded from the formula? If they aren't excluded, this formula overstates the average distance.
Try this in C3 and committed with Ctrl+Shfit+Enter.
Please Login or Register to view this content.
About your question in Post #12:
I think that all formulas, except the formula in Post #4, will still work fine.
In that example (Post #12), D11 (LFT/RIGHT) is empty. Empty or not empty. An average must be taken over 3 values.
The average of 200-2, 200+8 and 200 is the average of 198, 208 and 200 is 202.
And except for the formula in Post #4, all formulas return 202.
The formulas do not ignore the blank (null) cells. At least that is what I see. I've created a spreadsheet that shows various formulas at the top and my desired responses below.
I really appreciate your expertise on this!
O the yardages without LFT/RGT should be completely excluded from the calculation.
I had misunderstood that.
The easiest solution is to adjust Rory's formula:
Please tryFormula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks