N/A Error

[Simmo9]

I am using the following vlookup to return a value for some run rates of production lines. however, when i update my master data' and a product line de-lists, i am left with an N/A error which corrupts further data. Can anybody help with a simple update that would return the value 0?

=IF(SUMIF(Uniq,$A68&AU$2,'Forecasted Requirement'!$G:$G)/$E68>0,SUMIF(Uniq,$A68&AU$2,'Forecasted Requirement'!$G:$G)/$E68+($P68/60),SUMIF(Uniq,$A68&AU$2,'Forecasted Requirement'!$G:$G)/$E68)

Many Thanks
Simmo9

 

[Yard]

erm, that's not a VLOOKUP function?

Also, is your Uniq named range the same size as a complete column? The 1st and 3rd arguments of SUMIF must be the same dimension otherwise you'll get unexpected results.

 

[Simmo9]

Sorry - i was in the middle of writing a vlookup function!! Unable to multi task!

Thanks for the swift reply, yes, the uniq reference is the full size of a column.

forgive my ignorance, i have inherited some spreadsheets, and i'm still getting to grips with all the formulas - clearly the last guy here was more advanced than myself!

 

[Yard]

It won't be very pretty to de-error that formula unless you are using Excel 2007.

Is the #N/A coming from the SUMIF or from one of $A68, AU$2, $E68 or $P68?

 

[Simmo9]

No such look, using excel '03!

It is a response to the sumif as the other arguments are fine.

 

[Yard]

Then

=IF(ISNA(SUMIF(Uniq,$A68&AU$2,'Forecasted Requirement'!$G:$G)),0,IF(SUMIF(Uniq,$A68&AU$2,'Forecasted Requirement'!$G:$G)/$E68>0,SUMIF(Uniq,$A68&AU$2,'Forecasted Requirement'!$G:$G)/$E68+($P68/60),SUMIF(Uniq,$A68&AU$2,'Forecasted Requirement'!$G:$G)/$E68))

 

[Simmo9]

Thats great, thanks.