Public Function.

[How_Do_I]

Hi…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The Jeanie is working using the following Public Function:
<o></o>

Public Function V(Optional vrnt As Variant) As Variant<o:p></o:p>

'<o:p></o:p>
' Stephen Dunn<o:p></o:p>
' 2002-09-12<o:p></o:p>
'<o:p></o:p>
Static vrntV As Variant<o:p></o:p>
If Not IsMissing(vrnt) Then vrntV = vrnt<o:p></o:p>
V = vrntV<o:p></o:p>
End Function

<o></o>
It is giving the red highlighted results.
<o></o>
However, when I remove the data in Row A3:E3 everything disappears from the red highlighted area… It should return the “1”. Obviously this is a much reduced Jeanie to show my issue but strangely I get a #NUM error in my full workbook where as in this Jeanie example I get nothing.
<o></o>
Can anyone see what is going wrong please?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 32px"><COL style="WIDTH: 32px"><COL style="WIDTH: 133px"><COL style="WIDTH: 133px"><COL style="WIDTH: 71px"><COL style="WIDTH: 71px"><COL style="WIDTH: 84px"><COL style="WIDTH: 64px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 59px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Ref</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Week</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Home</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Away</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Date</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Matches</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD><TD>Hull City</TD><TD>Blackpool</TD><TD style="TEXT-ALIGN: right">11/11/2011</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD></TD><TD>Coventry City</TD><TD>Leicester City</TD><TD style="TEXT-ALIGN: right">11/11/2011</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="BACKGROUND-COLOR: #ffff00">Blackpool</TD><TD style="BACKGROUND-COLOR: #ffff00">Leicester City</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">Hull City</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000">1</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">Coventry City</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000">2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G2</TD><TD>=COUNT(Date)</TD></TR><TR><TD>H6</TD><TD>{=IF(ISNUMBER(V(MATCH(1,IF(Home=$G6,IF(Away=H$5,1,"")),0))),V(),"")}</TD></TR><TR><TD>I6</TD><TD>{=IF(ISNUMBER(V(MATCH(1,IF(Home=$G6,IF(Away=I$5,1,"")),0))),V(),"")}</TD></TR><TR><TD>H7</TD><TD>{=IF(ISNUMBER(V(MATCH(1,IF(Home=$G7,IF(Away=H$5,1,"")),0))),V(),"")}</TD></TR><TR><TD>I7</TD><TD>{=IF(ISNUMBER(V(MATCH(1,IF(Home=$G7,IF(Away=I$5,1,"")),0))),V(),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR><TR><TD><TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Names in Formulas </TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Name</TD><TD>Applies to</TD></TR><TR><TD>G2</TD><TD>Date</TD><TD>=Sheet1!$E$2:$E$18</TD></TR><TR><TD>H6</TD><TD>Away</TD><TD>=INDEX(ResultsArea,,4)</TD></TR><TR><TD>H6</TD><TD>Home</TD><TD>=INDEX(ResultsArea,,3)</TD></TR><TR><TD>H6</TD><TD>ResultsArea</TD><TD>=Sheet1!$A$2:INDEX(Sheet1!$D:$D,Sheet1!$G$2+1)</TD></TR><TR><TD>I6</TD><TD>Away</TD><TD>=INDEX(ResultsArea,,4)</TD></TR><TR><TD>I6</TD><TD>Home</TD><TD>=INDEX(ResultsArea,,3)</TD></TR><TR><TD>I6</TD><TD>ResultsArea</TD><TD>=Sheet1!$A$2:INDEX(Sheet1!$D:$D,Sheet1!$G$2+1)</TD></TR><TR><TD>H7</TD><TD>Away</TD><TD>=INDEX(ResultsArea,,4)</TD></TR><TR><TD>H7</TD><TD>Home</TD><TD>=INDEX(ResultsArea,,3)</TD></TR><TR><TD>H7</TD><TD>ResultsArea</TD><TD>=Sheet1!$A$2:INDEX(Sheet1!$D:$D,Sheet1!$G$2+1)</TD></TR><TR><TD>I7</TD><TD>Away</TD><TD>=INDEX(ResultsArea,,4)</TD></TR><TR><TD>I7</TD><TD>Home</TD><TD>=INDEX(ResultsArea,,3)</TD></TR><TR><TD>I7</TD><TD>ResultsArea</TD><TD>=Sheet1!$A$2:INDEX(Sheet1!$D:$D,Sheet1!$G$2+1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[jasonb75]

I will look at that Jason... The custom function was introduced to stop errors returns and/or double calculations when a game has not been played.

Your solution returns a "0" if the game hasn't been played so your solution has already become a double calculation:

=IF(SUMPRODUCT((Home=$BM12)*(Away=BN$1)*Ref)=0,"",SUMPRODUCT((Home=$BM12)*(Away=BN$1)*Ref))

Your edited version of the formula is a double calculation, my suggestion wasn't.

If you're seeing "0" results then that implies that you didn't use the format that I suggested for the cells.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Ref	Week	Home	Away	Date		Matches
2	1		Hull City	Blackpool	11/11/2011		2
3	2		Coventry City	Leicester City	11/11/2011
4
5								Blackpool	Leicester City
6							Hull City	1
7							Coventry City		2
Sheet1

 

[Aladin Akyurek]

Just a little observation, you don't need a custom function to do that.

=SUMPRODUCT((Home=$G6)*(Away=H$5)*Ref)

Format the red cells using [=0]"";#

The outcome is not calculated by the udf. The V() is used to prevent computing twice the conditional MATCH bit. The formula as given computes the intended outcome. That's not the issue. The issue is that, as OP observes, why the MATCH bit behaves as it does when the data area gets reduced by delete operations to just one record.

By the way, deploying a SumProduct formula for retrieval is risky for the data area contain duplicate data records.

 

[jasonb75]

The outcome is not calculated by the udf. The V() is used to prevent computing twice the conditional MATCH bit. The formula as given computes the intended outcome. That's not the issue. The issue is that, as OP observes, why the MATCH bit behaves as it does when the data area gets reduced by delete operations to just one record.

By the way, deploying a SumProduct formula for retrieval is risky for the data area contain duplicate data records.

I'm quite aware of what the udf is doing, my point was that a simple formula with a native function could eliminate the need for the udf.

In addition it also eliminates the need for the MATCH function, in turn eliminating the observed behaviour and the need for a fix.

Also, the sumproduct formula uses the same arrays, but with 1 function instead of 7, which I made the mistake of thinking would be more efficient, as the OP later mentioned preventing double calculating, I believe it was a worthwhile suggestion.

Finally, I do believe that there should be no duplicates in the data set so the use of sumproduct shouldn't be an issue.

 

[How_Do_I]

Your edited version of the formula is a double calculation, my suggestion wasn't.

If you're seeing "0" results then that implies that you didn't use the format that I suggested for the cells.

LOL... I'm guilty as charged... I didn't read your post correctly!

I don't like it when two people who have contributed so much to my workbook have a disagreement but, it has to be said that I wanted a fix and explanation as to why my UDF failed and thanks to Aladin that is what I got...

 

[jasonb75]

LOL... I'm guilty as charged... I didn't read your post correctly!

parole granted