Hi Community Members...
In C5 I want to average the only negative numbers
if its entered in B9:B29. If there is no negative number C5 should be blank or "No data" message.
I used =AVERAGE(IF(B9:B29<0,B9:B29," ")), but it returns a #DIV/0! error.
I have looked at Excel Help but could not work....
All help much appreciated and I thank all for their
help and time with my inquiry.
Best Regards.
Try
=IFERROR(AVERAGE(IF(B9:B29<0,B9:B29,"")),"")
C+S+E
??? or even
=IFERROR(AVERAGEIF(B9:B29,"<0",B9:B29),"")
entered normally. You can use a similar formula using SUMIF and COUNTIF rather than SUM(IF... and COUNT(IF....
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
It doesn't work with negative entries. Cell left blank.
I opened your file from post #4, selected C5, pressed F2 (to enter edit mode), then pressed ctrl-shift-enter to array enter the formula, and it returned the value -20.25. It looks to me like you did not array enter the formula, which is why it returned the error. Is that the problem, or is there more to the question?
Originally Posted by shg
Excellent.. I got it..
Thanks everyone here for kind help.
This formula worked! The help built in to the Excel app was useless. THANKS!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks