(952) 890-7770
Zkarlo.com provides discount and used laptop parts...
Answer
what are the disadvantages?
Problem for Business and Productivity Software
Generic problem for all Business and Productivity Sof...
Formulae problem
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?
Comments:
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 helpsJan 07, 2008
- Is there any way i can send this file - I'm sure it will be easier that way?Jan 07, 2008
- Thanks guys I figured it out!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
The 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.
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.
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.
Comments:
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.
Yes
No
This Problem Also Relates To:
HP HP Integrated Lights Out (;iLO) Advanced Pack 1 server license Upgrade Version (452141-B21)
Sony Vegas Pro 8 for PC (SVDVD8006)
Sony Acid Pro 6 Professional Music Workstation Full Version for PC
Sony American Idol Extreme Music Creator Promo/Demo License for PC (2205808)
Apple AppleWorks 6.2.7 for Mac
Encore Mavis Beacon Teaches Typing 17 Deluxe Full Version for PC, Mac
Encore Family Tree Maker 2008 Deluxe for PC (705381148500)
Popular Solutions for Business and Productivity
Questions and Unsolved Problems for Business and Productivity
Answer
How can i switch off the four front lights of Alfatel H2 model chinese mobile phone
Answer
i have user in domain server2003,but the taskbar is classic and i want taskbar xp normal , can you...
(More)
Answer
automatic switch off mobile & it lost its phone data(my msg.)date and time ;my mobile...
(More)
Answer
get error codes -6000 and -77
Ask our Experts
Top Business and... Experts
Top Computer & Laptop Repair
