Replace symbols with numbers

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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;-
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,475
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,047
Messages
5,569,867
Members
412,298
Latest member
dietitiann
Top