Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
texanfanrocket
 
Posts: n/a
Default How do I ignore cells in Excel?

I have a formula written in a column ten rows deep. Sometimes I use less
than 10 inputs. I'm trying to formulate an average of this column, but if I
don't input all 10 inputs, my average comes back with #DIV/0!

How can I get this column to average when some of the rows aren't being used?
  #2   Report Post  
Ray A
 
Posts: n/a
Default

Assuming data in A1:A10 In cell A11 use =average(A1:A10) The same applies if
data is in A1:A5 in cell A11 use the same formula. It will not consider blank
cells
HTH

"texanfanrocket" wrote:

I have a formula written in a column ten rows deep. Sometimes I use less
than 10 inputs. I'm trying to formulate an average of this column, but if I
don't input all 10 inputs, my average comes back with #DIV/0!

How can I get this column to average when some of the rows aren't being used?

  #3   Report Post  
texanfanrocket
 
Posts: n/a
Default

I tried that. The problem might be that all of the 10 cells contain formulas
from different cells. When the inputs for those formulas are left out, it
gives the same #DIV/0! response.

While those responses don't mean anything, it is imperative that the cell
avg. is.

"Ray A" wrote:

Assuming data in A1:A10 In cell A11 use =average(A1:A10) The same applies if
data is in A1:A5 in cell A11 use the same formula. It will not consider blank
cells
HTH

"texanfanrocket" wrote:

I have a formula written in a column ten rows deep. Sometimes I use less
than 10 inputs. I'm trying to formulate an average of this column, but if I
don't input all 10 inputs, my average comes back with #DIV/0!

How can I get this column to average when some of the rows aren't being used?

  #4   Report Post  
Ray A
 
Posts: n/a
Default

Look at a combination of =if(iserror(
NOt clear without fooling with it how that will work but you can use the
iserror to filter out the #div/0

"texanfanrocket" wrote:

I tried that. The problem might be that all of the 10 cells contain formulas
from different cells. When the inputs for those formulas are left out, it
gives the same #DIV/0! response.

While those responses don't mean anything, it is imperative that the cell
avg. is.

"Ray A" wrote:

Assuming data in A1:A10 In cell A11 use =average(A1:A10) The same applies if
data is in A1:A5 in cell A11 use the same formula. It will not consider blank
cells
HTH

"texanfanrocket" wrote:

I have a formula written in a column ten rows deep. Sometimes I use less
than 10 inputs. I'm trying to formulate an average of this column, but if I
don't input all 10 inputs, my average comes back with #DIV/0!

How can I get this column to average when some of the rows aren't being used?

  #5   Report Post  
JE McGimpsey
 
Posts: n/a
Default

One way:

=IF(COUNTA1:A10),AVERAGE(A1:A10),"No inputs")

In article ,
"texanfanrocket" wrote:

I tried that. The problem might be that all of the 10 cells contain formulas
from different cells. When the inputs for those formulas are left out, it
gives the same #DIV/0! response.

While those responses don't mean anything, it is imperative that the cell
avg. is.



  #6   Report Post  
texanfanrocket
 
Posts: n/a
Default

So try something like =IF(iserror #div/0!)AVERAGE(A1:A10)? That didn't work.
I'm not familiar with iserror inputs.

"Ray A" wrote:

Look at a combination of =if(iserror(
NOt clear without fooling with it how that will work but you can use the
iserror to filter out the #div/0

"texanfanrocket" wrote:

I tried that. The problem might be that all of the 10 cells contain formulas
from different cells. When the inputs for those formulas are left out, it
gives the same #DIV/0! response.

While those responses don't mean anything, it is imperative that the cell
avg. is.

"Ray A" wrote:

Assuming data in A1:A10 In cell A11 use =average(A1:A10) The same applies if
data is in A1:A5 in cell A11 use the same formula. It will not consider blank
cells
HTH

"texanfanrocket" wrote:

I have a formula written in a column ten rows deep. Sometimes I use less
than 10 inputs. I'm trying to formulate an average of this column, but if I
don't input all 10 inputs, my average comes back with #DIV/0!

How can I get this column to average when some of the rows aren't being used?

  #7   Report Post  
texanfanrocket
 
Posts: n/a
Default

No, that just changed the answer to #NAME?

"JE McGimpsey" wrote:

One way:

=IF(COUNTA1:A10),AVERAGE(A1:A10),"No inputs")

In article ,
"texanfanrocket" wrote:

I tried that. The problem might be that all of the 10 cells contain formulas
from different cells. When the inputs for those formulas are left out, it
gives the same #DIV/0! response.

While those responses don't mean anything, it is imperative that the cell
avg. is.


  #8   Report Post  
JE McGimpsey
 
Posts: n/a
Default

Missed a paren:

=IF(COUNT(A1:A10),AVERAGE(A1:A10),"No inputs")

In article ,
"texanfanrocket" wrote:

No, that just changed the answer to #NAME?

"JE McGimpsey" wrote:

One way:

=IF(COUNTA1:A10),AVERAGE(A1:A10),"No inputs")

  #9   Report Post  
Max
 
Posts: n/a
Default

Try, array-entered (press CTRL+SHIFT+ENTER)
something like:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


  #10   Report Post  
texanfanrocket
 
Posts: n/a
Default

=(E24/O24)^(1/A24)-1. << This is the formula in each of the 10 cells. They
are in a % format. And, unfortunately, I'm back with a #DIV/0! response.

"JE McGimpsey" wrote:

Missed a paren:

=IF(COUNT(A1:A10),AVERAGE(A1:A10),"No inputs")

In article ,
"texanfanrocket" wrote:

No, that just changed the answer to #NAME?

"JE McGimpsey" wrote:

One way:

=IF(COUNTA1:A10),AVERAGE(A1:A10),"No inputs")




  #11   Report Post  
texanfanrocket
 
Posts: n/a
Default

That stopped the error message, but it didn't compute the answer. It came
back with 0%.

"Max" wrote:

Try, array-entered (press CTRL+SHIFT+ENTER)
something like:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----



  #12   Report Post  
JE McGimpsey
 
Posts: n/a
Default

Ah, misunderstood where the #DIV/0 was coming from.

In general, it's better to trap the error closest to its source, so I
would modify the formulae in A1:A10 to

=IF(COUNT(A24,O24)=2,(E24/O24)^(1/A24)-1,"No Input")

Then average with the formula I gave.



In article ,
"texanfanrocket" wrote:

=(E24/O24)^(1/A24)-1. << This is the formula in each of the 10 cells. They
are in a % format. And, unfortunately, I'm back with a #DIV/0! response.

  #13   Report Post  
Max
 
Posts: n/a
Default

Perhaps try formatting the result to more decimal places and/or use
scientific. It could be the correct result is already returned.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"texanfanrocket" wrote in message
...
That stopped the error message, but it didn't compute the answer. It came
back with 0%.

"Max" wrote:

Try, array-entered (press CTRL+SHIFT+ENTER)
something like:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
EXCEL, VSTO: Fastest way to access multiple cells Alan Excel Discussion (Misc queries) 1 June 1st 05 02:45 PM
Excel 2003 will not display color fonts or color fill cells DaveC Excel Worksheet Functions 1 April 11th 05 04:38 PM
How can I have excel search and add multiple cells to find a targe Blakepro Excel Discussion (Misc queries) 1 April 1st 05 02:37 AM
Excel cannot shift nonblank cells Mr. Maz. Excel Discussion (Misc queries) 2 March 18th 05 12:10 AM
How do I merge cells in Excel, like just 2 cells to make one big . chattacat Excel Discussion (Misc queries) 2 January 19th 05 05:25 PM


All times are GMT +1. The time now is 09:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"