Replacing zero with "" and still counting?

[Marvo]

Morning. I much prefer a blank cell to a zero for display purposes, however if you have a formula that refers to the blank cell it returns

## Value in formula is the wrong data type.

Is there a way round that or do I have to show the zero?

Many Thanks.

[AliGW]

Add a clause to the formula that converts a blank value to 0 when found.

What's the formula that refers to the cell?

[Marvo]

=IF($E3="H",IF($J3="W",1+AA2,0),AA2)

The cell in Question is AA2

[AliGW]

Try this:

=IF($E3="H",IF($J3="W",1+IF(AA2="",0,AA2),0),AA2)

[Marvo]

Thanks Ali, sadly its not doing what I'm after which was a workaround. Do you recall yesterday when we discussed formatting a column to show where the highest number was? That worked great until I added a further criteria. So if 12 is the highest number, because the next row doesn't come under the criteria the highest number is repeated (another 12) which now gives me a false result.

Basically 12 + 0 = 12. Hope that makes sense.

[AliGW]

Look, Marvo, as we have said before, we can only work with what you give us. Do you have any idea how many people we help in a day? Are you really expecting us to remember previous threads of yours specifically? My response is to the simple question you posed in this thread.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Marvo]

On the attached workbook, please refer to column G.

It is looking for consecutives.

In G5407 it gives you the highest total (the club record) which is 16.

What I require in G5408 is how many instances of this. The result given is 6 but it should only be 1.

So maybe I need to use a different formula in G5408.

Thank you.

[Glenn Kennedy]

There are 6. Rows 92-97, to be exact.

[AliGW]

This has morphed into a completely different question!

Explain why that formula should return only 1 when there are clearly 6 instances of the maximum of 16.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1		Competition		F	A		GAH
92	H	League	W	3	1		16
93	A	League	L	0	1		16
94	A	FA Cup	W	2	0		16
95	A	League	W	3	0		16
96	A	FA Cup	D	0	0		16
97	A	League	L	0	2		16
5407							16

Sheet: ALL

[Marvo]

Its the same instance. Its the same 16 games being added up. The one true figure you can see is in G92. G93 to G97 doesn't fit the criteria as they are AWAY games (Column A).

The club record is 16 and it has only happened once. I had to cut the workbook down to get it on here but if you saw the other rows you'd maybe understand.

Its not a massive problem in this column but in others where there are more instances it becomes a real problem.

[AliGW]

Maybe we would. Trouble is, my remote viewing skills are minimal.

Sorry, Marvo - I'm good, but I'm not a mind reader, and it's a hell of a struggle to get you to properly articulate what you really want. I'm out for today. Good luck with it!

[Glenn Kennedy]

So if we are to believe that 6 = 1 then the problem is with the formula in the rest of column G. Explain what it is EXPECTED to do. It obviously should NOT return a 6 in rows 93-97 as there is no other way of distinguishing the first 6 from the subsequent ones...

[Pete_UK]

Your original question was about showing a zero as a blank, and you can do that by applying a Custom Format to the cell(s) of:

General;-General;;

Note the two semicolons at the end.

The cell will still contain a zero, and so it can be used in formulae with addition etc., but it will show as empty.

Hope this helps.

Pete

Hope this helps.

[Glenn Kennedy]

I have added another instances of a string of "16"s. I do not know if it is correct or not, but it gives another run of 16s.

=SUMPRODUCT(($G$1:$G$5403<>$G$5407)*($G$2:$G$5404=$G$5407))

[Marvo]

So if we are to believe that 6 = 1 then the problem is with the formula in the rest of column G. Explain what it is EXPECTED to do. It obviously should NOT return a 6 in rows 93-97 as there is no other way of distinguishing the first 6 from the subsequent ones...

It's football results Glenn. I'm looking at club records, in particular consecutive wins, draws, losses etc. It works fine when you are looking at ALL games (rows) but fails when you add a criteria that means some rows are excluded. Being at HOME for example.

I think I need a completely new formula, maybe coming at the same question but from a different angle?

If I could have made the result in the rows that weren't required blank that would have solved it but that doesn't look possible.

[Marvo]

Your original question was about showing a zero as a blank, and you can do that by applying a Custom Format to the cell(s) of:
...

Thanks Pete but I actually require the zero to be ignored in the formula.

[Glenn Kennedy]

see post 14.

[Marvo]

That works Glenn and I've tested it on other rows with bigger problems and it give the correct result there too.

I have one other question but I'll post a new thread.

Many thanks, I guessed I'd needed a completely different formula. I need to read up on SUMPRODUCT.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[AliGW]

Please mark this as SOLVED and make sure the title of your new thread actually says what you need help with - explicitly.

[Marvo]

Already have. In future I'll mark it as SOLVED before I reply to the poster thanking them.

[AliGW]

Thanks - the order doesn't matter.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.