Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#n/a drving me mad, need to force ignore or remove them - Help!
Hi,
Please help, I have tried hard to rectify this using some different formula etc but to no avail. I have a spreadsheet which logs visits to customers so that we can get a % of visits which become orders, however we need this by Team/Individual/Product etc, so gets more intense. I have broken it down as far as possible to collate the data. I am using formula: =IF((AND(F4="Education",H40)),H4,0) to get the value of orders/team. i.e. if F4 is the Education Team and a figure is in order column over zero then put the figure (as this is an order). The Team column (i.e. Education) comes from a VLOOKUP table, so therefore they select staff initials (from drop down list) and it automatically looks up the team name. This works great until the blank lines, no initials selected, results in no team name (#n/a) and #N/A's in my result - This wouldn't matter only I cannot add up a column with them appearing, result in AutoSum is (yes you guessed it!) #N/A!! Am I missing something glaringly obvious? Please help, I'm so close to finishing this one! I have tried =if(ISNA..... but cannot seem to get it to work,. not sure if I am using this in the correct place. -- Ang. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#n/a drving me mad, need to force ignore or remove them - Help!
Your vlookup, I am assuming, is causing the error. You could try one of two
ways. =IF(ISNA(VLOOKUP(...,...,...,...)),0,VLOOKUP(...,. ..,...,...)) or, if your initials field is blank (say your initial field is D4) Then your VLOOKUP could be: =IF(D4="","",VLOOKUP(...,...,...,...)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Ang" wrote: Hi, Please help, I have tried hard to rectify this using some different formula etc but to no avail. I have a spreadsheet which logs visits to customers so that we can get a % of visits which become orders, however we need this by Team/Individual/Product etc, so gets more intense. I have broken it down as far as possible to collate the data. I am using formula: =IF((AND(F4="Education",H40)),H4,0) to get the value of orders/team. i.e. if F4 is the Education Team and a figure is in order column over zero then put the figure (as this is an order). The Team column (i.e. Education) comes from a VLOOKUP table, so therefore they select staff initials (from drop down list) and it automatically looks up the team name. This works great until the blank lines, no initials selected, results in no team name (#n/a) and #N/A's in my result - This wouldn't matter only I cannot add up a column with them appearing, result in AutoSum is (yes you guessed it!) #N/A!! Am I missing something glaringly obvious? Please help, I'm so close to finishing this one! I have tried =if(ISNA..... but cannot seem to get it to work,. not sure if I am using this in the correct place. -- Ang. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#n/a drving me mad, need to force ignore or remove them - Help!
=IF(ISNA(YourEntireFormula),"",YourEntireFormula)
or =IF(ISNA(F4),"",YourFormula) -- Kind regards, Niek Otten Microsoft MVP - Excel "Ang" wrote in message ... Hi, Please help, I have tried hard to rectify this using some different formula etc but to no avail. I have a spreadsheet which logs visits to customers so that we can get a % of visits which become orders, however we need this by Team/Individual/Product etc, so gets more intense. I have broken it down as far as possible to collate the data. I am using formula: =IF((AND(F4="Education",H40)),H4,0) to get the value of orders/team. i.e. if F4 is the Education Team and a figure is in order column over zero then put the figure (as this is an order). The Team column (i.e. Education) comes from a VLOOKUP table, so therefore they select staff initials (from drop down list) and it automatically looks up the team name. This works great until the blank lines, no initials selected, results in no team name (#n/a) and #N/A's in my result - This wouldn't matter only I cannot add up a column with them appearing, result in AutoSum is (yes you guessed it!) #N/A!! Am I missing something glaringly obvious? Please help, I'm so close to finishing this one! I have tried =if(ISNA..... but cannot seem to get it to work,. not sure if I am using this in the correct place. -- Ang. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#n/a drving me mad, need to force ignore or remove them - Help!
Hi,
Try this for summing with NAs in the range =SUMIF(J4:J14,"<"&10^23) or this =SUMIF(J4:J14,"<#N/A") Mike "Ang" wrote: Hi, Please help, I have tried hard to rectify this using some different formula etc but to no avail. I have a spreadsheet which logs visits to customers so that we can get a % of visits which become orders, however we need this by Team/Individual/Product etc, so gets more intense. I have broken it down as far as possible to collate the data. I am using formula: =IF((AND(F4="Education",H40)),H4,0) to get the value of orders/team. i.e. if F4 is the Education Team and a figure is in order column over zero then put the figure (as this is an order). The Team column (i.e. Education) comes from a VLOOKUP table, so therefore they select staff initials (from drop down list) and it automatically looks up the team name. This works great until the blank lines, no initials selected, results in no team name (#n/a) and #N/A's in my result - This wouldn't matter only I cannot add up a column with them appearing, result in AutoSum is (yes you guessed it!) #N/A!! Am I missing something glaringly obvious? Please help, I'm so close to finishing this one! I have tried =if(ISNA..... but cannot seem to get it to work,. not sure if I am using this in the correct place. -- Ang. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#n/a drving me mad, need to force ignore or remove them - Help!
Change the vlookup formula to
=IF(ISNA(VLOOKUP(A2,$B$2:$C$100,2,0)),"",VLOOKUP(A 2,$B$2:$C$100,2,0)) Note: Replace the ranges and lookup cells with whatever cells you are using -- Regards, Peo Sjoblom "Ang" wrote in message ... Hi, Please help, I have tried hard to rectify this using some different formula etc but to no avail. I have a spreadsheet which logs visits to customers so that we can get a % of visits which become orders, however we need this by Team/Individual/Product etc, so gets more intense. I have broken it down as far as possible to collate the data. I am using formula: =IF((AND(F4="Education",H40)),H4,0) to get the value of orders/team. i.e. if F4 is the Education Team and a figure is in order column over zero then put the figure (as this is an order). The Team column (i.e. Education) comes from a VLOOKUP table, so therefore they select staff initials (from drop down list) and it automatically looks up the team name. This works great until the blank lines, no initials selected, results in no team name (#n/a) and #N/A's in my result - This wouldn't matter only I cannot add up a column with them appearing, result in AutoSum is (yes you guessed it!) #N/A!! Am I missing something glaringly obvious? Please help, I'm so close to finishing this one! I have tried =if(ISNA..... but cannot seem to get it to work,. not sure if I am using this in the correct place. -- Ang. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#n/a drving me mad, need to force ignore or remove them - Help!
Suppose your drop-down is in D2, and you are using this value in your
lookup formula - you could have something like this: =IF(D2="","",VLOOKUP(D2, ... etc) Alternatively you would use ISNA like this: =IF(ISNA(your_formula),"",your_formula) Both of these will give you a blank instead of the error, but you can change the "" to 0 if you prefer. Hope this helps. Pete On Nov 18, 2:52*pm, Ang wrote: Hi, Please help, I have tried hard to rectify this using some different formula etc but to no avail. I have a spreadsheet which logs visits to customers so that we can get a % of visits which become orders, however we need this by Team/Individual/Product etc, so gets more intense. *I have broken it down as far as possible to collate the data. I am using formula: =IF((AND(F4="Education",H40)),H4,0) to get the value of orders/team. i.e. if F4 is the Education Team and a figure is in order column over zero then put the figure (as this is an order). The Team column (i.e. Education) comes from a VLOOKUP table, so therefore they select staff initials (from drop down list) and it automatically looks up the team name. This works great until the blank lines, no initials selected, results in no team name (#n/a) *and #N/A's in my result - This wouldn't matter only I cannot add up a column with them appearing, result in AutoSum is (yes you guessed it!) #N/A!! Am I missing something glaringly obvious? *Please help, I'm so close to finishing this one! I have tried =if(ISNA..... but cannot seem to get it to work,. not sure if I am using this in the correct place. -- Ang. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove convert/extract the number from'12345.56; ie remove ' sign | Excel Worksheet Functions | |||
How do I remove hyperlink if 'remove' option is disabled | New Users to Excel | |||
how to force a lookup | Excel Worksheet Functions | |||
force a $0.00 | Excel Discussion (Misc queries) | |||
Force Tab | Excel Worksheet Functions |