Math and Science Solutions for Businesses/decimal chart


QUESTION: I don't know if you can answer this question about a chart of calling times which I have attached. The decimals represent time, e.g. 5.5 = 5.30am, etc. In the boxes at bottom I have inserted the formula for "average" and it works as far as the bottoms of columns A-D. Column E, bottom, is the average for columns A-D, and Column E, next to bottom is the average for column E. But here's the problem: when I insert a number into column E, it doesn't affect the bottom right (bolded) column. How do I get it so that all the bottom boxes each reflect the average for that column, and to make a separate box somehow that reflects the average for all columns. So for instance if I insert a number into the "Friday" column, the average in the E bottom box will reflect it? Thanks!

total average
total average  
col average
col average  
ANSWER: I assume you are using Excel or some similar spreadsheet program. As shown in the attached images, I have used the AVERAGE function  to calculate the 5 column averages and the overall average by choosing the correct cells for each.

In the first screen shot image, I show the formula for the average in col 5 (fri); a bold line surrounds the cells F4 to F5, which contain the numbers you want to average for that column. It is important to note that, for this and the other columns, the cells selected contain all the cells in the column, including the empty ones. For instance, for col 4 (thurs), the cells selected are E4 to E15. Excel knows to ignore empty cells and not treat them as though they were 0 (which would throw off the average). With these cells selected in the AVERAGE formula, you could add a time in one of the empty cells and it would be properly included in the column average.

The 2nd image shows a screen shot with the total average formula. Note that it selects the 5 cells in row 17 which correspond to the column averages.

Hope this makes sense.


---------- FOLLOW-UP ----------

QUESTION: I tried doing your suggestion. See attachment. In the corner box at bottom right (which is blank on the attached jpg), I inserted the formula Average for A19-E19. I'm using a iMac which provides "Pages" app with a table with adjustable columns and rows and the ability to insert formulae. A triangular warning sign (which doesn't appear on the attached jpg but does appear on my "pages" doc) says "A19-E19 isn't a valid reference."


I didn't get the attachment. I am also not familiar with the "Pages" app. Perhaps if you give me a screen shot of the Pages window I could help.

How do you do your averages? Do you use a finction or by hand? If you can get your app to average the columns properly, then it should be able to do the column averages as well. Are any of the cells in A19-E19 non-numeric?


Math and Science Solutions for Businesses

All Answers

Answers by Expert:

Ask Experts


Randy Patton


Questions regarding application of mathematical techniques and knowledge of physics and engineering principles to product and services design, optimization, prediction, feasibility and implementation. Examples include sales and product performance projections based on math/physics models in addition to standard regression; practical and cost effective sensor design and component configuration; optimal resource allocation using common tools (eg., MS Office); advanced data analysis techniques and implementation; simulation and "what if" analysis; and innovative applications of remote sensing.


26 years as professional physical scientist and project manager for elite research company providing academic quality basic and applied research for government and defense industry clients (currently retired). Projects I have been involved in include: - Notional sensor performance predictions for detecting underwater phenomena - Designing and testing guidance algorithms for multi-component system - Statistical analysis of ship tracking data and development of anomaly detector - Deployed vibration sensors in Arctic ice floes; analysis of data - Developed and tested ocean optical instrument to measure particles - Field testing of protoype sonar system - Analysis of synthetic aperture radar system data for ocean surface measurements - Redesigned dust shelters for greeters at Burning Man Festival Project management with responsibility for allocation and monitoriing of staff and equipment resources.

“A Numerical Model for Low-Frequency Equatorial Dynamics” (with Mark A. Cane), J. of Phys. Oceanogr., 14, No. 12, pp. 18531863, December 1984.

MIT, MS Physical Oceanography, 1981 UC Berkeley, BS Applied Math, 1976

Past/Present Clients
Am also an Expert in Advanced Math and Oceanography

©2017 All rights reserved.