| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#num Error index, match
I have a formula that the newsgroup help me with over a year ago. It worked
perfectly and I use it though out my programs. However I want to use it in a similar situation and I cannot get it to work. The formula returns #num. it should return 723,314.70 The range name is alabamasort. It is all in 1 worksheet different tabs. I want the formula to look in the range name alabamasort find TAX BILLED in column 1 and TOTAL in column 2 and return the value in column 4 The columns are as follows: Type City/County State amount Taxble sales cottonwood al 1,303.14 TAX BILLED TOTAL AL 723,314.70 This is the formual =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) Can you help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#num Error index, match
Hi!
The formula you posted has a leading space in " Tax Billed". Is that supposed to be there? Other than that, your formula works on my end. You can shorten it slightly: =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) You might be able to use this much shorter alternative (normally entered, not an array): =SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4)) Biff "taxmom" wrote in message ... I have a formula that the newsgroup help me with over a year ago. It worked perfectly and I use it though out my programs. However I want to use it in a similar situation and I cannot get it to work. The formula returns #num. it should return 723,314.70 The range name is alabamasort. It is all in 1 worksheet different tabs. I want the formula to look in the range name alabamasort find TAX BILLED in column 1 and TOTAL in column 2 and return the value in column 4 The columns are as follows: Type City/County State amount Taxble sales cottonwood al 1,303.14 TAX BILLED TOTAL AL 723,314.70 This is the formual =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) Can you help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#num Error index, match
Hi Biff,
If I enter it as an array I still get the #num!, if I just enter it I get zero data. I can't figure out why its not working. I made sure the 1st two columns were text and the 4th column number. The data was copied from Monarch so the space in front is there from the report so I kept it there. I even typed in the the labels right under the copied over data, fixed the formula to remove the space and I still get #num!. I tried a separate spreadsheet using the range testsort about five lines and now I get #REF. I cant figure out what I'm doing wrong, what is the hang up? Thanks so very much for your help. At least I know the formula is correct. I will keep working on it. Thanks again. "Biff" wrote: Hi! The formula you posted has a leading space in " Tax Billed". Is that supposed to be there? Other than that, your formula works on my end. You can shorten it slightly: =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) You might be able to use this much shorter alternative (normally entered, not an array): =SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4)) Biff "taxmom" wrote in message ... I have a formula that the newsgroup help me with over a year ago. It worked perfectly and I use it though out my programs. However I want to use it in a similar situation and I cannot get it to work. The formula returns #num. it should return 723,314.70 The range name is alabamasort. It is all in 1 worksheet different tabs. I want the formula to look in the range name alabamasort find TAX BILLED in column 1 and TOTAL in column 2 and return the value in column 4 The columns are as follows: Type City/County State amount Taxble sales cottonwood al 1,303.14 TAX BILLED TOTAL AL 723,314.70 This is the formual =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) Can you help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#num Error index, match
Biff,
What does the #NUM mean. Why does this come up? I tried the second formula and it does the same thing. The formual works outside this spreadsheet with other data. Why not in this spreadsheet? Please help me solve this. "Biff" wrote: Hi! The formula you posted has a leading space in " Tax Billed". Is that supposed to be there? Other than that, your formula works on my end. You can shorten it slightly: =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) You might be able to use this much shorter alternative (normally entered, not an array): =SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4)) Biff "taxmom" wrote in message ... I have a formula that the newsgroup help me with over a year ago. It worked perfectly and I use it though out my programs. However I want to use it in a similar situation and I cannot get it to work. The formula returns #num. it should return 723,314.70 The range name is alabamasort. It is all in 1 worksheet different tabs. I want the formula to look in the range name alabamasort find TAX BILLED in column 1 and TOTAL in column 2 and return the value in column 4 The columns are as follows: Type City/County State amount Taxble sales cottonwood al 1,303.14 TAX BILLED TOTAL AL 723,314.70 This is the formual =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) Can you help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#num Error index, match
#NUM! means that there is an invalid numeric value or the formula is
expecting a numeric value and gets something else. Are there any other types of errors in any of the referenced ranges? Ae you sure that leading space is actually a char(32) space? That wouldn't cause a #NUM! error but it could lead to a result of 0. What version of Excel are you using? See if this is available in your version: Select the formula cell. Goto ToolsFormula Auditing Trace Error See if that shows you where the error is coming from. Biff "taxmom" wrote in message ... Biff, What does the #NUM mean. Why does this come up? I tried the second formula and it does the same thing. The formual works outside this spreadsheet with other data. Why not in this spreadsheet? Please help me solve this. "Biff" wrote: Hi! The formula you posted has a leading space in " Tax Billed". Is that supposed to be there? Other than that, your formula works on my end. You can shorten it slightly: =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) You might be able to use this much shorter alternative (normally entered, not an array): =SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4)) Biff "taxmom" wrote in message ... I have a formula that the newsgroup help me with over a year ago. It worked perfectly and I use it though out my programs. However I want to use it in a similar situation and I cannot get it to work. The formula returns #num. it should return 723,314.70 The range name is alabamasort. It is all in 1 worksheet different tabs. I want the formula to look in the range name alabamasort find TAX BILLED in column 1 and TOTAL in column 2 and return the value in column 4 The columns are as follows: Type City/County State amount Taxble sales cottonwood al 1,303.14 TAX BILLED TOTAL AL 723,314.70 This is the formual =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) Can you help? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#num Error index, match
Thank You, Thank You, Thank You !
The company just got windows PX. There was something strange. The trace error traced to a total on the same page and a blank cell but, I had no reference to that total either in the formula or range name. I doubled check my ranges it is exactly the range I wanted. So, I deleted all of the previous range names and started new and it worked. I'm very gratefull for your help. Have a great day because you just made mine ! "Biff" wrote: #NUM! means that there is an invalid numeric value or the formula is expecting a numeric value and gets something else. Are there any other types of errors in any of the referenced ranges? Ae you sure that leading space is actually a char(32) space? That wouldn't cause a #NUM! error but it could lead to a result of 0. What version of Excel are you using? See if this is available in your version: Select the formula cell. Goto ToolsFormula Auditing Trace Error See if that shows you where the error is coming from. Biff "taxmom" wrote in message ... Biff, What does the #NUM mean. Why does this come up? I tried the second formula and it does the same thing. The formual works outside this spreadsheet with other data. Why not in this spreadsheet? Please help me solve this. "Biff" wrote: Hi! The formula you posted has a leading space in " Tax Billed". Is that supposed to be there? Other than that, your formula works on my end. You can shorten it slightly: =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) You might be able to use this much shorter alternative (normally entered, not an array): =SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4)) Biff "taxmom" wrote in message ... I have a formula that the newsgroup help me with over a year ago. It worked perfectly and I use it though out my programs. However I want to use it in a similar situation and I cannot get it to work. The formula returns #num. it should return 723,314.70 The range name is alabamasort. It is all in 1 worksheet different tabs. I want the formula to look in the range name alabamasort find TAX BILLED in column 1 and TOTAL in column 2 and return the value in column 4 The columns are as follows: Type City/County State amount Taxble sales cottonwood al 1,303.14 TAX BILLED TOTAL AL 723,314.70 This is the formual =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) Can you help? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#num Error index, match
You're welcome. Thanks for the feedback!
Biff "taxmom" wrote in message ... Thank You, Thank You, Thank You ! The company just got windows PX. There was something strange. The trace error traced to a total on the same page and a blank cell but, I had no reference to that total either in the formula or range name. I doubled check my ranges it is exactly the range I wanted. So, I deleted all of the previous range names and started new and it worked. I'm very gratefull for your help. Have a great day because you just made mine ! "Biff" wrote: #NUM! means that there is an invalid numeric value or the formula is expecting a numeric value and gets something else. Are there any other types of errors in any of the referenced ranges? Ae you sure that leading space is actually a char(32) space? That wouldn't cause a #NUM! error but it could lead to a result of 0. What version of Excel are you using? See if this is available in your version: Select the formula cell. Goto ToolsFormula Auditing Trace Error See if that shows you where the error is coming from. Biff "taxmom" wrote in message ... Biff, What does the #NUM mean. Why does this come up? I tried the second formula and it does the same thing. The formual works outside this spreadsheet with other data. Why not in this spreadsheet? Please help me solve this. "Biff" wrote: Hi! The formula you posted has a leading space in " Tax Billed". Is that supposed to be there? Other than that, your formula works on my end. You can shorten it slightly: =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)="TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) You might be able to use this much shorter alternative (normally entered, not an array): =SUMPRODUCT(--(INDEX(alabamasort,,1)="Tax Billed"),--(INDEX(alabamasort,,2)="Total"),INDEX(alabamasort, ,4)) Biff "taxmom" wrote in message ... I have a formula that the newsgroup help me with over a year ago. It worked perfectly and I use it though out my programs. However I want to use it in a similar situation and I cannot get it to work. The formula returns #num. it should return 723,314.70 The range name is alabamasort. It is all in 1 worksheet different tabs. I want the formula to look in the range name alabamasort find TAX BILLED in column 1 and TOTAL in column 2 and return the value in column 4 The columns are as follows: Type City/County State amount Taxble sales cottonwood al 1,303.14 TAX BILLED TOTAL AL 723,314.70 This is the formual =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=" TAX BILLED")*(INDEX(alabamasort,,2)="TOTAL"),0),4)) Can you help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match Index | Excel Worksheet Functions | |||
| Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
| Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
| Find a match that;s not exact | Excel Worksheet Functions | |||
| Vlookup, Index & Match | Excel Worksheet Functions | |||
Hybrid Mode
