VALUE Errors
 

hi, not sure if posing question right, but am trying to put a formula
together by
"AND"ing two items together. I can get one item to work, but get Value
errors when I break examples down / use: Evaluate formula. thanks, examples
a

works by self:
=SUMPRODUCT(($CK$162:$CK$1252="A00")*($CU$162:$CU$ 1252=10))

evaluation shows value errors, but if isna is setup / working ok, then skip..
=IF(ISNA(INDEX(ROW($CK$162:$CK$1252)-ROW($CK$162),
MATCH(TRUE,(($CK$162:$CK$1252="A00")*($CU$162:$CU$ 1252=$DD$7)),0))),"","yes")


ultimate goal, example: (one formula, middle section the problem?)
PROBLEM?: is it with line: INDEX(ROW($CK$162:$CK$1252),

=HYPERLINK(IF(ISNA(INDEX(ROW($CK$162:$CK$1252)-ROW($CK$162),MATCH(TRUE,(($CK$162:$CK$1252="A00")* ($CU$162:$CU$1252=$DD$7)),0))),"",

"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$CK12),"$",""),ROW(),"")&
INDEX(ROW($CK$162:$CK$1252),
MATCH(TRUE,(($CK$162:$CK$1252="A00")*($CU$162:$CU$ 1252=$DD$7)),0))),$Z$3,0))),

SUMPRODUCT(($CK$162:$CK$1252="A00")*($CU$162:$CU$1 252=$DD$7)))

VALUE Errors
 

You get #VALUE errors when you try to carry out arithmetic on non-
numeric values, so a good starting point would be to check out your
data to see if there are any "numbers" which are actually text values.
You could make use of:

=ISNUMBER(cell)

which will return FALSE if the value in cell is text.

Hope this helps.

Pete

On Oct 10, 6:05 am, nastech wrote:
hi, not sure if posing question right, but am trying to put a formula
together by
"AND"ing two items together. I can get one item to work, but get Value
errors when I break examples down / use: Evaluate formula. thanks, examples
a

works by self:
=SUMPRODUCT(($CK$162:$CK$1252="A00")*($CU$162:$CU$ 1252=10))

evaluation shows value errors, but if isna is setup / working ok, then skip..
=IF(ISNA(INDEX(ROW($CK$162:$CK$1252)-ROW($CK$162),
MATCH(TRUE,(($CK$162:$CK$1252="A00")*($CU$162:$CU$ 1252=$DD$7)),0))),"","ye*s")

ultimate goal, example: (one formula, middle section the problem?)
PROBLEM?: is it with line: INDEX(ROW($CK$162:$CK$1252),

=HYPERLINK(IF(ISNA(INDEX(ROW($CK$162:$CK$1252)-ROW($CK$162),MATCH(TRUE,(($C*K$162:$CK$1252="A00") *($CU$162:$CU$1252=$DD$7)),0))),"",

"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$CK*12),"$",""),ROW(),"")&
INDEX(ROW($CK$162:$CK$1252),
MATCH(TRUE,(($CK$162:$CK$1252="A00")*($CU$162:$CU$ 1252=$DD$7)),0))),$Z$3,0*))),

SUMPRODUCT(($CK$162:$CK$1252="A00")*($CU$162:$CU$1 252=$DD$7)))