Generic problem for all Business and Productivity Software
Formulae problem
posted by gavster70 on Jan 05, 2008
I am busy creating a sheet which I use to import figures, sort them and then transfer them to a single column which I copy and paste to another file. My problem arises due to the fact that some positions are blank and when the info is tranfered the correcsponding amount for those positions don't come up as "0"s. This is the formulae that I am using to transfer 2 columns of info across to another sheet which should fill in the missing numbers but doesn't show them as "0" =LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B) Can you help me?
Comment posted on Jan 06, 2008
27003
140
27001
#N/A
27004
350
27002
#N/A
27005
65.65
27003
140
27010
623.75
27008
113.5
27013
123
27009
201
27014
495.33
27010
623.75
27011
623.75
27012
623.75As you can left side has missing numbers which transfer across and fill in the blanks (yellow). I need "0"'s but get #N/A and repetition. Hope this helps
Comment posted on Jan 07, 2008
Is there any way i can send this file - I'm sure it will be easier that way?
Comment posted on Jan 07, 2008
Thanks guys I figured it out!
Comment posted on Jan 07, 2008
Sorry for messing around but I am new to all of this so I will start again: I have 2 worksheets named DCT INFO and DCT. In DCT INFO I have 2 columns A has positions (some are missing so it's not consecutive) B has a corresponding dollar amount. I need to copy these 2 columns to worksheet DCT together with all the missing positions, and their corresponding values. The missing positions which are filled in on column A must show a "0" in column B in DCT.
Best Solution
posted on Jan 06, 2008
MasterThe problem doesn't say this explicitly, but it looks like you're using Excel. This answer depends on that assumption being correct.
Are you saying that some of your inputs are blank? And LOOKUP fails to map them to 0? Or are you saying that some of your values come back from LOOKUP as blanks? (They shouldn't; lookup should return either a value from the lookup table or an error.) Or is it the case that some input values get mapped to blank by your LOOKUP function?
Assuming that you're getting blanks back from LOOKUP where you wanted zeros -- for whatever reason -- here is something you can try.
It looks like the value you're looking up is in cell A3. Let's say your formula (the result of the LOOKUP) is in B3. In C3, you can put the formula =IF(ISBLANK(A3), 0, A3). This function tests if A3 is blank. If it is, it returns a 0. If it's not, you get A3 again. (But A3 has to really be blank -- spaces will count as non-blank.)
Then, you can copy data out of column C instead of column B, and it should have zeros in place of blanks.
Are you saying that some of your inputs are blank? And LOOKUP fails to map them to 0? Or are you saying that some of your values come back from LOOKUP as blanks? (They shouldn't; lookup should return either a value from the lookup table or an error.) Or is it the case that some input values get mapped to blank by your LOOKUP function?
Assuming that you're getting blanks back from LOOKUP where you wanted zeros -- for whatever reason -- here is something you can try.
It looks like the value you're looking up is in cell A3. Let's say your formula (the result of the LOOKUP) is in B3. In C3, you can put the formula =IF(ISBLANK(A3), 0, A3). This function tests if A3 is blank. If it is, it returns a 0. If it's not, you get A3 again. (But A3 has to really be blank -- spaces will count as non-blank.)
Then, you can copy data out of column C instead of column B, and it should have zeros in place of blanks.
Comment posted on Jan 06, 2008
LOOKUP returns #N/A when the value you're looking up is less than the smallest number in the lookup table. You can detect this after the fact using the ISERROR function.
Thus, you can follow the advice in the solution above, except use the formula =IF(ISERROR(A3), 0, A3). This will have your original results in column A and your new results in a new column. I like doing it this way, because it's easy to test and make sure everything's working right.
Once you're satisfied that the formulas are working properly, you can combine both formulas into one. Change your formula from this:
=LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B)
to this:
=ISERROR(LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B),0,LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B))
This formula is saying, do the lookup. If it is an error, put 0 in the cell. Otherwise, put the lookup value in the cell.
Thus, you can follow the advice in the solution above, except use the formula =IF(ISERROR(A3), 0, A3). This will have your original results in column A and your new results in a new column. I like doing it this way, because it's easy to test and make sure everything's working right.
Once you're satisfied that the formulas are working properly, you can combine both formulas into one. Change your formula from this:
=LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B)
to this:
=ISERROR(LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B),0,LOOKUP(A3,'DCT INFO'!A:A,'DCT INFO'!B:B))
This formula is saying, do the lookup. If it is an error, put 0 in the cell. Otherwise, put the lookup value in the cell.
This Problem Also Relates To:
Sony ACID Music Studio 6.0 (ASAC6000)
Apple AppleWorks 6.2.4 (M8761Z/A) for Mac
Cisco 3845 Series IOS Full Version for PC (cd384aesk9)
Encore 3D Home Architect® Design Suite (00705381106340)
Global Marketing Partners Smart Restart By Centurion Technologies Inc Full Version for PC (187722000027)
Global Marketing Partners GLOBAL MARKETING PARTNERS RVB10 RAPID VIDEO BLOGGER - HONEST TECHNOLOGY WIN XP,VISTA for PC (882960000234)
IBM Lotus Notes Messaging for Mac, PC
Can you Help with these Business and Productivity Software problems?
Business and Productivity Software
Subject 1 Subject 2 Subject 3 Total Percentage Student 1...
Microsoft Office Professional 2007 Full...
i want the excel compound intrest formula regarding this...
Microsoft Office 2003 Basic Edition English...
My outlook 2007 instant search is not working properly,...
Microsoft Office 2003 Basic Edition English...
I need a formula for an interest rate worksheet. Cell A1 =...
Pinnacle Studio Plus 10 Full Version for PC
I have successfully used Pinnacle Studio 10 Plus with my...
Wiz
Repair Service
Find Business and Pro. Repair Service Provider Near You:
About | News
| Contact
| Find Products
| Find Cars
| Terms
| Privacy Policy
| FAQ
Advertisers | Tags | Blog | Repair Service Directory | Partners | Careers |
Advertisers | Tags | Blog | Repair Service Directory | Partners | Careers |
