Quick Little Formula Question

[Naoki]

Currently I am using the following formula -

=SUMIF($J$24:$J$75,">0",$B$24:$B$75)

It is being used to sum data in column B, which contains weeks 1-52 i
the year, with data in every cell, despite the year not being over.

I want to report only data up until the current week however so...

It looks at Column J, which contains a value UP UNTIL the current wee
of the year, but contains an #REF! error for any other weeks (becaus
it links to a sheet which works on a by week basis).

So the ">0" is used to pull only data that is not #REF!. However,
now need to pull negative data, and obviously, this formula is pullin
only positive data. How do I have this formula pull positive an
negative numbers, but not #REF! (and possibly the #N/A! error too,
might have that one in some of the cells for one reason or another)

 

[Frank Kabel]

Hi
=SUMIF($J$24:$J$75,"<>0",$B$24:$B$75)

 

[Naoki]

Well then it appears I dug myself a little deeper. I thought I had this
formula working properly yesterday, at least to the point where it
wouldn't pull the #REF! error.

It turns out the above formula will pull a value with #REF!, so the
">0" criterial is useless.

So basically, all I'm asking is how to add up cells in a column, based
on another COLUMN, so long as each cell in the latter does not contain
an error. Any help would be appreciated!

 

[Frank Kabel]

Hi
best way would be to prevent these errors!. You may try the following
array formula (entered with cTRL+SHIFT+ENTER):
=SUM(IF(ISNUMBER(A1:A1000),A1:A1000))

 

[Naoki]

Thanks so much! Yes, I wish I could avoid the errors, but I'm trying t
automate a spreadsheet by use from someone with no knowledge of excel
to enter data in one place and have it work consistently for an entir
fiscal year.

It would seem to me then that the error messages are going to be
requirement, and as long as I can pull all the data AROUND an error
then I will be ok. So maybe there is one one particular thing abou
Excel I need to learn and I could figure out many of these problems o
my own.

With that said, I have another issue.

I have a column that looks similar to:

300... numbers continued prior to this one
200
450
N/A!
N/A!
N/A!.... continued

Again, the N/A is because of the automation, and they will be replace
on a by week basis.

What I want to do with this data is obtain an average of the LAST 1
WEEKS, obviously, not including the N/A! errors, so it would includ
"450, 200, 300..." and the 7 numbers prior to this. Any ideas? Usin
that ISERROR thing, I can now do a lot of different stuff with my data
but I'm not sure how I would possibly JUST get 10 values that ar
seemingly in the middle of my data, without losing the automation o
this document. Filtering is not an option

 

[Frank Kabel]

Hi
still you may post your formulas which result in these errors. there
are some ways to prevent these errors!. that said try the following
array formula for your avarage:
=AVERAGE(IF(ISNA(A1:A1000),,A1:A1000))

 

[Naoki]

Thanks again. Hmm... I can understand why avoiding the errors woul
make writing code a piece of cake (like I've been asking about), bu
I'm not sure how it would be accomplished.

For instance:

='X:\PRODUCE\[Pr0448.xls]A'!$G$41

produces a #REF! error, because G41 within the "produce" document
doesnt exist. As I've been saying, that cell is based on a week tha
hasn't been completed or reported yet.

So then, let's say there is a way to have this not display an error (i
that is what you are suggesting), what would then appear in that cell?
Would it be blank? It'd still have to have the underlying "formula" a
I showed above, to retain the automation of this document. My boss, wh
will be taking on this development task in the future, would love t
avoid these errors from the beginning, although with my curren
document, I'm in far enough that I'll keep working with what I got, an
luckily it's my last before the job gets handed off. I wish I coul
make it easier from the onset for her

 

[Frank Kabel]

Hi
try:
=IF(ISERROR('X:\PRODUCE\[Pr0448.xls]A'!$G$41),"",'X:\PRODUCE\[Pr0448.xl
s]A'!$G$41)