Average, minimum and maximum values in MS Access 2003 if some columns are null

Excel 2007 can do it, but I cannot find a way in Access 2003 to do it: find the average, minimum and maximum values of multiple columns in a table if some of the columns' numeric values are blank/null for a given record.  Here is some sample data for five columns in three records:

   C1      C2      C3      C4      C5
   275        275                    275
   272        274                    272
   270        271                                          272

... where the values in Columns C3 and C4 are null/blank for these three records.

I also need to have other columns in Access to indicate the minimum and maximum values in C1 to C5 for each record, even with the nulls.

Currently, the values in this Access 2003 table are being queried into an Excel 2007 sheet, and I can do formulas like this in a separate column in Excel, for example, to highlight the MS Access C1 to C5 mismatches via Excel conditional formatting:


... where Acccess' C1 data is in Excel's Column A and Access' C5 data is in Excel's Column E.

But it REALLY slows down filtering and sorting in the Excel file (with 30,000+ records), so I'd like to have Access do these calculations and put them into columns in the table being queried by Excel.

I'll be on vacation until next Mon 16-Sep, so if you do not hear back from me immediately, that's why...

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

There is a function in Access called Nz(). It allows to replace the value for null to another value.

You can use together with MIN and MAX to get better values.

So, fox MAX you would use
MAX(Nz(C1,0), Nz(C2,0), Nz(C3,0), Nz(C4,0), Nz(C5,0))

Open in new window

For Min you need to replace the NULL values to some big number
MIN(Nz(C1,1234567890), Nz(C2,1234567890), Nz(C3,1234567890), Nz(C4,1234567890), Nz(C5,1234567890)) 

Open in new window

However, this trick will not work for AVE, as I believe with average you would want to omit the NULL values altogether.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upload a sample database with the table. Add test records including all possible variations of data, and list the required output.
It may need a function to do the job. The table needs a primary or unique key.
What maximum number records are you anticipating>?
Jeffrey CoachmanMIS LiasonCommented:
Yes, ...in some cases NZ() may return the incorrect results.

a Null is not considered in any mathematical calculations, where as a Zero will be.

So if you have:
...Then MIN() will be 5

If, on the other hand, you have:
...the min is the Zero

So as hnasr stated, sometimes it is best if you post a sample database and include the expected results.

curtwadeAuthor Commented:
The MS Access NZ function is the key to what I am looking for - thanks!  But trying a query formula like this in Access:

   MAX(Nz(C1,0), Nz(C2,0), Nz(C3,0), Nz(C4,0), Nz(C5,0))

... returns a 'The expression you entered has a function containing the wrong number of arguments' error.

I dug around and found the two MinOfList and MaxOfList functions at http://allenbrowne.com/func-09.html which does exactly what I need.

Thanks, folks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.