Clueless Newcomer
New Member
- Joined
- Mar 7, 2017
- Messages
- 21
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.
<tbody>
</tbody>
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
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.
| A | B | C | D | E | F | |
| 1 | 20 | 5 | 10 | 25 | 50 | 75 |
| 2 | ||||||
| 3 | 9 | 3 | 33 | 0 | 33 | |
| 4 | 6 | 0 | 0 | 0 |
<tbody>
</tbody>
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
