A problem with #DIV/0! and circular reference errors

[Wally3178]

Hi,

Please forgive this long winded request but I am going to try and make this
problem as clear as I possibly can.

I am writing a spreadsheet to assist me in my hobby as an amateur
meteorologist and also to try and teach myself how to use Excel. Everything
is going well with a couple of exceptions.

I have formatted the sheet into the twelve months from columns D to AA with
two columns per month for Max/Min temps and the appropriate number of days
for each month down to rows 40-42. Each cell is conditionally formatted to
turn red or blue if the data entered exceeds limits placed on it.

At the bottom each column, I have three rows showing the maximum temperature
for the month, the minimum for the month and the average for the month; its
the average row that I am having a problem with. Until data is entered into
at least one cell for the month, the average shown for each month is #DIV/0!
I have tried the formula =IF(F11:F40,0,"",AVERAGE(F11:F40)) but that only
changes the error to a circular reference error, an error that I just can't
see or understand.

My second problem relates to the formula I have for showing the lowest
temperature for the year which automatically updates day by day. Even
though I have low temperatures showing at the foot of monthly columns, the
yearly low is showing as zero, I think because some columns do not have any
data as yet, the formula of =MIN(D49:AA49) is reading blank cells as '0'

I think the basic problem is my not being able to have empty cells display
as exacty that, empty cells; they are displaying as 0. Can anyone put me on
the right path to a solution?

I thank you all for taking the time to read this.

Cheers,

 

[carlo]

Dear Wal

Don't be sorry, i prefer a long text where everything the user knows
is explained than asking 12 times to be able to respond.

To your first problem.

If you have no data, Average will always give you DIV/0, this should
work though:
=IF(COUNT(F11:F40)=0,"",AVERAGE(F11:F40))

Concerning your other formula-problem, have a look at this site:
http://www.ozgrid.com/Excel/minimum-without-zero.htm

One advice: Try to search google before posting, there are lots of
sources for excel formulas out there which can help you. Sometimes you
don't know what to search for or you can't find it, that's when the
forum comes in handy.

hth

Carlo

 

[Wally3178]

Thanks Carlo, the formula worked fine and I got some useful information from
the link; your blood is worth bottling, thanks again.

Cheers

 

[carlo]

Thanks Carlo, the formula worked fine and I got some useful information from
the link; your blood is worth bottling, thanks again.

Cheers
--
Wal










- Show quoted text -

You're welcome and thanks for the feedback.

Carlo