Calculating averages when not in range and avoiding DIV/0

[tommyturner]

Hi

I am new to the power of Excel and am in need of some help! I have searched and read a number of similar problems but nothing quite matches or has worked for me, so I'm sorry if this a repetitive question!

I will set the scene. I have a sheet for each week of the year and each sheet has the days of the week in horizontal columns. Under each day there are a number of sub-columns with a different variable/category in each (10 in total). These variables are repeated every day.
So the first variable on Monday is the same first variable/category on Tuesday. They are therefore separated and not in continuous range.
All of the cells are blank until they will be filled with a number throughout the year. However in a number of cases, the cells will remain blank and won't be filled.

Therefore when calculating weekly averages for each category, the basic average formula returns DIV/0 which I want to avoid as I have yearly averages etc. Any other formulas I have tried say that there are too many arguements, presumably because the data is not continuous?

If someone could help me with a formula that will calculate an average from cells which are in some cases blank and not in continuous range, I would be extremely grateful!

Many thanks

Tom

 

[Gerald Higgins]

Hi, welcome to the board.

I think the basic AVERAGE() function will only return a DIV/0 error if ALL the cells are empty. If a single one contains a value and all the others are blank, you won't get a DIV/0 error. Does this help you get round the problem ?

If not, a quick and dirty way is to do something like this -

=if(iserror(YOURAVERAGEFUNCTION),"",YOURAVERAGEFUNCTION)

 

[barry houdini]

You can use COUNT, e.g. to average D3, F6 and Z7 try

=IF(COUNT(D3,F6,Z7),AVERAGE(D3,F6,Z7),"")

 

[jdmc45]

Hi

I am new to the power of Excel and am in need of some help! I have searched and read a number of similar problems but nothing quite matches or has worked for me, so I'm sorry if this a repetitive question!

I will set the scene. I have a sheet for each week of the year and each sheet has the days of the week in horizontal columns. Under each day there are a number of sub-columns with a different variable/category in each (10 in total). These variables are repeated every day.
So the first variable on Monday is the same first variable/category on Tuesday. They are therefore separated and not in continuous range.
All of the cells are blank until they will be filled with a number throughout the year. However in a number of cases, the cells will remain blank and won't be filled.

Therefore when calculating weekly averages for each category, the basic average formula returns DIV/0 which I want to avoid as I have yearly averages etc. Any other formulas I have tried say that there are too many arguements, presumably because the data is not continuous?

If someone could help me with a formula that will calculate an average from cells which are in some cases blank and not in continuous range, I would be extremely grateful!

Many thanks

Tom

Use the AVERAGEA function, it won't count empty, blank or text cells

=AVERAGEA(...,...,etc)

 

[T. Valko]

Hi

I am new to the power of Excel and am in need of some help! I have searched and read a number of similar problems but nothing quite matches or has worked for me, so I'm sorry if this a repetitive question!

I will set the scene. I have a sheet for each week of the year and each sheet has the days of the week in horizontal columns. Under each day there are a number of sub-columns with a different variable/category in each (10 in total). These variables are repeated every day.
So the first variable on Monday is the same first variable/category on Tuesday. They are therefore separated and not in continuous range.
All of the cells are blank until they will be filled with a number throughout the year. However in a number of cases, the cells will remain blank and won't be filled.

Therefore when calculating weekly averages for each category, the basic average formula returns DIV/0 which I want to avoid as I have yearly averages etc. Any other formulas I have tried say that there are too many arguements, presumably because the data is not continuous?

If someone could help me with a formula that will calculate an average from cells which are in some cases blank and not in continuous range, I would be extremely grateful!

Many thanks

Tom

If you're using Excel 2007 or later try something like this...

=IFERROR(AVERAGE(...),"")

Fill in the details of the average function.

 

[tommyturner]

Many thanks for the replies everybody!

In my case both Barry and T. Valko's solutions worked for me. The basic average function does leave div/0 when many of the cells will be blank for some weeks but thanks anyway jdmc45. Gerald your solution returned the all too familiar 'too many arguements' but thanks for the idea.

Again thanks ever so much for your help, you will probably hear from me again!!

Tom

 

[snoopyhr]

I realize it is already answered, but here is another approach

=IF(C1:C12<>"",AVERAGE(C1:C12),"")

the range to average is c1 to c12

 

[T. Valko]

Many thanks for the replies everybody!

In my case both Barry and T. Valko's solutions worked for me. The basic average function does leave div/0 when many of the cells will be blank for some weeks but thanks anyway jdmc45. Gerald your solution returned the all too familiar 'too many arguements' but thanks for the idea.

Again thanks ever so much for your help, you will probably hear from me again!!

Tom

You're welcome. Thanks for the feedback!