Ask Your Question
0

Why returning Zero value

asked 2016-09-25 06:10:07 +0200

JG101 gravatar image

Please see attachment and info on document

I have spend few hours with different variations and not able to fix, not sure why its returning a zero value. Just because the 1st column is zero or maybe thats not the reason.

thank you in advance for any help provided.

Values equal Zero does not return correctly 09-24-2016.ods

edit retag flag offensive close merge delete

Comments

in the document TEAM 1 and T should return a value of 12 from Row 11, and the Zero value in Column M should have a value from Column B. For some reason Index - Match is only picking up value from column B if its the first name for each row If their is no value in the first name of both names then it will not return a value , but it should find the value in next row since match finds that value but does not display it correctly How is this fixed

JG101 gravatar imageJG101 ( 2016-09-25 20:52:05 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2016-09-25 22:30:14 +0200

mark_t gravatar image

updated 2016-09-25 22:37:40 +0200

Try the following formula for M10 and copy down the column.

=IFNA(INDEX($B$10:$B$73,MATCH(K10&J10,IF($B$10:$B$73<>"",$C$10:$C$73&$D$10:$D$73,""),0)),"")

Only rows where the B column is not blank will be included in the search array.

Edit to add an explanation.

Note that the MATCH function will only return the index of the first match found in the search array, it has no way to determine that you want to skip the first match because it doesn't have a value in column B. The IF function added above will evalauate as an array result where the rows that B is blank will have a blank row in the array result.

edit flag offensive delete link more

Comments

"...where the B column is not blank will be included..."
Please note:
NOT(ISBLANK(B10)) and
(B10<>"")
yield different results if B10 is containing a formula which returned the empty string.
(The first formula returning TRUE then, the second one FALSE; numerically 1 vs. 0.)

Lupp gravatar imageLupp ( 2016-09-26 11:48:35 +0200 )edit

Please also note:
The correct answer to the why-question was given by @pierre-yves samyn. This answer by @mark_t and my own answer to find in this thread ignore the "why" and actually assume a "how to get the expected result?".

Lupp gravatar imageLupp ( 2016-09-26 11:54:46 +0200 )edit
0

answered 2016-09-25 07:42:40 +0200

pierre-yves samyn gravatar image

Hi

Same result with =B10

You always get 0 when the referred cell is empty. Several ways to do this according to your needs:

  • Adapt you formula using ISBLANK
  • Use format 0 leading zeroes (but also impact cells containing zero as solutions below)
  • Do not print 0 (FormatPageSheet tab▸untick Zero values)
  • ToolsOptionsCalcViewDisplay: Zero values

Adjust the formula seems the best solution in this case...

Regards

edit flag offensive delete link more

Comments

I'm not understanding what your referring to.... in the document TEAM 1 and T should return a value of 12 from Row 11, and the Zero value in Column M should have a value from Column B. For some reason Index - Match is only picking up value from column B if its the first name for each row If their is no value in the first name of both names then it will not return a value , but it should find the value in next row since match finds that value but does not display it correctly How is this fixed

JG101 gravatar imageJG101 ( 2016-09-25 17:13:18 +0200 )edit
0

answered 2016-09-25 23:04:43 +0200

Lupp gravatar image

updated 2016-09-25 23:21:01 +0200

@JG101: The same idea as used to get combined matches from columns C and D regarding the content in the current row of columns K and J you may use to restrict the matches to rows where ISBLANK(Bnn) is false (numeric: 0). Enter
=IFNA(INDEX($B$10:$B$73;MATCH(K10&J10&0;$C$10:$C$73&$D$10:$D$73&ISBLANK($B$10:$B$73);0));"")
into M10 and fill down.

(Editing: See this reworked example.)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-25 06:10:07 +0200

Seen: 664 times

Last updated: Sep 25 '16