Placement of ISNA in long formula

[Crispy85]

Hi guys,

I am familiar with the ISNA formula, but i do sometimes hesitate where i'd put it.
Now i have a rather big formula where it should be placed.

Maybe you can help me, i've added an example of the sheet.

Here a brief description.

A weekly report i make, includes about 40000 lines, each with it's own "Asset", these "Assets" have values.
Whenever a goods received has taken place, an asset will be booked.
For a quick weekly summary i want to see in a pivot table how much is been booked in comparison of a week earlier.

But, i only want to include in the table if an asset is increased by value, not if it is reduced. That part i found out.
But, also new assets are created weekly, and i can't find a comparison ofcourse. So, an #N/A pops up, and my pivot table doesn't sum anymore.

Instead of the #N/A, i would like the value of it.

The formula i use now is:

Please Login or Register  to view this content.

Please enlighten me!

[Andrew-R]

The ISNA would have to apply to the first instance of VLOOKUP (since the call of the second instance is conditional on the value of the first).

However, I'm not clear what you want the formula to do is the VLOOKUP does return ISNA. It's likely that an IFERROR will work better for you.

[Crispy85]

Hi Andrew,

Thanks for your answer.
When an error occurs, it should mean that that asset didn't exist a week ago, yet it has been booked within the last week.
So it should simply give me the value instead of an error.

[Andrew-R]

OK - assuming that everybody using this sheet is going to have Excel 2007 or later then I'd use IFERROR instead of ISNA.

Try this formula in cell C2, copied down:

=IFERROR(IF(B2=0,"",IF(VLOOKUP(A2,'List wk 29'!$A:$B,2,0)<'List wk 30'!B2,'List wk 30'!B2-VLOOKUP('List wk 30'!A2,'List wk 29'!$A:$B,2,0),"")),B2)

[Crispy85]

Many thanks for your help Andrew!