I am getting this error - "#VALUE!" when attempting to perform a function
"=AVERAGE(D3:D4,D6,D8:D11,D13:D14,D27:D29,D39,D41, D47:D49,D51:D53,D56:D60,D62,D68:D69,D71:D72,D74:D7 7,D84:D87,D99,D102,D115,D117:D121,D131:D132,D139,D 144:D163,D164)"

Some of the cells where information is being pulled from is "N/A" and other
cells contain "%" amounts.

Is it possible to complete an total Average function where the information I
am using contains text and number formats and does not create the "#VALUE!"
error? This is in a working spreadsheet where the information changes from
N/A to % often and is unpredictable as to which cell is altered. I would
like a total average that is a number and not an error.

Please help!

=IF(ISERROR(D3:D4,D6,D8:D11,D13:D14,D27:D29,D39,D4 1,D47:D49,D51:D53,D56:D60,D62,D68:D69,D71:D72,D74: D77,D84:D87,D99,D102,D115,D117:D121,D131:D132,D139 ,D144:D163,D164),"",D3:D4,D6,D8:D11,D13:D14,D27:D2 9,D39,D41,D47:D49,D51:D53,D56:D60,D62,D68:D69,D71: D72,D74:D77,D84:D87,D99,D102,D115,D117:D121,D131:D 132,D139,D144:D163,D164))
ctrl+shift+enter, not just enter


"whispagirl" wrote:

I am getting this error - "#VALUE!" when attempting to perform a function
"=AVERAGE(D3:D4,D6,D8:D11,D13:D14,D27:D29,D39,D41, D47:D49,D51:D53,D56:D60,D62,D68:D69,D71:D72,D74:D7 7,D84:D87,D99,D102,D115,D117:D121,D131:D132,D139,D 144:D163,D164)"

Some of the cells where information is being pulled from is "N/A" and other
cells contain "%" amounts.

Is it possible to complete an total Average function where the information I
am using contains text and number formats and does not create the "#VALUE!"
error? This is in a working spreadsheet where the information changes from
N/A to % often and is unpredictable as to which cell is altered. I would
like a total average that is a number and not an error.

Please help!

I presume you have text "N/A" and not error values #N/A (otherwise you'd get
#N/A not #VALUE!). Text doesn't cause an error for AVERAGE, AVERAGE just
ignores it. Do you have other error values within the data to be averaged
(e.g. #VALUE!). You say you want to exclude percentages, that might be
difficult because percentages are stored as numbers in Excel so the only way
to differentiate percentages from numbers migt be if all your % were less
than 1 and all "numbers" over 1, is that the case?

You probably don't want to hear this but the best answer is probably -
spreadsheet re-design. It's not good practice to have multiple data types
with one range or to have data grouped so that you need to sum or average
discrete ranges.

"whispagirl" wrote:

I am getting this error - "#VALUE!" when attempting to perform a function
"=AVERAGE(D3:D4,D6,D8:D11,D13:D14,D27:D29,D39,D41, D47:D49,D51:D53,D56:D60,D62,D68:D69,D71:D72,D74:D7 7,D84:D87,D99,D102,D115,D117:D121,D131:D132,D139,D 144:D163,D164)"

Some of the cells where information is being pulled from is "N/A" and other
cells contain "%" amounts.

Is it possible to complete an total Average function where the information I
am using contains text and number formats and does not create the "#VALUE!"
error? This is in a working spreadsheet where the information changes from
N/A to % often and is unpredictable as to which cell is altered. I would
like a total average that is a number and not an error.

Please help!

No, what I said was I wanted the averages to pull the % in a total average
cell at the bottom of the spreadsheet. And, I wondered if the pulling of the
"n/a's" was what was causing the #Value! error that I was getting. I never
stated that I wanted to ignore the %'s. It is the %'s that I need to average
at the total Average cell.



"daddylonglegs" wrote:

I presume you have text "N/A" and not error values #N/A (otherwise you'd get
#N/A not #VALUE!). Text doesn't cause an error for AVERAGE, AVERAGE just
ignores it. Do you have other error values within the data to be averaged
(e.g. #VALUE!). You say you want to exclude percentages, that might be
difficult because percentages are stored as numbers in Excel so the only way
to differentiate percentages from numbers migt be if all your % were less
than 1 and all "numbers" over 1, is that the case?

You probably don't want to hear this but the best answer is probably -
spreadsheet re-design. It's not good practice to have multiple data types
with one range or to have data grouped so that you need to sum or average
discrete ranges.

"whispagirl" wrote:

I am getting this error - "#VALUE!" when attempting to perform a function
"=AVERAGE(D3:D4,D6,D8:D11,D13:D14,D27:D29,D39,D41, D47:D49,D51:D53,D56:D60,D62,D68:D69,D71:D72,D74:D7 7,D84:D87,D99,D102,D115,D117:D121,D131:D132,D139,D 144:D163,D164)"

Some of the cells where information is being pulled from is "N/A" and other
cells contain "%" amounts.

Is it possible to complete an total Average function where the information I
am using contains text and number formats and does not create the "#VALUE!"
error? This is in a working spreadsheet where the information changes from
N/A to % often and is unpredictable as to which cell is altered. I would
like a total average that is a number and not an error.

Please help!

OK, I see, I misunderstood your intent but that still doesn't really answer
my questions. If N/A is text then it won't cause a #VALUE! error, do you have
other error values in the range?

Did you try teethless Mama's suggestion? You'd need to put an AVERAGE
formula round that

"whispagirl" wrote:

No, what I said was I wanted the averages to pull the % in a total average
cell at the bottom of the spreadsheet. And, I wondered if the pulling of the
"n/a's" was what was causing the #Value! error that I was getting. I never
stated that I wanted to ignore the %'s. It is the %'s that I need to average
at the total Average cell.



"daddylonglegs" wrote:

I presume you have text "N/A" and not error values #N/A (otherwise you'd get
#N/A not #VALUE!). Text doesn't cause an error for AVERAGE, AVERAGE just
ignores it. Do you have other error values within the data to be averaged
(e.g. #VALUE!). You say you want to exclude percentages, that might be
difficult because percentages are stored as numbers in Excel so the only way
to differentiate percentages from numbers migt be if all your % were less
than 1 and all "numbers" over 1, is that the case?

You probably don't want to hear this but the best answer is probably -
spreadsheet re-design. It's not good practice to have multiple data types
with one range or to have data grouped so that you need to sum or average
discrete ranges.

"whispagirl" wrote:

I am getting this error - "#VALUE!" when attempting to perform a function
"=AVERAGE(D3:D4,D6,D8:D11,D13:D14,D27:D29,D39,D41, D47:D49,D51:D53,D56:D60,D62,D68:D69,D71:D72,D74:D7 7,D84:D87,D99,D102,D115,D117:D121,D131:D132,D139,D 144:D163,D164)"

Some of the cells where information is being pulled from is "N/A" and other
cells contain "%" amounts.

Is it possible to complete an total Average function where the information I
am using contains text and number formats and does not create the "#VALUE!"
error? This is in a working spreadsheet where the information changes from
N/A to % often and is unpredictable as to which cell is altered. I would
like a total average that is a number and not an error.

Please help!

whispagirl wrote:
I am getting this error - "#VALUE!" when attempting to perform a function
"=AVERAGE(D3:D4,D6,D8:D11,D13:D14,D27:D29,D39,D41, D47:D49,D51:D53,D56:D60,D62,D68:D69,D71:D72,D74:D7 7,D84:D87,D99,D102,D115,D117:D121,D131:D132,D139,D 144:D163,D164)"
Some of the cells where information is being pulled from is "N/A" and other
cells contain "%" amounts.
Is it possible to complete an total Average function where the information I
am using contains text and number formats and does not create the "#VALUE!"
error?

AVERAGE() already ignores cells with text -- that is, real text. I
presume you mean that some cells have Excel errors such as #NA. And I
presume that you merely want AVERAGE() to ignore those cells, but
otherwise compute an average of the non-error cells.

The only way I know how to do that is to fix the problem at its source:
do not allow Excel errors to creep into cells in the first place --
which is a good idea anyway. So, in each cell that might result in an
Excel error (D3, D4, etc), change your formula from =formula to
=if(iserror(formula),"N/A",formula). Messy, I know. But I don't know
of any Excel option that would avoid putting Excel errors into cells
:-(. Note: In some formulas, you might be able to avoid
iserror(formula) by avoiding the error during computation. For
example, if the formula were =A1/B1, you could avoid a #DIV/0! error by
doing =if(B1=0,"N/A",A1/B1), which is more efficient than
=if(iserror(A1/B1),...).

Time to upgrade to xl2007 <vbg. (I haven't.)

There's a new =iferror() function that will make this kind of thing less messy.



wrote:

whispagirl wrote:
I am getting this error - "#VALUE!" when attempting to perform a function
"=AVERAGE(D3:D4,D6,D8:D11,D13:D14,D27:D29,D39,D41, D47:D49,D51:D53,D56:D60,D62,D68:D69,D71:D72,D74:D7 7,D84:D87,D99,D102,D115,D117:D121,D131:D132,D139,D 144:D163,D164)"
Some of the cells where information is being pulled from is "N/A" and other
cells contain "%" amounts.
Is it possible to complete an total Average function where the information I
am using contains text and number formats and does not create the "#VALUE!"
error?

AVERAGE() already ignores cells with text -- that is, real text. I
presume you mean that some cells have Excel errors such as #NA. And I
presume that you merely want AVERAGE() to ignore those cells, but
otherwise compute an average of the non-error cells.

The only way I know how to do that is to fix the problem at its source:
do not allow Excel errors to creep into cells in the first place --
which is a good idea anyway. So, in each cell that might result in an
Excel error (D3, D4, etc), change your formula from =formula to
=if(iserror(formula),"N/A",formula). Messy, I know. But I don't know
of any Excel option that would avoid putting Excel errors into cells
:-(. Note: In some formulas, you might be able to avoid
iserror(formula) by avoiding the error during computation. For
example, if the formula were =A1/B1, you could avoid a #DIV/0! error by
doing =if(B1=0,"N/A",A1/B1), which is more efficient than
=if(iserror(A1/B1),...).

--

Dave Peterson