average over worksheets excluding zeros

[sccottt]

im trying to average over a number of work sheets to determine the avergage talk time of employees over a day, ive tried:

=IF(ISERROR(AVERAGE(persons name:persons name!h16)),"",AVERAGE(Persons name:persons name!h16))

Were Persons name is the name of the worksheet and h16 is the cell i wish to average, i keep getting a value error or simply that it wont work, i understand that i can use a vba to do this although i am not familliar with this is there another way to average the time over a number of worksheets? i know i could just remove the zeros from the cells but the info in them is taken from another sheet so it is not possible to do that.

Thanks Scott

[maybelle]

I think that you need to do average(personsnameCell,ppersonsnameCell,personsnameCell,personsnameCell) instead of average(personsnameCell:personsnameCell). Basically select the cell you want in a sheet, type "," select the next cell on the next sheet and so on.

[NBVC]

Is the person's name separated with spaces?

Try:

=IF(ISERROR(AVERAGE('persons name:persons name'!h16)),"",AVERAGE('Persons name:persons name'!h16))

Note the apostrophe's

[DonkeyOte]

If as you say the Average is conditional (exclude zero H16 values) then if you're not prepared to utilise a Helper on each sheet then you're looking at pretty expensive / volatile SUMPRODUCT / Array formulae.

John McGimpsey has a page outlining a variety of approaches for conditional 3D calculations: http://www.mcgimpsey.com/excel/threedsumif.html

All of which can be adapted to conduct an Average - ie 3D SUM / 3D COUNTIF (<>0)

[NBVC]

I took it as the OP is getting Div/0 error when the cells all have 0's or are blank..... and is trying to not have that error display...

[sccottt]

thanks for the quick replies! the formula is as you suggested NBVC but just get a VALUE error, looks like it will need to be the 3D conditional formulas just need to create one that will do the job, if anyone has a pointer were to start id be greatful.

regards Scott

[NBVC]

Can you explain exactly what you need... ie. what is being averaged and based on what conditions, etc.

[sccottt]

im trying to average, the average talk time across a team. in h16 of each individuals sheet is there talk time for the day in h16 on the summary sheet is the average for the team, some people work part time or are off. i know i could just delete the zero to get the average but the individuals average talk time is taken from another source so i cannot delete the formula out.hope this makes sense

Thanks scott

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[sccottt]

i tired to add one with my last post but keep getting a forum database error

[NBVC]

Try zipping it.. and keep the size below the allowable limit

[sccottt]

here is a dummy file i need an average talk time in the summary sheet for the average talk time for the team thanks

[NBVC]

Want to make sure I understand...

... if I enter this formula:

=AVERAGE('SPARE 1:SPARE 6'!H16)

I get a result, even if all H16 cells are 0... I get 0....

Am I misunderstanding you?

[sccottt]

no your understanding right and given my first post with the forumula thats what i expected it to be but i need it to exclude cells with a zero, thought an if is error would work but doesnt any ideas how to get an average of the teams talktime and exclude 0 were people werent in on that day etc i.e i need it to average the cells with an value in and ignore it if it contains a zero. hope this makes sense and thanks for you help so far

[NBVC]

Ok. See attached.

First, if your actual sheet names do not have the pattern you show or similar... like if they are totally different, then list the sheetnames in your summary sheet somewhere... see P1:P6.. then name that list something like "TabNames", through Insert|Name|Define.

Then use formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&TabNames&"'!h16"),"<>0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!h16"),"<>0"))

See H16.


If your sheets are named in a pattern similar to your sample, then no need to create a list and named range... just use formula as per:

=SUMPRODUCT(SUMIF(INDIRECT("'SPARE "&ROW(INDIRECT("1:6"))&"'!H16"),"<>0"))/SUMPRODUCT(COUNTIF(INDIRECT("'SPARE "&ROW(INDIRECT("1:6"))&"'!H16"),"<>0"))

See P16.

[sccottt]

hi thanks for the reply ive tried the way you suggested (the first one as the sheets are peoples names) however as people leave and new people join the company the names on the sheets changes, so i dont think it will work in the long run. is there anyother way of doing it so that if the names of the sheet change it would still work? thanks for your help

[NBVC]

An alternative that would work would be to install a free addin called Morefunc.xll from here: Morefunc


Then insert a blank sheet before all the named sheets and call this sheet 'Start' and insert another sheet just before your summary sheet and after all the named sheets and call this 'End' (without the quotes).

Then apply formula below:

=SUMPRODUCT(--(THREED(Start:End!H16)<>0),THREED(Start:End!H16))/SUMPRODUCT(--(THREED(Start:End!H16)<>0))

Now you can add and delete sheet between these "bookends" as you desire.