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!