help with formula to show certain zeroes

[Clueless Newcomer]

Hi all,

My name says it all, I recently retired and as a lifelong backer of horses, I have recently started to use Excel in order to try and produce data more speedily and usable for form study but to use a racing term, I seem to have fallen at a Hurdle.

Anything I've learned and utilised in my Workbooks, I have copied and pasted from going through this site's Archive, so thanks to all those that give so freely of their time, It's much appreciated here.


[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]25[/TD]
[TD]50[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]33[/TD]
[TD]0[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Please be kind enough to bear with me while I explain what I require, perhaps in a longwinded (not your language) manner.

I end up everyday with multiple worksheets (one for every race that day in Ireland and the UK) in a single workbook.

Even after running macros in each worksheet, I am left with a lot of cells (Template size pre macro 4000 rows and 380 columns)

As I could have anywhere from 30 to 80 worksheets ( 1 for each race) leaving the workbook to show zeros would drive me crazy trying to pick out actual figures, so I opted to not show a Zero where there's a Zero value.

This is where I ran into problems, so I turned show zeroes back on.

To get the relevent zeroes to show and hide the unwanted zeroes I conditionally formatted the cells to colour the zero to the same colour the cell is filled with (all workbook cells are various colour filled).

The table above is what I want and what I have but by a circuitous route and is only good for viewing, when printing I will still be printing the hidden/unseen zeroes.

Each row above represents a horse

A1 = It's total runs
B1 = It's total wins
C1 = It's total 2nd & 3rds
D1 = A percentage value - of wins in relation to runs
E1 = A percentage value - of 2nd & 3rds in relation to wins
F1 a percentage value of top 3 finishes

and so on for rows2 and 3

As my formula gives a zero in the blank cells above which I conditionally format to hide

can someone please provide a formula replicate the table above but with the following must have condition

WORKBOOK MUST BE SET TO HIDE ZEROES - I have only shaded cells for on screen purposes but will be printing out each worksheet in black and white on a 36 inch plotter and can't have unwanted zeroes it will be too hard to read the printout

in row 2 above cells A2:C2 are blank which means the horse has never ran before so it's corresponding percentage cells are blank, therefore at a glance I know its unraced

whereas in row 3 + 4 above the cells containing a zero tell me straight away at a glance it's ran under whatever the zero symbolizes and wasn't successful.

The key cell is in column A formula wise if the first cell in the column is blank the all cells across should be blank, if the cell contains 1 or bigger it should result in a zero being placed where relative

I've used all variations of formulae built around the relative percentage calculation but can't get it.

Thank you and please forgive my method of explanation I hope it is logical

 

[Scott T]

Try

=IF(your existing formula to pull the data here=0,"",your existing formula to pull the data here)

 

[JeniLynn]

You are awesome, it works thank you so much