Archive of Mr Excel Message Board

Assuming your numbers are in A1-A7, you could use

=AVERAGE(IF(A1:A7<>0,A1:A7))

Hit Control Shift Enter to enter this array formula

Thanks Ian

Ian, that formula didn't work. We received the error message "#VALUE!". When we took out the <, we received the error message "#NAME?". Thak you for your help.

Al, it may be tempting to use Marks shortened version, but it is a bad habit to start. You may well find yourself looking at it months down the track and thinking 'now what does this do'. Another user most certainly would.

Ians one is the better option.

Dave

OzGrid Business Applications

Dave: I feel compelled to comment on this. You can just attach a comment to the cell where you use the shorter formula and comment:
This formula is equivalent to =AVERAGE(IF(A1:A7<>0,A1:A7)). Even a further explanation how they work.

Aladin

I couldn't agree more about the use of
comments. In fact, ever workbook really
should include 1 sheet that contains nothing
but documentation. If there's any doubt that
someone might not understand this legitimate
use of IF() then they should include a the
following truth table in their documentation:

--- =IF(x,...)
x<0 TRUE
x=0 FALSE
x>0 TRUE

The array formula:
=AVERAGE(IF(A1:A7<>0,A1:A7))
Tells the average user at a glance what it does.

But having said that, as you may know already I believe array formulas are an easy trap to fall into, too many of them and recalculation slows down to a crawl. Excel has the Database formulas that will do the job much more effieciently (DAVERAGE in this case) and are much easier to edit. In effect you could have a single DAVERAGE formula linked to a cell containing a Data Validation list give back numerous results.

But we may just have to agree to disagree on this one :o)
OzGrid Business Applications

A lot of such inefficencies can be "wrung" out of a worksheet by employing a good design philosophy. Part of that design philosophy includes the use of 0/1 instead of Y/N, Yes/No, Male/Female, On/Off, etc. This reduces the need for SUMIF(), COUNTIF() and improves the performance of IF().

I don't believe for a second that you were advocating the attachment of a Comment for each and every IF() statement in a worksheet. I guess Dave doesn't document his workbooks with a dedicated worksheet or use a master comment in cell A1 of each worksheet.

When I raise the hood of my car I don't see an identifying label on my radiator hose. If I need to know what that black tube is I can always consult my owner's manual or a Peterson guide.

AGREE TO THE FULL EXTENT.

A lot of such inefficencies can be "wrung" out of a worksheet by employing a good design philosophy. Part of that design philosophy includes the use of 0/1 instead of Y/N, Yes/No, Male/Female, On/Off, etc. This reduces the need for SUMIF(), COUNTIF() and improves the performance of IF().

YEP. HOWEVER, I ADMIT TO SIN FROM TIME TO TIME AGAINST USING 0/1. THE HUMAN COGNITION APPEARS TO BE DISPOSED TO PROCESS NATURAL LANGUAGE BOOLEAN VALUES SUCH AS Yes/No MUCH FASTER THAN MACHINE REPRESENTATIONS THEREOF. THAT'S THE REASON THAT I TELL MY STUDENTS TO USE "Yes/No" WHENEVER THEY NEED TO PRODUCE OUTPUT THAT CONTAINS A JUDGMENT. SO I DON'T SEE ANY HARM CONVERTING BACK TO NATURAL LANGUAGE BOOLEANS IN THE OUTPUT PHASE. IT'S HARD JOB TO GET THE STUDENTS TO USE 0/1s DURING COMPUTATION.

I don't believe for a second that you were advocating the attachment of a Comment for each and every IF() statement in a worksheet.

THAT'S RIGHT: I WASN'T.

I guess Dave doesn't document his workbooks with a dedicated worksheet or use a master comment in cell A1 of each worksheet.

I GET PRETTY SLOPPY MYSELF IN THIS RESPECT. NOT WHEN PROGRAMMING IN LISP, THOUGH. HEY, "a master comment" IS A DAMN GOOD SUGGESTION. I PROMISE I'LL TAKE UP THIS ONE NEXT YEAR IN MY CLASSES.

HOW TRUE. BUT HUMAN COGNITION SEEMS TO BE A "LAZY EVALUATOR" (WHICH I APPRECIATE BY THE WAY AS BEING THE BEST POSSIBLE DESIGN). THE DESIGNERS OF THE OBJECTS SHOULD COME UP WITH OBJECTS THAT TELL US, AS MUCH AS POSSIBLE, WHAT THEY ARE AND, MORE IMPORTANTLY, WHAT THEY DO. WE CAN KEEP HOPEFULLY THE OWNER'S MANUAL QUITE A BIT SHORTER.

Aladin

Ah, but you can have your cake and eat it too by using a custom number format such as:

[=1]"Yes";[=0]"No"

The cell says "Yes", but it's value is 1.

This is another aspect of that design philosophy I was referring to earlier. Sometime you and I ought to do a "deep dive" on this subject!

Both array formulas (neither of which are mine BTW) loop through the cells A1:A7 and return either TRUE of FALSE. The recalculation time between the 2 is probably not even measurable, and is certainly worth the trade off as it is an easily read formula. When designing spreadsheets it is a very bad habit to get tunnel vision and abbreviate all formulas as much as possible. Secretaries do this with shorthand but they certainly don't push it out as the final product.

What I am saying is that without any doubt the array containing the IF statement tells the user at a glance what it does. The same certainly cannot be said for Marks array. Which you do agree, hence YOUR suggestion on cell comments.

Now I know your going to be responding (or echoing Mark) along the lines of 'Yes but if you have a lot of these types of arrays re-calculation will be slower'. But I don't think you can even begin to use that as your argument as you BOTH completely ignored my preferred method of using the DAVERAGE.

Using a "Master Comment" as you echoed is fine, but it should tell the user WHY you are doing something NOT HOW. the how can be normally be achieved within the formula.

Mark has contradicted himself by using the argument "When I raise the hood of my car I don't see an identifying label on my radiator hose" as it was YOU that suggested it and HE that agreed. Now you have blindly followed him and agreed.

Anyway this is my final comment on the matter as you only seem to agree with Mark and have no real argument of your own, which is a pity, as when I first started posting here you had some clever suggestions. Trust me I have seen and met Marks kind before and life's too short and too much fun to stop and waste time on them, they will always say black is white.

DaveOzGrid Business Applications

No doubt worth trying. Sounding perhaps a little unconvinced here. In a teaching situation I try not to overload the learner. I think the second year students can grasp such an idea. I'd like to see them to apply the custom number format that you suggest in the output phase, not in the processing.

Yeah. Why not. I'd like that.

Aladin

No doubt worth trying. Sounding perhaps a little unconvinced here. In a teaching situation I try not to overload the learner. I think the second year students can grasp such an idea. I'd like to see them to apply the custom number format that you suggest in the output phase, not in the processing.

Yeah. Why not. I'd like that.

Aladin