Excel (VBA) Assistance needed

[Matthew]

21/10/2002	International	Indoor	Carpet	Quarterfinals	3	Federer R.	Roddick A.
07/01/2002	International	Outdoor	Hard	Semifinals	3	Federer R.	Roddick A.
22/10/2001	International	Indoor	Carpet	Quarterfinals	3	Federer R.	Roddick A.

If I have the above data as a history for this match:

04/07/2003

Grand Slam

Outdoor

Grass

Semifinals

5

Federer R.

Roddick A.

and I want to calculate the win % age for each player on the surface of the upcoming game.

My code currently counts the instances of the player names in the history data where the surface is the same.

You can see my problem, in the history data Federer has not player Roddick before the upcoming match. So I end up trying to do the calculation:

zero / zero

and i get an error.

"Run-time error '6':

Overflow"

I assume i need to change my code to cope with this.

The code as it stands is this:

player_1_h2h_surface_win_pct = (player1surfacecount / (player1surfacecount + player2surfacecount)) * 100

How do I change the code to cope with zero / zero?????

I could do On Error etc but I assume there must be a cleverer way.

Thanks in advance

Matt

[PAULM03]

I don't really use VBA but I have a bit of experience programming with matlab. The way I usually deal with that is to add the matlab 'epsilon' to the denominator. Epsilon is the limit of floating point precsion in matlab (10^-52, using double precision). I think there must be an equivalent in VBA. If not just add a really small number to your code manually, say 0.0000000000000001. This won't change the result within the limits of accuracy you're expecting and you shouldn't get a division by 0 error.

[mcgin]

Matt,

Only way to do other then On error os to have a check for the 0/0 condition using an if/else block. It's not very clean, but does exactly what you want

if ((player1surfacecount + player2surfacecount)=0)
player_1_h2h_surface_win_pct = 0 'Or whatever you want)
else
player_1_h2h_surface_win_pct = (player1surfacecount / (player1surfacecount + player2surfacecount)) * 100

[Matthew]

PAULM03, mcgin,

Thanks very much - I went with mcgin's suggestion.

Like most things - simple solutions are the way forward.
My brain must have been a bit fuzzy at 2:45am.

Thanks again.