Hidden rows not included in formula

[Guest]

I have a spreadsheet that contains hidden rows (inactive accounts). I want to put in a formula that will for example calculate the average of all values in a column. The problem that I have is that some of the cells contain formulas and since some of the accounts are not updated anymore they contain #DIV/0! and that screws up my calculations. Is there a way I can omit hidden rows from my formula? I know I can enter the formula manually and just skip the rows, but when you're dealing with 200+ rows, it seems a little tedious.
Thanks for your help.

 

[Don Guillett]

Might be best to write your formulas so you don't get div/0
--
Don Guillett
SalesAid Software
(e-mail address removed)

I have a spreadsheet that contains hidden rows (inactive accounts). I

want to put in a formula that will for example calculate the average of all
values in a column. The problem that I have is that some of the cells
contain formulas and since some of the accounts are not updated anymore they
contain #DIV/0! and that screws up my calculations. Is there a way I can
omit hidden rows from my formula? I know I can enter the formula manually
and just skip the rows, but when you're dealing with 200+ rows, it seems a
little tedious.

 

[Ragdyer]

If the hidden rows are the result of filtering, you could use the Subtotal
function.

If they were all manually hidden, one approach might be to select the
visible cells only, copy them to a column with no hidden rows (or below the
hidden rows), and then create a Sum formula.

To select *only* the visible cells, select the column concerned, then:
<F5> <Special>, click "VisibleCellsOnly", then <OK>.

Right click in this selection, and choose "Copy",
And then paste to a section without any hidden rows, and then create your
new Sum formula.

If you do this often, there is a "Visible Cells" icon that you can place on
your toolbar.
Right click in the toolbar, then:
<Customize> <Commands> tab, <Edit> ,
And scroll all the way down in the Commands box, and click and drag the
"SelectVisbleCells" icon to your toolbar.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I have a spreadsheet that contains hidden rows (inactive accounts). I

want to put in a formula that will for example calculate the average of all
values in a column. The problem that I have is that some of the cells
contain formulas and since some of the accounts are not updated anymore they
contain #DIV/0! and that screws up my calculations. Is there a way I can
omit hidden rows from my formula? I know I can enter the formula manually
and just skip the rows, but when you're dealing with 200+ rows, it seems a
little tedious.

 

[Guest]

My formulas are fine. The reason I get DIV/0! is that inactive accounts have a balance of zero and account balance is used to calculate the gain/loss.
That's why I don't want to include the hidden rows in my calculations.

 

[DDM]

Marta, you could revise your formula to =IF(ISERROR(your formula
here),"",your formula here) to trap the DIV/0 error. That's what Don Guillet
is suggesting.

Alternatively, if you happen to be using Excel 2003, you could use the
SUBTOTAL worksheet function to average your column data, so:
=SUBTOTAL(101,your range). This will ignore hidden rows.

If neither of these is acceptable, you can AutoFilter the data, creating a
custom filter to display only the rows that do NOT equal DIV/0. Then you can
use SUBTOTAL, so: =SUBTOTAL(1,your range). This will ignore rows hidden as a
result of AutoFilter. Note that this flavor of SUBTOTAL is available in
Excel 97-2003.

Hope this helps.

--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com

My formulas are fine. The reason I get DIV/0! is that inactive accounts

have a balance of zero and account balance is used to calculate the
gain/loss.

 

[Harlan Grove]

Marta, you could revise your formula to =IF(ISERROR(your formula
here),"",your formula here) to trap the DIV/0 error. That's what
Don Guillet is suggesting.

....

Personally, I hope Don was suggesting

=IF(Denominator,Formula,"")

since using ISERROR to trap #DIV/0! is overkill.