Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
=(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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL, VSTO: Fastest way to access multiple cells | Excel Discussion (Misc queries) | |||
Excel 2003 will not display color fonts or color fill cells | Excel Worksheet Functions | |||
How can I have excel search and add multiple cells to find a targe | Excel Discussion (Misc queries) | |||
Excel cannot shift nonblank cells | Excel Discussion (Misc queries) | |||
How do I merge cells in Excel, like just 2 cells to make one big . | Excel Discussion (Misc queries) |