Prev: Calculating Elapsed Time Over a Set Period
Next: S- Curve Calculation and chart
From: David on 27 Dec 2009 08:07
I have used the following formula to replace N/A with 0, where there is no value to return.

=IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0 ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE))

However, the formula does not register and the above formula just shows as text in the respective cell.

I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still have the same problem.

Any ideas as to why this is happening, is much appreciated.

Best regards,

David


Submitted via EggHeadCafe - Software Developer Portal of Choice
The Software Project Loan Shark!
http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx
From: Mike H on 27 Dec 2009 08:52
Hi,

Small syntax error, try the formula below. A couple of points though:-

While you can use full columns, unless it's necessary I wouldn't because it
can get a bit slow.

Your using column A - E in the lookup array and returning Column B why not
use just 2 columns

=IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List
'!A:E,2,FALSE)),0,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE))

Mike

"David Scarfe" wrote:

> I have used the following formula to replace N/A with 0, where there is no value to return.
>
> =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0 ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE))
>
> However, the formula does not register and the above formula just shows as text in the respective cell.
>
> I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still have the same problem.
>
> Any ideas as to why this is happening, is much appreciated.
>
> Best regards,
>
> David
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> The Software Project Loan Shark!
> http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx
> .
>
From: Mike H on 27 Dec 2009 08:56
Hi,

On reflection, I prefer this

=IF(COUNTIF('KOTC_Customer Price List
'!A:A,D18)=0,0,VLOOKUP(D18,'KOTC_Customer Price List '!A:B,2,FALSE))

Mike

"Mike H" wrote:

> Hi,
>
> Small syntax error, try the formula below. A couple of points though:-
>
> While you can use full columns, unless it's necessary I wouldn't because it
> can get a bit slow.
>
> Your using column A - E in the lookup array and returning Column B why not
> use just 2 columns
>
> =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List
> '!A:E,2,FALSE)),0,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE))
>
> Mike
>
> "David Scarfe" wrote:
>
> > I have used the following formula to replace N/A with 0, where there is no value to return.
> >
> > =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0 ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE))
> >
> > However, the formula does not register and the above formula just shows as text in the respective cell.
> >
> > I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still have the same problem.
> >
> > Any ideas as to why this is happening, is much appreciated.
> >
> > Best regards,
> >
> > David
> >
> >
> > Submitted via EggHeadCafe - Software Developer Portal of Choice
> > The Software Project Loan Shark!
> > http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx
> > .
> >
From: Ms-Exl-Learner on 27 Dec 2009 08:59
Your Formula is missing the closing parenthesis of ISNA Function.

=IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List
'!A:E,2,FALSE)),0,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"David Scarfe" wrote:

> I have used the following formula to replace N/A with 0, where there is no value to return.
>
> =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0 ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE))
>
> However, the formula does not register and the above formula just shows as text in the respective cell.
>
> I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still have the same problem.
>
> Any ideas as to why this is happening, is much appreciated.
>
> Best regards,
>
> David
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> The Software Project Loan Shark!
> http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx
> .
>
From: Fred Smith on 27 Dec 2009 10:07
If the "formula just shows as text in the respective cell", then you have
the cell formatted as Text. To fix this:
-- Format the cell as General
-- Re-enter the formula

Regards,
Fred

"David Scarfe" wrote in message
news:200912278710david.scarfe(a)alfalaval.com...
>I have used the following formula to replace N/A with 0, where there is no
>value to return.
>
> =IF(ISNA(VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE), 0
> ,VLOOKUP(D18,'KOTC_Customer Price List '!A:E,2,FALSE))
>
> However, the formula does not register and the above formula just shows as
> text in the respective cell.
>
> I have even tried the IF ISERROR formula in Excel 2003 & 2007 and still
> have the same problem.
>
> Any ideas as to why this is happening, is much appreciated.
>
> Best regards,
>
> David
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> The Software Project Loan Shark!
> http://www.eggheadcafe.com/tutorials/aspnet/86bc31c4-0983-432a-b68d-3d73e47db1d5/the-software-project-loan.aspx

 |  Next  |  Last
Pages: 1 2
Prev: Calculating Elapsed Time Over a Set Period
Next: S- Curve Calculation and chart