Spreadsheet is created for a layover from other sources, therefore,
zeros will be present when data is laid on top of master spreadsheet.
Also, want to show no values by using a "-" when no numbers present
for easy recognition. Current formula is =IF(SUM(D9:GU9)=0,"-",
(AVERAGE(D9:GU9))) but if two numbers and a zero are present it
averages by three. Can anyone HELP?????

Can there be any negative numbers in your range?

Assuming not, try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(SUM(D9:GU9),AVERAGE(IF(D9:GU90,D9:GU9)),"-")

Biff

wrote in message
ps.com...
Spreadsheet is created for a layover from other sources, therefore,
zeros will be present when data is laid on top of master spreadsheet.
Also, want to show no values by using a "-" when no numbers present
for easy recognition. Current formula is =IF(SUM(D9:GU9)=0,"-",
(AVERAGE(D9:GU9))) but if two numbers and a zero are present it
averages by three. Can anyone HELP?????

Hi

You could replace the (AVERAGE(D9:GU9) bit with
=SUM(D9:GU9)/COUNTIF(D9:GU9,"0")

HTH
Martin