|
#N/A Conversion
Hi - how do I go about converting a value that shows #N/A to a number like 0?
I want to use the value to calculate a sum of a column but the #N/As in the column prevent me from doing so. This is what the spreadsheet looks like: A B 1 2 Date Count 3 11/1/2009 0 4 11/2/2009 #N/A 5 11/3/2009 0 : : : : : : Column A1 is the first day of the month. The formula for Column A2 is A1+1. The formula for Column A3 is A2+1 and it keeps going until the end of the month. Column B1 is a formula that refers to another sheet and uses hlookup. =IF(ERROR.TYPE(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE ))=ERROR.TYPE(#N/A),0,1) On Sheet1 not all of the dates are there - the weekends are excluded from the sheet. On Sheet2 I have all of the dates there. I'm trying to automate Sheet2 so that if I enter the first day of the month in A3 that it'll pull the info from Sheet1 into Sheet2 where I can calculate the sum of the numbers. Sheet1 is more of an input area with lots of info whereas Sheet2 is for presentation of limited information. So basically the IF statement above is looking for a match on the date, if it can't find the date, it should be a zero otherwise a 1 should be there. It works if it can't find a date but if there is a date it shows an #N/A now. I can't sum the column up if there is an #N/A. I thought that the IF statement should work whether true or false. Can someone assist in pointing to the right direction in fixing the statement? I'm almost at wits end with this. Thank you! Aaron |
#N/A Conversion
For doing similar things, I use the ISNA function so:
=IF(ISNA(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE)),0,1 ) -- LMH "Aaron" wrote: Hi - how do I go about converting a value that shows #N/A to a number like 0? I want to use the value to calculate a sum of a column but the #N/As in the column prevent me from doing so. This is what the spreadsheet looks like: A B 1 2 Date Count 3 11/1/2009 0 4 11/2/2009 #N/A 5 11/3/2009 0 : : : : : : Column A1 is the first day of the month. The formula for Column A2 is A1+1. The formula for Column A3 is A2+1 and it keeps going until the end of the month. Column B1 is a formula that refers to another sheet and uses hlookup. =IF(ERROR.TYPE(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE ))=ERROR.TYPE(#N/A),0,1) On Sheet1 not all of the dates are there - the weekends are excluded from the sheet. On Sheet2 I have all of the dates there. I'm trying to automate Sheet2 so that if I enter the first day of the month in A3 that it'll pull the info from Sheet1 into Sheet2 where I can calculate the sum of the numbers. Sheet1 is more of an input area with lots of info whereas Sheet2 is for presentation of limited information. So basically the IF statement above is looking for a match on the date, if it can't find the date, it should be a zero otherwise a 1 should be there. It works if it can't find a date but if there is a date it shows an #N/A now. I can't sum the column up if there is an #N/A. I thought that the IF statement should work whether true or false. Can someone assist in pointing to the right direction in fixing the statement? I'm almost at wits end with this. Thank you! Aaron |
#N/A Conversion
Try this:
=IF(ISERROR(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE)), 0,1) Aaron wrote: #N/A Conversion 13-Nov-09 Hi - how do I go about converting a value that shows #N/A to a number like 0? I want to use the value to calculate a sum of a column but the #N/As in the column prevent me from doing so. This is what the spreadsheet looks like: A B 1 2 Date Count 3 11/1/2009 0 4 11/2/2009 #N/A 5 11/3/2009 0 Column A1 is the first day of the month. The formula for Column A2 is A1+1. The formula for Column A3 is A2+1 and it keeps going until the end of the month. Column B1 is a formula that refers to another sheet and uses hlookup. =IF(ERROR.TYPE(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE ))=ERROR.TYPE(#N/A),0,1) On Sheet1 not all of the dates are there - the weekends are excluded from the sheet. On Sheet2 I have all of the dates there. I am trying to automate Sheet2 so that if I enter the first day of the month in A3 that it will pull the info from Sheet1 into Sheet2 where I can calculate the sum of the numbers. Sheet1 is more of an input area with lots of info whereas Sheet2 is for presentation of limited information. So basically the IF statement above is looking for a match on the date, if it cannot find the date, it should be a zero otherwise a 1 should be there. It works if it cannot find a date but if there is a date it shows an #N/A now. I cannot sum the column up if there is an #N/A. I thought that the IF statement should work whether true or false. Can someone assist in pointing to the right direction in fixing the statement? I am almost at wits end with this. Thank you! Aaron Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Book Review: C# 3.0 Cookbook [O'Reilly] http://www.eggheadcafe.com/tutorials...-30-cookb.aspx |
Another Formula
You could also use:
=SUMPRODUCT(--(ISNUMBER(SHEET1!$1:$138)) Tom Lafferty wrote: #N/A Conversion 13-Nov-09 Try this: =IF(ISERROR(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE)), 0,1) Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Windows Forms .NET DataGrid ComboBox Columns And Custom Row Coloring http://www.eggheadcafe.com/tutorials...et-datagr.aspx |
#N/A Conversion
Hi LMH - Wow! Perfect! The statement works really well. Thank you so much
for your help!!! Aaron "LMH" wrote: For doing similar things, I use the ISNA function so: =IF(ISNA(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE)),0,1 ) -- LMH "Aaron" wrote: Hi - how do I go about converting a value that shows #N/A to a number like 0? I want to use the value to calculate a sum of a column but the #N/As in the column prevent me from doing so. This is what the spreadsheet looks like: A B 1 2 Date Count 3 11/1/2009 0 4 11/2/2009 #N/A 5 11/3/2009 0 : : : : : : Column A1 is the first day of the month. The formula for Column A2 is A1+1. The formula for Column A3 is A2+1 and it keeps going until the end of the month. Column B1 is a formula that refers to another sheet and uses hlookup. =IF(ERROR.TYPE(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE ))=ERROR.TYPE(#N/A),0,1) On Sheet1 not all of the dates are there - the weekends are excluded from the sheet. On Sheet2 I have all of the dates there. I'm trying to automate Sheet2 so that if I enter the first day of the month in A3 that it'll pull the info from Sheet1 into Sheet2 where I can calculate the sum of the numbers. Sheet1 is more of an input area with lots of info whereas Sheet2 is for presentation of limited information. So basically the IF statement above is looking for a match on the date, if it can't find the date, it should be a zero otherwise a 1 should be there. It works if it can't find a date but if there is a date it shows an #N/A now. I can't sum the column up if there is an #N/A. I thought that the IF statement should work whether true or false. Can someone assist in pointing to the right direction in fixing the statement? I'm almost at wits end with this. Thank you! Aaron |
#N/A Conversion
Hi Tom - Thank you very much for the reply. I tried this function out and it
works similarly to what LMH replied earlier with the ISNA function. They both work out the same. I will test further with the both of these functions. I really appreciate your help! Thank you again! Aaron "Tom Lafferty" wrote: Try this: =IF(ISERROR(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE)), 0,1) Aaron wrote: #N/A Conversion 13-Nov-09 Hi - how do I go about converting a value that shows #N/A to a number like 0? I want to use the value to calculate a sum of a column but the #N/As in the column prevent me from doing so. This is what the spreadsheet looks like: A B 1 2 Date Count 3 11/1/2009 0 4 11/2/2009 #N/A 5 11/3/2009 0 Column A1 is the first day of the month. The formula for Column A2 is A1+1. The formula for Column A3 is A2+1 and it keeps going until the end of the month. Column B1 is a formula that refers to another sheet and uses hlookup. =IF(ERROR.TYPE(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE ))=ERROR.TYPE(#N/A),0,1) On Sheet1 not all of the dates are there - the weekends are excluded from the sheet. On Sheet2 I have all of the dates there. I am trying to automate Sheet2 so that if I enter the first day of the month in A3 that it will pull the info from Sheet1 into Sheet2 where I can calculate the sum of the numbers. Sheet1 is more of an input area with lots of info whereas Sheet2 is for presentation of limited information. So basically the IF statement above is looking for a match on the date, if it cannot find the date, it should be a zero otherwise a 1 should be there. It works if it cannot find a date but if there is a date it shows an #N/A now. I cannot sum the column up if there is an #N/A. I thought that the IF statement should work whether true or false. Can someone assist in pointing to the right direction in fixing the statement? I am almost at wits end with this. Thank you! Aaron Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Book Review: C# 3.0 Cookbook [O'Reilly] http://www.eggheadcafe.com/tutorials...-30-cookb.aspx . |
#N/A Conversion
Just be aware that ISERROR masks all errors, not just #N/A
Gord Dibben MS Excel MVP On Fri, 13 Nov 2009 16:28:09 -0800, Aaron wrote: Hi Tom - Thank you very much for the reply. I tried this function out and it works similarly to what LMH replied earlier with the ISNA function. They both work out the same. I will test further with the both of these functions. I really appreciate your help! Thank you again! Aaron "Tom Lafferty" wrote: Try this: =IF(ISERROR(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE)), 0,1) Aaron wrote: #N/A Conversion 13-Nov-09 Hi - how do I go about converting a value that shows #N/A to a number like 0? I want to use the value to calculate a sum of a column but the #N/As in the column prevent me from doing so. This is what the spreadsheet looks like: A B 1 2 Date Count 3 11/1/2009 0 4 11/2/2009 #N/A 5 11/3/2009 0 Column A1 is the first day of the month. The formula for Column A2 is A1+1. The formula for Column A3 is A2+1 and it keeps going until the end of the month. Column B1 is a formula that refers to another sheet and uses hlookup. =IF(ERROR.TYPE(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE ))=ERROR.TYPE(#N/A),0,1) On Sheet1 not all of the dates are there - the weekends are excluded from the sheet. On Sheet2 I have all of the dates there. I am trying to automate Sheet2 so that if I enter the first day of the month in A3 that it will pull the info from Sheet1 into Sheet2 where I can calculate the sum of the numbers. Sheet1 is more of an input area with lots of info whereas Sheet2 is for presentation of limited information. So basically the IF statement above is looking for a match on the date, if it cannot find the date, it should be a zero otherwise a 1 should be there. It works if it cannot find a date but if there is a date it shows an #N/A now. I cannot sum the column up if there is an #N/A. I thought that the IF statement should work whether true or false. Can someone assist in pointing to the right direction in fixing the statement? I am almost at wits end with this. Thank you! Aaron Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Book Review: C# 3.0 Cookbook [O'Reilly] http://www.eggheadcafe.com/tutorials...-30-cookb.aspx . |
#N/A Conversion
Hi Gord - thank you for pointing that out. I didn't realize that. I will
check my calcs again and see how this function interacts with the other errors. Thanks! Aaron "Gord Dibben" wrote: Just be aware that ISERROR masks all errors, not just #N/A Gord Dibben MS Excel MVP On Fri, 13 Nov 2009 16:28:09 -0800, Aaron wrote: Hi Tom - Thank you very much for the reply. I tried this function out and it works similarly to what LMH replied earlier with the ISNA function. They both work out the same. I will test further with the both of these functions. I really appreciate your help! Thank you again! Aaron "Tom Lafferty" wrote: Try this: =IF(ISERROR(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE)), 0,1) Aaron wrote: #N/A Conversion 13-Nov-09 Hi - how do I go about converting a value that shows #N/A to a number like 0? I want to use the value to calculate a sum of a column but the #N/As in the column prevent me from doing so. This is what the spreadsheet looks like: A B 1 2 Date Count 3 11/1/2009 0 4 11/2/2009 #N/A 5 11/3/2009 0 Column A1 is the first day of the month. The formula for Column A2 is A1+1. The formula for Column A3 is A2+1 and it keeps going until the end of the month. Column B1 is a formula that refers to another sheet and uses hlookup. =IF(ERROR.TYPE(HLOOKUP(A3,Sheet1!$1:$138,127,FALSE ))=ERROR.TYPE(#N/A),0,1) On Sheet1 not all of the dates are there - the weekends are excluded from the sheet. On Sheet2 I have all of the dates there. I am trying to automate Sheet2 so that if I enter the first day of the month in A3 that it will pull the info from Sheet1 into Sheet2 where I can calculate the sum of the numbers. Sheet1 is more of an input area with lots of info whereas Sheet2 is for presentation of limited information. So basically the IF statement above is looking for a match on the date, if it cannot find the date, it should be a zero otherwise a 1 should be there. It works if it cannot find a date but if there is a date it shows an #N/A now. I cannot sum the column up if there is an #N/A. I thought that the IF statement should work whether true or false. Can someone assist in pointing to the right direction in fixing the statement? I am almost at wits end with this. Thank you! Aaron Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Book Review: C# 3.0 Cookbook [O'Reilly] http://www.eggheadcafe.com/tutorials...-30-cookb.aspx . . |
#N/A Conversion
|
| All times are GMT +1. The time now is 02:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com