I've been working with several formulas to get an average of certain rows, based on values in a column, but I'm getting #DIV/0 errors.
The average range depends on a number in another column, and it must only average positive numbers.
I've attached a spreadsheet with a note showing what I'd like.
It seems like a simple formula, but I'm just not experienced enough to get it to work. If it helps I can post the formula I've been trying.
Thanks to all you bright people helping me!
In cell R19
=IF(M19=0,0,AVERAGEIF(M$9:M19,"<>0"))
Copy down
Life's a spreadsheet, Excel!
Say thanks, Click *
Thanks. If I do that, then the calculations are correct for cells R19 through R24, however R25 should be $24.73, and R26 should be $33.29. (The formula is returning values of $28.71 for R25 and $37.96 for R26)
The average range needs to be dynamic somehow. Since cell Q24 is 0, it means no shares are owned at that point. So the average should start over begining at row 25 and not include any rows above. What we want in column R, is the average price of shares currently owned. Any thoughts? Thanks much!
jrtaylor,
I don't understand howIf I have been understanding where the resets occur formula should be averaging M25:M26 in R26, or $24.73 and $75.38. I get $50.05 in R26 with this array entered formula in R9 filled down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.and R26 should be $33.29.What am I missing?Formula:
=IF(Q9=0,Q9,AVERAGE(IF(INDEX($M$9:M9,MATCH(2,1/($Q$9:Q9=0),1)+1):INDEX($M$9:M9,MATCH(2,1/($Q$9:Q9>0),1))>0,INDEX($M$9:M9,MATCH(2,1/($Q$9:Q9=0),1)+1):INDEX($M$9:M9,MATCH(2,1/($Q$9:Q9>0),1)))))
Dave
Thanks. What you were missing was my failure to do the math right! Your formula solves the problem.
You're welcome. Glad to hear it works.Thanks for the feedback and the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks