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]

Instead of returning zeros you could return a blank if the horse has never run.
Simply put your formula to calculate the % in the false argument of an if statement

=IF(ISBLANK(A1),"",B1/A1)

 

[FDibbins]

This may be your 1st error...

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

Excel works best if all data is collected on 1 worksheet, and then any summaries, extracts, analysis is done on other sheets

 

[Clueless Newcomer]

Hi Scott T and thank you for your reply.

It still doesn't seem to be working.

Worksheet set to not show a zero in cells that have zero value

so if I enter the following formula in D1 =IF(ISBLANK(A1),"",(100/(A1/B1))) and drag down I get a #DIV/0! error even if i adjust with IFERROR and include "" or "0" on the end

Anybody?

 

[Scott T]

If you get a #div/0! error then something is not right with your IFERROR
This should work

=IF(ISBLANK(A1),"",IFERROR(100/(A1/B1),""))

 

[Peter_SSs]

I hope it is logical

To me there seems to be a problem in that regard.

D1 = A percentage value - of wins in relation to runs
E1 = A percentage value - of 2nd & 3rds in relation to wins

Looking at your row 1

D1, wins in relation to runs: 5/20 = 25% as you have shown

Using the same logic, E1 would be 10/5 = 200% but your sheet shows 50%. 50% would be the percentage of wins in relation to 2nds & 3rds not the other way around.

If the correct result is 50 for E1 (ie 5/10 = 50%) then the zeros you have shown in E3 & E4 must be wrong because the calculations would involve division by 0.

So you need to consider what the correct values are for E1, E3 and E4 and why those are the correct values & let us know.

 

[Clueless Newcomer]

[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]
[TD]g[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]15[/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]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]33[/TD]
[TD]0[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Sorry I have just edited this to include the above correct table columns E to G are the percentage values, please adjust anything below to reflect this correct table




Hi Peter, many thanks for replying, apologies for my error, that should read RUNS not wins.

I want to divide by zero, that's the whole point and have a zero be placed in the resulting cell, despite my workbook/worksheets being set to not show zero values.

I am able to replicate the table above but only On my computer screen, as I have every cell in the worksheet colour filled with various colours to make it easier to read and make sense of, I then Conditionally Format certain zero value cells containing a zero the same colour as that the cell is filled with, so that the zero is no longer visible.

My worksheets are so large (even after running macros in each race worksheet to get rid of unused columns and rows) that they will only fit to a 36 inch wide plotter (which I have just bought for 2,500 euros) and not a standard printer, as I am printing a lot of worksheets daily/weekly, if I could afford it, I would just print exactly what's on the screen but I can't, as it would cost a fortune refilling coloured inks. So if I remove all Fill and all Conditional Formatting in order to print in black and white, all zeroes will be visible and print, making the printed result a minefield to read/study.

Here's what I do to achieve the Table as it appears above:

1.) Workbook/Worksheets set to show a zero in cells that have a zero value

2.) Every worksheet cell colour filled with various colours under different column headings

3.) Use this formula and obviously the cell refs change as it is dragged and placed in respective cells =IFERROR(100/(A1/B1),"0") so this gives me a zero when I try to divide by zero and not a #div0! error which is exactly what I want because zero is the percentage answer as in row 4 of my table above. A4 = Horse ran 6 times, B4 = Horse never won, C4 = Horse never 2nd or 3rd, D4 = % of wins against total runs which is zero% (perfect) and so for E4 and F4.
The Blank cells above are perfect also as at a glance if cell blank I know the horse has never ran and any cell with a number from 0 and above tells me 2 things at a glance, it has run and it's % result.


4.) Now to get rid (actually hide to the eye) the unwanted zeroes so columns A to C are conditionally formatted using "cells that only contain" a zero, where the zero font is the same colour as the cell fill. Columns D to F are Conditionally Formatted using a formula so E1 would be =A1=0

So that gives me exactly what I want on screen but zeros will return when printed unless I print in colour which I can't afford to do.

What I want:

I know I am now telling Excel now to hide zeroes and that seems to be the root of the problem

1.) Workbook/Worksheets MUST be set to Not Show a Zero in Zero Value Cells

2.) Formula must get round this in certain cases

4.) Incorporate my % formula in 3 above remembering it must get rid of the #DIV0! error

3.) In plain English : If Cell A1 is Blank then all Cells across should remain blank as a result and not be filled with a zero, If cell A1 contains a number from 1 and higher, then there is the base for a % calculation, even if the result is zero, that's the percent result so the cell should show a 0

With such a formula I can then print in black and white a perfect worksheet with blank cells and zeros where the result is 0% and not another single zero showing anywhere else.

Please somebody help, I am at the tearing my Hair(remaining) out stage.

Or can it not be done that a formula can't over ride a worksheet set to not show zeros?

Thanks so much.

 

[Clueless Newcomer]

Bump.

 

[Peter_SSs]

Firstly, in relation to your Bump: Posts #7 and 8 were made at approximately midnight and 5am my time. You need to remember that helpers can be anywhere in the world & do need to sleep, so please be patient.


Now to your problem, There are far too may words and too little concrete information for me to easily get a good handle on what you have and what you are trying to do. Remember that we are not familiar with your worksheet or requirements.
Try to keep any explanation or example simple & concise.

As best I can understand what you want, wouldn't these formulas copied down do what you want and you wouldn't need to have your worksheet set to 'Not Show Zero Values'? At least they seem to me to replicate the original table which is what you asked.

If this is not it, please explain which cells are incorrect, what the correct values should be for those cells and why they are the correct values.

Excel 2010 32 bit
	A	B	C	D	E	F
1	20	5	10	25	50	75
2
3	9	3		33.3	0	33.3
4	6			0	0	0
Zero Issue

Cell Formulas
Range	Formula
D1	=IF(A1="","",B1/A1*100)
E1	=IF(A1="","",C1/A1*100)
F1	=IF(A1="","",(B1+C1)/A1*100)

 

[JeniLynn]

I have a similar problem, I have a massive spreadsheet/workbook that connects 5 store locations and when they update one of their 4 sheets it updates the master. The date column on the store location sheets shows a blank cell (which is what I want) but the master shows 1/0/1900. How do I make the master show a blank cell.