excluding zeros when averaging

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, Formula did not work

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.

Are you just hitting enter?

This is array formula that must be entered by hitting control shift and enter at the same time. If you are getting the Value error, hit F2 and then Control Shift Enter. Please let me know if that doesn't work.
Regards,
Ian

You can also use...

{=AVERAGE(IF(A1:A7,A1:A7))}

Bad habit

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

Re: Bad habit

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

Thanks Ian, it worked like a charm!

Re: Bad habit

While this formulas do the same thing
{=AVERAGE(IF(A1:A7<>0,A1:A7))} makes 7 unnecessary comparisions when evaluating
A1:A7<>0. These comparisons steal processor
cycles for no good purpose.

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

My truth table was mangled by this web site...

Re: Bad habit

Aladin, yes you could attach a comment to the cell(s), but again I believe that would make for 2 bad habits. If you have a workbook with a lot of different formulas you will end up with a Workbook with a lot of cell comments. Cell comments are objects and too many of them do can cause a blow out in file size. They are like sticky notes in the real world and should be used sparingly. Besides if you have to go through the process of inserting a cell note and typing in an explanation you may as well use Ians array.

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

Dave still doesn't get it...

His array formulas are inefficient because of his formula construction. IF() expects a boolean value as its first argument. If a conditional operator is used in that argument it must be evaluated and if that operator is used with an array of 100 cells it must be evaluated 100 times! If the 1st argument is a number this additional evaluation isn't necessary because IF() and all other boolean functions understand the truth table that I posted earlier.

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.

Documenting spreadsheets...

IF() expects a boolean value as its first argument. If a conditional operator is used in that argument it must be evaluated and if that operator is used with an array of 100 cells it must be evaluated 100 times! If the 1st argument is a number this additional evaluation isn't necessary because IF() and all other boolean functions understand the truth table that I posted earlier.

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

Re: Documenting spreadsheets...

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().

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!

My Final Attempt At The Obvious

Aladin, please think for yourself :)

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

Come on you girls! This used to be a happy board.

Hey I'm still smiling :o)

Re: Documenting spreadsheets...

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

Re: Documenting spreadsheets...

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