calculations 1

[wafs]

I need to have rows still calculate even if there are NA in the rows. Is this possible?

 

[AnotherHiggins]

My crystal ball is a bit hazy.

What do you mean 'calculate'? What formula are you having trouble with?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[wafs]

Sorry sum the rows even if it has NA in some of the rows.

 

[SkipVought]

Use IFNA() function to gracefully substitute a ZERO or whatever.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

I found a calculation that goes right into a cell.

=sum_visible_cells(B4:B100)

But I get an error (A value used in the formula is of the wrong data type).

I got this off the Microsoft web site, can any help me with why this is not working.

 

[SkipVought]

Hi,
[tt]
=SUBTOTAL(9,YourRange)
[/tt]
does the same thing.

But first you must Filter the rows containing #NA.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[AnotherHiggins]

Forget about the User Defined Function right now. You started off with trying to sum cells. Now you are trying to sum visible cells?

What exactly is in the cells that are giving you trouble?

Is it a text string, "NA", or is it the error message, "#N/A"? If you are unsure, then answer this: are there formulae in those cells?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[wafs]

All my rows containing #N/A are all group together and hidden.

#N/A has a formula in the cell and the row is hidden. I'm working in a cell and not a macro.

 

[wafs]

tried it Skip and I got #N/A for an answer.

 

[AnotherHiggins]

If you can make this change, I'd suggest going with Skip's earlier suggestion of using ISNA in the functions that are producing these unsightly returns.

Example:
Let's say you have
=VLookup(A1,B1:C10,2,0)
which is returning #N/A because the value in A1 isn't found in the range B1:C10. Then change the formula to this:
=If(IsNA(VLlookup(A1,B1:C10,2,0)),"",VLookup(A1,B1:C10,2,0))
Now you can just sum an entire column
=Sum(D)

If that would be entirely too much trouble, then you still needn't worry about hiding the rows containing #N/A. Or hide them to improve the appearance of the sheet. It is really up to you.

The following formula will work either way:
[COLOR=red white]=SumIf(D,"<>#N/A")[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[macropod]

Hi wafs,

Try:
=SUM(IF(ISERROR(A1:A100),,A1:A100))
as an array formula (ie input with ctrl-shift-enter), where A1:A100 is the range you want to sum.

Cheers

 

[KenWright]

You generally have two options available to you:-

1) Fix the errors at source (Usually the advisable route), and best handled with the syntax, =IF(ISNA(Your_Formula),0,Your_Formula)

2) Compensate for the errors using an array formula that ignores errors such as the ones given, or using a non-array approach:-

=SUMIF($A$1:$A$25,"<>#N/A")

Note that this does not take into account cells that are only visible

Regards
Ken............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Personally i would use approach number 1 to get rid of the errors at source, and then use SUBTOTAL() if you need to sum only visible cells. You should also note though that SUBTOTAL in versions of Excel prior to 2003 will only work if the rows have been hidden by use of a filter, whereas in 2003 extra arguments were added to allow you to ignore manually hidden rows too.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[wafs]

I ended up changeing the subtotal(9,A1:A5) to subtotal(109,a1:a5) which worked. the 109 skips hidded rows.