averages that ignore #VALUE! cells
 

I am making a spreadsheet in excel that will add subtract times and then
average the totals of multiples of these. My problem is that some of the
tiems do not need to be entered causing a # VALUE! in teh line where my
subtraction formula. This is fine and needs to stay this way. My problem is
that the average then has a #VALUE! instead of an average. How do I skip
over cells that have the #VALUE! ? I ammaking htis spreadsheet for someone
else, so I can not correct it each time this happens. This is what my sheet
is like:
A B C
1 1:15 1:30 15 =(B1-A1)*1440
2 12:30 1:00 30 =(B2-A2)*1440
3 3:15 N/A #VALUE! =(B3-A3)*1440
4 #VALUE! =AVERAGE(C1:C3)

Any suggestions to get the average to actaully average the amounts that are
not #VALUE! ??? Any help would be fantastic!!

averages that ignore #VALUE! cells
 

=AVERAGE(IF(NOT(ISERROR(C1:C3)),C1:C3))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"ericaamousseau" wrote in message
...
I am making a spreadsheet in excel that will add subtract times and then
average the totals of multiples of these. My problem is that some of the
tiems do not need to be entered causing a # VALUE! in teh line where my
subtraction formula. This is fine and needs to stay this way. My problem
is
that the average then has a #VALUE! instead of an average. How do I skip
over cells that have the #VALUE! ? I ammaking htis spreadsheet for
someone
else, so I can not correct it each time this happens. This is what my
sheet
is like:
A B C
1 1:15 1:30 15 =(B1-A1)*1440
2 12:30 1:00 30 =(B2-A2)*1440
3 3:15 N/A #VALUE! =(B3-A3)*1440
4 #VALUE! =AVERAGE(C1:C3)

Any suggestions to get the average to actaully average the amounts that
are
not #VALUE! ??? Any help would be fantastic!!

averages that ignore #VALUE! cells
 

Try one of these...

This is an array formula** :

=AVERAGE(IF(ISNUMBER(C1:C3),C1:C3))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Or this normally entered formula:

=SUMIF(C1:C3,"<#VALUE!")/COUNT(C1:C3)

This formula will only work if the errors are of the #VALUE! type.

--
Biff
Microsoft Excel MVP


"ericaamousseau" wrote in message
...
I am making a spreadsheet in excel that will add subtract times and then
average the totals of multiples of these. My problem is that some of the
tiems do not need to be entered causing a # VALUE! in teh line where my
subtraction formula. This is fine and needs to stay this way. My problem
is
that the average then has a #VALUE! instead of an average. How do I skip
over cells that have the #VALUE! ? I ammaking htis spreadsheet for
someone
else, so I can not correct it each time this happens. This is what my
sheet
is like:
A B C
1 1:15 1:30 15 =(B1-A1)*1440
2 12:30 1:00 30 =(B2-A2)*1440
3 3:15 N/A #VALUE! =(B3-A3)*1440
4 #VALUE! =AVERAGE(C1:C3)

Any suggestions to get the average to actaully average the amounts that
are
not #VALUE! ??? Any help would be fantastic!!

averages that ignore #VALUE! cells
 

This is perfect, thanks so much. One more questions though, how do I get it
to still ignore the cells with no numbers? It averaged all of the cells, not
just the ones without an error or with a value. Thanks so much for your help!

"Bob Phillips" wrote:

=AVERAGE(IF(NOT(ISERROR(C1:C3)),C1:C3))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"ericaamousseau" wrote in message
...
I am making a spreadsheet in excel that will add subtract times and then
average the totals of multiples of these. My problem is that some of the
tiems do not need to be entered causing a # VALUE! in teh line where my
subtraction formula. This is fine and needs to stay this way. My problem
is
that the average then has a #VALUE! instead of an average. How do I skip
over cells that have the #VALUE! ? I ammaking htis spreadsheet for
someone
else, so I can not correct it each time this happens. This is what my
sheet
is like:
A B C
1 1:15 1:30 15 =(B1-A1)*1440
2 12:30 1:00 30 =(B2-A2)*1440
3 3:15 N/A #VALUE! =(B3-A3)*1440
4 #VALUE! =AVERAGE(C1:C3)

Any suggestions to get the average to actaully average the amounts that
are
not #VALUE! ??? Any help would be fantastic!!

averages that ignore #VALUE! cells
 

Great thanks! I really like the firt formula, but now the average is
including cells that do not have a vaule. How can I use this same formula,
but have the average only include cells that have numbers in them? Thanks
again for all the help!

"Bob Phillips" wrote:

=AVERAGE(IF(NOT(ISERROR(C1:C3)),C1:C3))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"ericaamousseau" wrote in message
...
I am making a spreadsheet in excel that will add subtract times and then
average the totals of multiples of these. My problem is that some of the
tiems do not need to be entered causing a # VALUE! in teh line where my
subtraction formula. This is fine and needs to stay this way. My problem
is
that the average then has a #VALUE! instead of an average. How do I skip
over cells that have the #VALUE! ? I ammaking htis spreadsheet for
someone
else, so I can not correct it each time this happens. This is what my
sheet
is like:
A B C
1 1:15 1:30 15 =(B1-A1)*1440
2 12:30 1:00 30 =(B2-A2)*1440
3 3:15 N/A #VALUE! =(B3-A3)*1440
4 #VALUE! =AVERAGE(C1:C3)

Any suggestions to get the average to actaully average the amounts that
are
not #VALUE! ??? Any help would be fantastic!!

averages that ignore #VALUE! cells
 

Try it like this (aaray entered):

=AVERAGE(IF(ISNUMBER(C1:C3),C1:C3))

Or:

=IF(COUNT(C1:C3),AVERAGE(IF(ISNUMBER(C1:C3),C1:C3) ),"")


--
Biff
Microsoft Excel MVP


"ericaamousseau" wrote in message
...
Great thanks! I really like the firt formula, but now the average is
including cells that do not have a vaule. How can I use this same
formula,
but have the average only include cells that have numbers in them? Thanks
again for all the help!

"Bob Phillips" wrote:

=AVERAGE(IF(NOT(ISERROR(C1:C3)),C1:C3))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"ericaamousseau" wrote in
message
...
I am making a spreadsheet in excel that will add subtract times and then
average the totals of multiples of these. My problem is that some of
the
tiems do not need to be entered causing a # VALUE! in teh line where my
subtraction formula. This is fine and needs to stay this way. My
problem
is
that the average then has a #VALUE! instead of an average. How do I
skip
over cells that have the #VALUE! ? I ammaking htis spreadsheet for
someone
else, so I can not correct it each time this happens. This is what my
sheet
is like:
A B C
1 1:15 1:30 15 =(B1-A1)*1440
2 12:30 1:00 30 =(B2-A2)*1440
3 3:15 N/A #VALUE! =(B3-A3)*1440
4 #VALUE! =AVERAGE(C1:C3)

Any suggestions to get the average to actaully average the amounts that
are
not #VALUE! ??? Any help would be fantastic!!

averages that ignore #VALUE! cells
 

The first one is perfect! Thank you somuch for your help!

"T. Valko" wrote:

Try it like this (aaray entered):

=AVERAGE(IF(ISNUMBER(C1:C3),C1:C3))

Or:

=IF(COUNT(C1:C3),AVERAGE(IF(ISNUMBER(C1:C3),C1:C3) ),"")


--
Biff
Microsoft Excel MVP


"ericaamousseau" wrote in message
...
Great thanks! I really like the firt formula, but now the average is
including cells that do not have a vaule. How can I use this same
formula,
but have the average only include cells that have numbers in them? Thanks
again for all the help!

"Bob Phillips" wrote:

=AVERAGE(IF(NOT(ISERROR(C1:C3)),C1:C3))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"ericaamousseau" wrote in
message
...
I am making a spreadsheet in excel that will add subtract times and then
average the totals of multiples of these. My problem is that some of
the
tiems do not need to be entered causing a # VALUE! in teh line where my
subtraction formula. This is fine and needs to stay this way. My
problem
is
that the average then has a #VALUE! instead of an average. How do I
skip
over cells that have the #VALUE! ? I ammaking htis spreadsheet for
someone
else, so I can not correct it each time this happens. This is what my
sheet
is like:
A B C
1 1:15 1:30 15 =(B1-A1)*1440
2 12:30 1:00 30 =(B2-A2)*1440
3 3:15 N/A #VALUE! =(B3-A3)*1440
4 #VALUE! =AVERAGE(C1:C3)

Any suggestions to get the average to actaully average the amounts that
are
not #VALUE! ??? Any help would be fantastic!!

averages that ignore #VALUE! cells
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ericaamousseau" wrote in message
...
The first one is perfect! Thank you somuch for your help!

"T. Valko" wrote:

Try it like this (aaray entered):

=AVERAGE(IF(ISNUMBER(C1:C3),C1:C3))

Or:

=IF(COUNT(C1:C3),AVERAGE(IF(ISNUMBER(C1:C3),C1:C3) ),"")


--
Biff
Microsoft Excel MVP


"ericaamousseau" wrote in
message
...
Great thanks! I really like the firt formula, but now the average is
including cells that do not have a vaule. How can I use this same
formula,
but have the average only include cells that have numbers in them?
Thanks
again for all the help!

"Bob Phillips" wrote:

=AVERAGE(IF(NOT(ISERROR(C1:C3)),C1:C3))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly
brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to
excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"ericaamousseau" wrote in
message
...
I am making a spreadsheet in excel that will add subtract times and
then
average the totals of multiples of these. My problem is that some
of
the
tiems do not need to be entered causing a # VALUE! in teh line where
my
subtraction formula. This is fine and needs to stay this way. My
problem
is
that the average then has a #VALUE! instead of an average. How do I
skip
over cells that have the #VALUE! ? I ammaking htis spreadsheet for
someone
else, so I can not correct it each time this happens. This is what
my
sheet
is like:
A B C
1 1:15 1:30 15 =(B1-A1)*1440
2 12:30 1:00 30 =(B2-A2)*1440
3 3:15 N/A #VALUE! =(B3-A3)*1440
4 #VALUE! =AVERAGE(C1:C3)

Any suggestions to get the average to actaully average the amounts
that
are
not #VALUE! ??? Any help would be fantastic!!