Hello,
I have a formula to find the average like this: SUM(A1:A5)/COUNTIF(A1:A5,">"&0)
The formula above is applied for range. Now I want to apply by individual cells how can I use the countif like above. For example, I want to find an average in column S2 and W2 if S2 is 0 then I just need to use W2 number. If I applied formula above, it would look like this SUM(S2,W2)/COUNTIF(?). Since Countif only allows to use range, how can modify this formula and get what I expected. Please help.
Thanks,
I am not exactly sure I follow what you are trying to do.
Can you mock up a sample data set, or desensitize the actual data set you are using, and give us a mocked up solution?
Last edited by mikeTRON; 01-13-2014 at 01:59 PM.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
I would recommend a helper row. Using your example, in cells S1:W1 put a TRUE if you want to use the cell in your calculation, else put a FALSE. So, you would have TRUE in S1 and W1 and FALSE everywhere else. Then use this formula:
=AVERAGEIFS(S2:W2,S2:W2,">0",S1:W1,TRUE)
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks