Formula needs to check if it equates to zero and return a blank cell

[john dalton]

Hi I have a formula (see below) that looks at a clolumn of information an returns a value if one is present if not then the cell needs to remain blank, currently the formula returns a 0 if blank, is there any way of stopping this or just by implementing a If(Formula=0,"") situation.

=IFERROR(IF($C212="","",INDEX('Course Data Entry'!$X$17:$X$538,MATCH(1,INDEX(('Course Data Entry'!$Z$17:$Z$538=$E212)*('Course Data Entry'!$AF$17:$AF$538=$C212),0),0))),"")


Thanks in advance

JD

[Chris 53]

There are 2 zero's near the end of your formula, replace these with double quotes "".

[Pete_UK]

Hi John,

what kind of values do they return? I can see they are numbers, but are they integers, or can they be fractional?

You can hide zeros by using a custom format on the cells like this:

General;General;;

but if you specifically want, say, 2 decimal places shown you need to use:

0.00:-0.00;;

Hope this helps.

Pete

[john dalton]

If I do that it actually gets rid of the result wether its a 0 or not.

[Pete_UK]

There are 2 zero's near the end of your formula, replace these with double quotes "".

No, those zeros are needed for the two INDEX functions - a zero is returned by the formula if the cell that it identifies is either blank or contains a zero itself.

Pete

[john dalton]

There three types of this formula, one returns a date, the second returns a time and the third returns a day.

The date just shows up as 01/00/1900 (a zero), the time shows up as 12:00 Am (Again a zero) and the day just defaults to Saturday.

[Pete_UK]

Okay, so they are all positive. You can use these custom formats as appropriate:

dd/mm/yyyy;;;

hh:mm;;;

dddd;;;

The important thing is the semicolons after the format string. Custom Formats have 4 parts - one for positive, one for negative, one for zero values and the other for Text, and each is separated by a semicolon. By having nothing between those semicolons, this causes nothing to be displayed.

So, click on a cell which has a date format, right-click and choose Format Cells, then on the Number tab you will have Date selected. Scroll down and select Custom, and the previous format will already be shown in the dialogue box. Just add three semicolons to the end, then click OK. Similar for the other data types.

Hope this helps.

Pete

[john dalton]

Pity i cant swear on here LOL coz that was a ******* great answer Pete, thanks for bailing me out again pal.

Much appreciated.

[Pete_UK]

I wonder what those seven asterisks can be ???

Glad to be able to help - you know what to do about marking the thread as Solved, and clicking the star etc.

Pete