| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula showing zero's
I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
You can replace C19 with IF(C19="","",C19) and similary for C20.
Or you can use ISNA in the cells having VLOOKUP like this =IF(ISNA(Vlookup(...),"",Vlookup(...))) If you want to supress # then you can combine it with the IF formula with either C19 or C20.... "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
Sheeloo
Tried both of your solutions and am getting the same "0#0" as a result of my concatenate formula. If I could only get rid of the "0" that C20 is causing I would be able to live with it. My formula in C20 is now IF(ISNA(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLO OKUP(G18,MultiCustomer,4,FALSE)) Any other ideas? Mike Rogers "Sheeloo" wrote: You can replace C19 with IF(C19="","",C19) and similary for C20. Or you can use ISNA in the cells having VLOOKUP like this =IF(ISNA(Vlookup(...),"",Vlookup(...))) If you want to supress # then you can combine it with the IF formula with either C19 or C20.... "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
It would be interesting to understand the cause of this problem - so while
you are seeing 0#0 in your formula move the C19 and select the whole formula on the Formula Bar and press F9 - what do you see on the formula bar exactly? Oh, and another idea, what is the format of the concatenated cell? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mike Rogers" wrote: Sheeloo Tried both of your solutions and am getting the same "0#0" as a result of my concatenate formula. If I could only get rid of the "0" that C20 is causing I would be able to live with it. My formula in C20 is now IF(ISNA(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLO OKUP(G18,MultiCustomer,4,FALSE)) Any other ideas? Mike Rogers "Sheeloo" wrote: You can replace C19 with IF(C19="","",C19) and similary for C20. Or you can use ISNA in the cells having VLOOKUP like this =IF(ISNA(Vlookup(...),"",Vlookup(...))) If you want to supress # then you can combine it with the IF formula with either C19 or C20.... "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
Shane
With every ones help I figured out the problem. Thanks for the mental push... Mike Rogers "Shane Devenshire" wrote: It would be interesting to understand the cause of this problem - so while you are seeing 0#0 in your formula move the C19 and select the whole formula on the Formula Bar and press F9 - what do you see on the formula bar exactly? Oh, and another idea, what is the format of the concatenated cell? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mike Rogers" wrote: Sheeloo Tried both of your solutions and am getting the same "0#0" as a result of my concatenate formula. If I could only get rid of the "0" that C20 is causing I would be able to live with it. My formula in C20 is now IF(ISNA(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLO OKUP(G18,MultiCustomer,4,FALSE)) Any other ideas? Mike Rogers "Sheeloo" wrote: You can replace C19 with IF(C19="","",C19) and similary for C20. Or you can use ISNA in the cells having VLOOKUP like this =IF(ISNA(Vlookup(...),"",Vlookup(...))) If you want to supress # then you can combine it with the IF formula with either C19 or C20.... "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
On Feb 15, 11:58*am, Mike Rogers <Mike060349@NoxSpamxAOLDOTcom wrote:
Sheeloo Tried both of your solutions and am getting the same "0#0" as a result of my concatenate formula. *If I could only get rid of the "0" that C20 is causing I would be able to live with it. *My formula in C20 is now IF(ISNA(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLO OKUP(G18,MultiCustomer,4,FALSE)) *Any other ideas? Mike Rogers "Sheeloo" wrote: You can replace C19 with IF(C19="","",C19) and similary for C20. Or you can use ISNA in the cells having VLOOKUP like this =IF(ISNA(Vlookup(...),"",Vlookup(...))) If you want to supress # then you can combine it with the IF formula with either C19 or C20.... "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). *This formula returns 0#0. *K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula * =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula * =VLOOKUP(G18,MultiCustomer,4,FALSE). *How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zero’s. *Any ideas??? Mike Rogers Sounds like the VLOOKUP is returning 0 because the table has a blank in the cell whose value is being returned. If this is the case then maybe... =IF(ISBLANK(VLOOKUP(G18,MultiCustomer,3,FALSE)),"" ,VLOOKUP (G18,MultiCustomer,3,FALSE)) in C19 and... =IF(ISBLANK(VLOOKUP(G18,MultiCustomer,4,FALSE)),"" ,VLOOKUP (G18,MultiCustomer,4,FALSE)) in C20. Ken Johnson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
Ken
Got it figured out that for the help!!! "Ken Johnson" wrote: On Feb 15, 11:58 am, Mike Rogers <Mike060349@NoxSpamxAOLDOTcom wrote: Sheeloo Tried both of your solutions and am getting the same "0#0" as a result of my concatenate formula. If I could only get rid of the "0" that C20 is causing I would be able to live with it. My formula in C20 is now IF(ISNA(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLO OKUP(G18,MultiCustomer,4,FALSE)) Any other ideas? Mike Rogers "Sheeloo" wrote: You can replace C19 with IF(C19="","",C19) and similary for C20. Or you can use ISNA in the cells having VLOOKUP like this =IF(ISNA(Vlookup(...),"",Vlookup(...))) If you want to supress # then you can combine it with the IF formula with either C19 or C20.... "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers Sounds like the VLOOKUP is returning 0 because the table has a blank in the cell whose value is being returned. If this is the case then maybe... =IF(ISBLANK(VLOOKUP(G18,MultiCustomer,3,FALSE)),"" ,VLOOKUP (G18,MultiCustomer,3,FALSE)) in C19 and... =IF(ISBLANK(VLOOKUP(G18,MultiCustomer,4,FALSE)),"" ,VLOOKUP (G18,MultiCustomer,4,FALSE)) in C20. Ken Johnson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
Sheeloo
After reading what everyone else suggested I found the problem. The cell had a "0" in it ans was supressed by toolsoptionsviewunchecked "Zero Values". So I changed your first suggestion to 0 instead of "" and it works. Cudos to all..Thanks Mike Rogers "Mike Rogers" wrote: Sheeloo Tried both of your solutions and am getting the same "0#0" as a result of my concatenate formula. If I could only get rid of the "0" that C20 is causing I would be able to live with it. My formula in C20 is now IF(ISNA(VLOOKUP(G18,MultiCustomer,4,FALSE)),"",VLO OKUP(G18,MultiCustomer,4,FALSE)) Any other ideas? Mike Rogers "Sheeloo" wrote: You can replace C19 with IF(C19="","",C19) and similary for C20. Or you can use ISNA in the cells having VLOOKUP like this =IF(ISNA(Vlookup(...),"",Vlookup(...))) If you want to supress # then you can combine it with the IF formula with either C19 or C20.... "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
If your two VLOOKUP functions haven't a value to look up then they would
return #N/A errors, so it suggests that they are returning somethng and your concatenated formula suggest that something is 0 even if the VLOOKUP doesn't display it, possibly because those cells are formatted to not display 0. You need to determine what value the VLOOKUP function are returning and then you need to have the CONCATENATE function test for those. And I think I would use & instead of concatenate. =CONCATENATE(K4,P10,C19,"#",C20) would become something like =K4&P10&IF(C19=0,"",C19)&"#"&IF(C20=0,"",C20) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
Shane
After reading what everyone else suggested I found the problem. The cell had a "0" in it and was suppressed by toolsoptionsviewunchecked "Zero Values". So I changed Sheloo's first suggestion to 0 instead of "" and it works. Cudos to all..Thanks Mike Rogers "Shane Devenshire" wrote: If your two VLOOKUP functions haven't a value to look up then they would return #N/A errors, so it suggests that they are returning somethng and your concatenated formula suggest that something is 0 even if the VLOOKUP doesn't display it, possibly because those cells are formatted to not display 0. You need to determine what value the VLOOKUP function are returning and then you need to have the CONCATENATE function test for those. And I think I would use & instead of concatenate. =CONCATENATE(K4,P10,C19,"#",C20) would become something like =K4&P10&IF(C19=0,"",C19)&"#"&IF(C20=0,"",C20) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
Glad we all could help and thanks for the feedback.
Cheers, Shane Devenshire "Mike Rogers" wrote: Shane After reading what everyone else suggested I found the problem. The cell had a "0" in it and was suppressed by toolsoptionsviewunchecked "Zero Values". So I changed Sheloo's first suggestion to 0 instead of "" and it works. Cudos to all..Thanks Mike Rogers "Shane Devenshire" wrote: If your two VLOOKUP functions haven't a value to look up then they would return #N/A errors, so it suggests that they are returning somethng and your concatenated formula suggest that something is 0 even if the VLOOKUP doesn't display it, possibly because those cells are formatted to not display 0. You need to determine what value the VLOOKUP function are returning and then you need to have the CONCATENATE function test for those. And I think I would use & instead of concatenate. =CONCATENATE(K4,P10,C19,"#",C20) would become something like =K4&P10&IF(C19=0,"",C19)&"#"&IF(C20=0,"",C20) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
Go the simple route:
=IF(CONCATENATE(K4,P10,C19,"#",C20)="0#0","",CONCA TENATE(K4,P10,C19,"#",C20)) -- Gary''s Student - gsnu200833 "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula showing zero's
Gary''s Student
Thanks for the help, With every one's comment of help it all came together and of course it was a big "DUH" and all is working fine. Mike Rogers "Gary''s Student" wrote: Go the simple route: =IF(CONCATENATE(K4,P10,C19,"#",C20)="0#0","",CONCA TENATE(K4,P10,C19,"#",C20)) -- Gary''s Student - gsnu200833 "Mike Rogers" wrote: I have a formula in O5 ,=CONCATENATE(K4,P10,C19,"#",C20). This formula returns 0#0. K4 is empty until a macro inputs a number, P10 is empty until the user inputs data, C19 has the formula =VLOOKUP(G18,MultiCustomer,3,FALSE), and C20 has the formula =VLOOKUP(G18,MultiCustomer,4,FALSE). How do I change the formula in O5 to return nothing if the two lookup formulas (C19 & C20) have not looked up the data it is designed to. If these to cells are not showing anything they are not still not empty because they have a formula, thus causing the concatenate formula to return the zeros. Any ideas??? Mike Rogers |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to fix my spreadsheet from showing formula to showing answer | Excel Discussion (Misc queries) | |||
| How to fix my spreadsheet from showing formula to showing answer | Excel Discussion (Misc queries) | |||
| Zero's are showing up as negative numbers | Excel Worksheet Functions | |||
| Average formula Counting zero's How do I get just the numers counted. | Excel Discussion (Misc queries) | |||
| =SUMPRODUCT formula is counting the blank cells as well as zero's | Excel Worksheet Functions | |||
![Mike Rogers[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
