Hope somebody can help.
Imagine I have shares in six companies called 101,102,103,104,105 and 106. I want to look up the year-end share prices for the last five years and have a vlookup table in Excel.
Now companies 103, 104 and 105 were only floated two years ago, so are not present in the list of year-end prices for the first three years. So Excel returns '#N/A'. Under such circumstances I want it to return a zero.
What can I put in my 'if' statement. (In view of the number of data points in the data I am processing, manual entry of zeros is really not an option owing to the risk of error. I need an auditable spreadsheet.)
Many thanks.
|
i.e. if(company 103 is not listed in table,0,value)
|
Yahoo provide a very good financial service including share prices at a glance over different periods from 24 hrs to 5 yrs. SWMBO who does a little dabbling for money uses them. Saves all that exporting malarkey
|
Thanks, PU. I'm actually in the office trying to manipulate something completely different (Company share options - none of them mine) - the share prices was supposed to be an illustration.
My initial illustration was going to be horses...
Any excel wizzes out there?
|
|
|
Yahoo provide a very good financial service including share prices at a glance
maybe ok for a quick glance but their accuracy is not to be trusted at all.
i have pointed out errors to them many times but they do not ever bother to correct them, nor acknowledge the effort i have put in.
so now i don't bother telling them, and rely on other sources for accurate information.
|
|
|
|
|
Mapmaker,
I can get by in Excel but I'm no expert. Does this fit;
How to stop the #N/A! error when using VLOOKUP
One very common question asked by Excel users is "How can I stop VLOOKUP returning #N/A! when it cannot find my data?". There are a few ways this can be done, unfortunately the most popular way is also the least efficient. This is to use the ISNA function as shown below:
=IF(ISNA(VLOOKUP(368.59,$A$1:$C$1000,2,False)),"",VLOOKUP(368.59,$A$1:
$C$1000,2,False)
The use of the ISNA function like this will force Excel to perform the VLOOKUP twice if the value does exist, which is most often the case. This can become a problem by slowing down Excel's recalculation time. A slightly better options is:
=IF(COUNTIF($A$1:$A$10000,368.59),VLOOKUP(368.59,$A$1:$C$10000,2,FALSE
),"")
However, see Lookup Functions here for more & even better ways.
?
From www.ozgrid.com/Excel. I typed Excel vlookup into Google.
JH
|
|
HERO! Thanks. Didn't think of googling, silly me.
|
|
|
Just a thought,
Have you tried sumif instead sumif(range where value is, criteria ie value you are looking for,range where the data value is) - works very similar to vlookup, bu will sum any occurrences of a particular company.
E.g. you have a list of 10 companies in column a, and the value of the share price in column b. Sum if then will work as follows - looks for a value in column a, that matches your criteria, then gets the share price from column b) This way you don't get any n/a's, but if you have two occurrences of the same company, it will add up the sum of each.
It's a very useful formula and is fairly simple.
|
|
|
|
|
