Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A problem with #DIV/0! and circular reference errors
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, -- Wal |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular Reference Problem Calculating Net Pay | Excel Worksheet Functions | |||
Simple Circular Reference Problem | Excel Worksheet Functions | |||
Array, circular reference problem | Excel Worksheet Functions | |||
Problem with Circular Reference | Excel Discussion (Misc queries) | |||
Circular reference problem | Excel Discussion (Misc queries) |