software

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)=quot; TAX
BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot; TAX BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))

Can you help?

Hi!

The formula you posted has a leading space in quot; Tax Billedquot;. 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)=quot;TAX
BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))

You might be able to use this much shorter alternative (normally entered,
not an array):

=SUMPRODUCT(--(INDEX(alabamasort,,1)=quot;Tax
Billedquot;),--(INDEX(alabamasort,,2)=quot;Totalquot;),INDEX(alabamasort, ,4))

Biff

quot;taxmomquot; gt; wrote in message
...
gt;I have a formula that the newsgroup help me with over a year ago. It worked
gt; perfectly and I use it though out my programs. However I want to use it
gt; in a
gt; similar situation and I cannot get it to work. The formula returns #num.
gt; it
gt; should return 723,314.70
gt;
gt; The range name is alabamasort. It is all in 1 worksheet different tabs.
gt; I want the formula to look in the range name alabamasort find TAX BILLED
gt; in
gt; column 1 and TOTAL in column 2 and return the value in column 4
gt;
gt; The columns are as follows:
gt; Type City/County State amount
gt;
gt; Taxble sales cottonwood al 1,303.14
gt; TAX BILLED TOTAL AL 723,314.70
gt;
gt; This is the formual
gt;
gt; =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=quot; TAX
gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;
gt; TAX BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt;
gt; Can you help?
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.

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; The formula you posted has a leading space in quot; Tax Billedquot;. Is that
gt; supposed to be there? Other than that, your formula works on my end. You can
gt; shorten it slightly:
gt;
gt; =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt;
gt; You might be able to use this much shorter alternative (normally entered,
gt; not an array):
gt;
gt; =SUMPRODUCT(--(INDEX(alabamasort,,1)=quot;Tax
gt; Billedquot;),--(INDEX(alabamasort,,2)=quot;Totalquot;),INDEX(alabamasort, ,4))
gt;
gt; Biff
gt;
gt; quot;taxmomquot; gt; wrote in message
gt; ...
gt; gt;I have a formula that the newsgroup help me with over a year ago. It worked
gt; gt; perfectly and I use it though out my programs. However I want to use it
gt; gt; in a
gt; gt; similar situation and I cannot get it to work. The formula returns #num.
gt; gt; it
gt; gt; should return 723,314.70
gt; gt;
gt; gt; The range name is alabamasort. It is all in 1 worksheet different tabs.
gt; gt; I want the formula to look in the range name alabamasort find TAX BILLED
gt; gt; in
gt; gt; column 1 and TOTAL in column 2 and return the value in column 4
gt; gt;
gt; gt; The columns are as follows:
gt; gt; Type City/County State amount
gt; gt;
gt; gt; Taxble sales cottonwood al 1,303.14
gt; gt; TAX BILLED TOTAL AL 723,314.70
gt; gt;
gt; gt; This is the formual
gt; gt;
gt; gt; =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=quot; TAX
gt; gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;
gt; gt; TAX BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt; gt;
gt; gt; Can you help?
gt;
gt;
gt;

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.

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; The formula you posted has a leading space in quot; Tax Billedquot;. Is that
gt; supposed to be there? Other than that, your formula works on my end. You can
gt; shorten it slightly:
gt;
gt; =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt;
gt; You might be able to use this much shorter alternative (normally entered,
gt; not an array):
gt;
gt; =SUMPRODUCT(--(INDEX(alabamasort,,1)=quot;Tax
gt; Billedquot;),--(INDEX(alabamasort,,2)=quot;Totalquot;),INDEX(alabamasort, ,4))
gt;
gt; Biff
gt;
gt; quot;taxmomquot; gt; wrote in message
gt; ...
gt; gt;I have a formula that the newsgroup help me with over a year ago. It worked
gt; gt; perfectly and I use it though out my programs. However I want to use it
gt; gt; in a
gt; gt; similar situation and I cannot get it to work. The formula returns #num.
gt; gt; it
gt; gt; should return 723,314.70
gt; gt;
gt; gt; The range name is alabamasort. It is all in 1 worksheet different tabs.
gt; gt; I want the formula to look in the range name alabamasort find TAX BILLED
gt; gt; in
gt; gt; column 1 and TOTAL in column 2 and return the value in column 4
gt; gt;
gt; gt; The columns are as follows:
gt; gt; Type City/County State amount
gt; gt;
gt; gt; Taxble sales cottonwood al 1,303.14
gt; gt; TAX BILLED TOTAL AL 723,314.70
gt; gt;
gt; gt; This is the formual
gt; gt;
gt; gt; =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=quot; TAX
gt; gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;
gt; gt; TAX BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt; gt;
gt; gt; Can you help?
gt;
gt;
gt;

#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 Toolsgt;Formula Auditing
Trace Error

See if that shows you where the error is coming from.

Biff

quot;taxmomquot; gt; wrote in message
...
gt; Biff,
gt;
gt; What does the #NUM mean. Why does this come up? I tried the second
gt; formula
gt; and it does the same thing. The formual works outside this spreadsheet
gt; with
gt; other data. Why not in this spreadsheet?
gt;
gt; Please help me solve this.
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; The formula you posted has a leading space in quot; Tax Billedquot;. Is that
gt;gt; supposed to be there? Other than that, your formula works on my end. You
gt;gt; can
gt;gt; shorten it slightly:
gt;gt;
gt;gt; =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt;gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt;gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt;gt;
gt;gt; You might be able to use this much shorter alternative (normally entered,
gt;gt; not an array):
gt;gt;
gt;gt; =SUMPRODUCT(--(INDEX(alabamasort,,1)=quot;Tax
gt;gt; Billedquot;),--(INDEX(alabamasort,,2)=quot;Totalquot;),INDEX(alabamasort, ,4))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;taxmomquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have a formula that the newsgroup help me with over a year ago. It
gt;gt; gt;worked
gt;gt; gt; perfectly and I use it though out my programs. However I want to use
gt;gt; gt; it
gt;gt; gt; in a
gt;gt; gt; similar situation and I cannot get it to work. The formula returns
gt;gt; gt; #num.
gt;gt; gt; it
gt;gt; gt; should return 723,314.70
gt;gt; gt;
gt;gt; gt; The range name is alabamasort. It is all in 1 worksheet different
gt;gt; gt; tabs.
gt;gt; gt; I want the formula to look in the range name alabamasort find TAX
gt;gt; gt; BILLED
gt;gt; gt; in
gt;gt; gt; column 1 and TOTAL in column 2 and return the value in column 4
gt;gt; gt;
gt;gt; gt; The columns are as follows:
gt;gt; gt; Type City/County State amount
gt;gt; gt;
gt;gt; gt; Taxble sales cottonwood al 1,303.14
gt;gt; gt; TAX BILLED TOTAL AL 723,314.70
gt;gt; gt;
gt;gt; gt; This is the formual
gt;gt; gt;
gt;gt; gt; =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=quot; TAX
gt;gt; gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;
gt;gt; gt; TAX BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt;gt; gt;
gt;gt; gt; Can you help?
gt;gt;
gt;gt;
gt;gt;
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 !quot;Biffquot; wrote:

gt; #NUM! means that there is an invalid numeric value or the formula is
gt; expecting a numeric value and gets something else.
gt;
gt; Are there any other types of errors in any of the referenced ranges?
gt;
gt; Ae you sure that leading space is actually a char(32) space? That wouldn't
gt; cause a #NUM! error but it could lead to a result of 0.
gt;
gt; What version of Excel are you using? See if this is available in your
gt; version:
gt;
gt; Select the formula cell.
gt; Goto Toolsgt;Formula Auditing
gt; Trace Error
gt;
gt; See if that shows you where the error is coming from.
gt;
gt; Biff
gt;
gt; quot;taxmomquot; gt; wrote in message
gt; ...
gt; gt; Biff,
gt; gt;
gt; gt; What does the #NUM mean. Why does this come up? I tried the second
gt; gt; formula
gt; gt; and it does the same thing. The formual works outside this spreadsheet
gt; gt; with
gt; gt; other data. Why not in this spreadsheet?
gt; gt;
gt; gt; Please help me solve this.
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt; The formula you posted has a leading space in quot; Tax Billedquot;. Is that
gt; gt;gt; supposed to be there? Other than that, your formula works on my end. You
gt; gt;gt; can
gt; gt;gt; shorten it slightly:
gt; gt;gt;
gt; gt;gt; =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt; gt;gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt; gt;gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt; gt;gt;
gt; gt;gt; You might be able to use this much shorter alternative (normally entered,
gt; gt;gt; not an array):
gt; gt;gt;
gt; gt;gt; =SUMPRODUCT(--(INDEX(alabamasort,,1)=quot;Tax
gt; gt;gt; Billedquot;),--(INDEX(alabamasort,,2)=quot;Totalquot;),INDEX(alabamasort, ,4))
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;taxmomquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I have a formula that the newsgroup help me with over a year ago. It
gt; gt;gt; gt;worked
gt; gt;gt; gt; perfectly and I use it though out my programs. However I want to use
gt; gt;gt; gt; it
gt; gt;gt; gt; in a
gt; gt;gt; gt; similar situation and I cannot get it to work. The formula returns
gt; gt;gt; gt; #num.
gt; gt;gt; gt; it
gt; gt;gt; gt; should return 723,314.70
gt; gt;gt; gt;
gt; gt;gt; gt; The range name is alabamasort. It is all in 1 worksheet different
gt; gt;gt; gt; tabs.
gt; gt;gt; gt; I want the formula to look in the range name alabamasort find TAX
gt; gt;gt; gt; BILLED
gt; gt;gt; gt; in
gt; gt;gt; gt; column 1 and TOTAL in column 2 and return the value in column 4
gt; gt;gt; gt;
gt; gt;gt; gt; The columns are as follows:
gt; gt;gt; gt; Type City/County State amount
gt; gt;gt; gt;
gt; gt;gt; gt; Taxble sales cottonwood al 1,303.14
gt; gt;gt; gt; TAX BILLED TOTAL AL 723,314.70
gt; gt;gt; gt;
gt; gt;gt; gt; This is the formual
gt; gt;gt; gt;
gt; gt;gt; gt; =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=quot; TAX
gt; gt;gt; gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;
gt; gt;gt; gt; TAX BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt; gt;gt; gt;
gt; gt;gt; gt; Can you help?
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

You're welcome. Thanks for the feedback!

Biff

quot;taxmomquot; gt; wrote in message
...
gt; Thank You, Thank You, Thank You !
gt;
gt; The company just got windows PX. There was something strange. The trace
gt; error traced to a total on the same page and a blank cell but, I had no
gt; reference to that total either in the formula or range name. I doubled
gt; check
gt; my ranges it is exactly the range I wanted. So, I deleted all of the
gt; previous range names and started new and it worked. I'm very gratefull
gt; for
gt; your help. Have a great day because you just made mine !
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; #NUM! means that there is an invalid numeric value or the formula is
gt;gt; expecting a numeric value and gets something else.
gt;gt;
gt;gt; Are there any other types of errors in any of the referenced ranges?
gt;gt;
gt;gt; Ae you sure that leading space is actually a char(32) space? That
gt;gt; wouldn't
gt;gt; cause a #NUM! error but it could lead to a result of 0.
gt;gt;
gt;gt; What version of Excel are you using? See if this is available in your
gt;gt; version:
gt;gt;
gt;gt; Select the formula cell.
gt;gt; Goto Toolsgt;Formula Auditing
gt;gt; Trace Error
gt;gt;
gt;gt; See if that shows you where the error is coming from.
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;taxmomquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Biff,
gt;gt; gt;
gt;gt; gt; What does the #NUM mean. Why does this come up? I tried the second
gt;gt; gt; formula
gt;gt; gt; and it does the same thing. The formual works outside this spreadsheet
gt;gt; gt; with
gt;gt; gt; other data. Why not in this spreadsheet?
gt;gt; gt;
gt;gt; gt; Please help me solve this.
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Hi!
gt;gt; gt;gt;
gt;gt; gt;gt; The formula you posted has a leading space in quot; Tax Billedquot;. Is that
gt;gt; gt;gt; supposed to be there? Other than that, your formula works on my end.
gt;gt; gt;gt; You
gt;gt; gt;gt; can
gt;gt; gt;gt; shorten it slightly:
gt;gt; gt;gt;
gt;gt; gt;gt; =IF(ISNA(MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt;gt; gt;gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0)),0,IND EX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;TAX
gt;gt; gt;gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt;gt; gt;gt;
gt;gt; gt;gt; You might be able to use this much shorter alternative (normally
gt;gt; gt;gt; entered,
gt;gt; gt;gt; not an array):
gt;gt; gt;gt;
gt;gt; gt;gt; =SUMPRODUCT(--(INDEX(alabamasort,,1)=quot;Tax
gt;gt; gt;gt; Billedquot;),--(INDEX(alabamasort,,2)=quot;Totalquot;),INDEX(alabamasort, ,4))
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt; quot;taxmomquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I have a formula that the newsgroup help me with over a year ago. It
gt;gt; gt;gt; gt;worked
gt;gt; gt;gt; gt; perfectly and I use it though out my programs. However I want to
gt;gt; gt;gt; gt; use
gt;gt; gt;gt; gt; it
gt;gt; gt;gt; gt; in a
gt;gt; gt;gt; gt; similar situation and I cannot get it to work. The formula returns
gt;gt; gt;gt; gt; #num.
gt;gt; gt;gt; gt; it
gt;gt; gt;gt; gt; should return 723,314.70
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; The range name is alabamasort. It is all in 1 worksheet different
gt;gt; gt;gt; gt; tabs.
gt;gt; gt;gt; gt; I want the formula to look in the range name alabamasort find TAX
gt;gt; gt;gt; gt; BILLED
gt;gt; gt;gt; gt; in
gt;gt; gt;gt; gt; column 1 and TOTAL in column 2 and return the value in column 4
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; The columns are as follows:
gt;gt; gt;gt; gt; Type City/County State amount
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Taxble sales cottonwood al 1,303.14
gt;gt; gt;gt; gt; TAX BILLED TOTAL AL 723,314.70
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; This is the formual
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; =IF(ISNA(INDEX(alabamasort,MATCH(1,(INDEX(alabamas ort,,1)=quot; TAX
gt;gt; gt;gt; gt; BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4)),0, INDEX(alabamasort,MATCH(1,(INDEX(alabamasort,,1)=quot;
gt;gt; gt;gt; gt; TAX BILLEDquot;)*(INDEX(alabamasort,,2)=quot;TOTALquot;),0),4))
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Can you help?
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;