Replace symbols with numbers

[SilentPirate007]

Hello,

I'm busy with a project and in excel I am trying to generate averages. But for the number "0", I need it to be replaced with "-" instead, but when I generate an average it disregards it. How could I get "-" to represent "0"?

This is a link to an example:
http://i893.photobucket.com/albums/ac137/SilentPirate007/excelproject.jpg

Thanks

 

[VoG]

If you format your cells as Custom

0.0;-0.0;-

then zeros will be displayed as - but the averages will calculate correctly.

If you use , as the decimal separator then I think you would need

0,0;-0,0;-

 

[SilentPirate007]

Thank you very much ... Works like a charm!

 

[jim may]

You're going to have to use:

=AVERAGE(IF(A1:A10,A1:A10)) << Entered with Ctrl+Shift+Enter Keys. Not just the Enter Key (Yhe formula bar will display the results as:
{=AVERAGE(IF(A1:A10,A1:A10))} You cannot enter the { }'s using the keyboard.

Formatting isn't going the change/alter the actual result value of the cell formula