Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need Averaging Help With Workbook Please! - DailyMoodTool.xls (1/1)
I am doing a workbook for my patients. I have attached it here for
anyone that cares to look at it. I have a series of symptoms down the left hand side and a date range across the top. I have averaged response by symptom by the days in the month the patient places a numerical value in the cell, and averaged all symptoms responded to with a numerical value by day. What is stumping me is how to average all of the daily averages. What I want to end up with is a running average by symptom, a daily average of all symptoms, and a monthly average of all daily averages. What is the formula. Notice the ORANGE box. This is where I want the last calculation to go. I have tried =AVERAGE(B35:AF35), and =AVERAGE(AG5:AG34) with no luck. It does place the function in the cell, but when I start inputing numerical values in the cells, it auto averages symptom by month, and all symptoms by day, but nothing appears in the cell for the monthly average except the ## sign for the function! I understand that Excel auto rounds out based on the width of the cell, so I opened up the cell wider to see if that is the problem, but it is not. Does this require a different formula, because I am averaging a series of averages. I thought I could just choose a range of any cells and it would average it??? Can anyone help. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need Averaging Help With Workbook Please! - DailyMoodTool.xls (1/1)
Hi
The formula in cell AG5 needs to be =IF(COUNT$(B5:$AF5)=0,"",AVERAGE($B5:$AF5)) the formula in B35 needs to be =IF(COUNT($B5:B$34)=0,"",AVERAGE(B$5:B$34)) -- Regards Roger Govier wrote in message ... I am doing a workbook for my patients. I have attached it here for anyone that cares to look at it. I have a series of symptoms down the left hand side and a date range across the top. I have averaged response by symptom by the days in the month the patient places a numerical value in the cell, and averaged all symptoms responded to with a numerical value by day. What is stumping me is how to average all of the daily averages. What I want to end up with is a running average by symptom, a daily average of all symptoms, and a monthly average of all daily averages. What is the formula. Notice the ORANGE box. This is where I want the last calculation to go. I have tried =AVERAGE(B35:AF35), and =AVERAGE(AG5:AG34) with no luck. It does place the function in the cell, but when I start inputing numerical values in the cells, it auto averages symptom by month, and all symptoms by day, but nothing appears in the cell for the monthly average except the ## sign for the function! I understand that Excel auto rounds out based on the width of the cell, so I opened up the cell wider to see if that is the problem, but it is not. Does this require a different formula, because I am averaging a series of averages. I thought I could just choose a range of any cells and it would average it??? Can anyone help. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need Averaging Help With Workbook Please! - DailyMoodTool.xls (1/1)
In article ,
"Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi The formula in cell AG5 needs to be =IF(COUNT$(B5:$AF5)=0,"",AVERAGE($B5:$AF5)) the formula in B35 needs to be =IF(COUNT($B5:B$34)=0,"",AVERAGE(B$5:B$34)) -- Regards Roger Govier wrote in message ... I am doing a workbook for my patients. I have attached it here for anyone that cares to look at it. I have a series of symptoms down the left hand side and a date range across the top. I have averaged response by symptom by the days in the month the patient places a numerical value in the cell, and averaged all symptoms responded to with a numerical value by day. What is stumping me is how to average all of the daily averages. What I want to end up with is a running average by symptom, a daily average of all symptoms, and a monthly average of all daily averages. What is the formula. Notice the ORANGE box. This is where I want the last calculation to go. I have tried =AVERAGE(B35:AF35), and =AVERAGE(AG5:AG34) with no luck. It does place the function in the cell, but when I start inputing numerical values in the cells, it auto averages symptom by month, and all symptoms by day, but nothing appears in the cell for the monthly average except the ## sign for the function! I understand that Excel auto rounds out based on the width of the cell, so I opened up the cell wider to see if that is the problem, but it is not. Does this require a different formula, because I am averaging a series of averages. I thought I could just choose a range of any cells and it would average it??? Can anyone help. It did not work? I got an error message? Why can't I just do: =AVERAGE(AG5:AG34)? Would that not average the columns. It does display the function in the cell. It just does not compute when I enter number into the cells? I am confused :) |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need Averaging Help With Workbook Please! - DailyMoodTool.xls (1/1)
Hi
My apologies. Those fingers were not typing what I was thinking<g The formulae should have read In AG5 =IF(COUNT($B5:$AF5)=0,"",AVERAGE($B5:$AF5)) In B34 =IF(COUNT(B$5:B$34)=0,"",AVERAGE(B$5:B$34)) The problem is that when there is no data, you get a #DIV/0 error, which is what you will see if you widen any of your columns where the error is showing. The COUNT function is eliminating this error, by placing a null in the cell if there are no values to average. If you try to use =AVERAGE(AG5:AG34) when there are #DIV/0 errors in the range AG5:AG34, then that will also return a #DIV/0 error, or again if there are no entries in the range you will get an error. Once you have error trapped the other entries, then you could enter in AG36 =IF(COUNT(AG5:AG34)=0,"",AVERAGE(AG5:AG34)) Be aware though that the value obtained from that calculation could be different to the value obtained if you went the other way =IF(COUNT(B35:AF35)=0,"",AVERAGE(B35:AF35)) The true overall average would be =IF(COUNT(B5:AF34)=0,"",AVERAGE(B5:AF34)) -- Regards Roger Govier wrote in message ... In article , "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi The formula in cell AG5 needs to be =IF(COUNT$(B5:$AF5)=0,"",AVERAGE($B5:$AF5)) the formula in B35 needs to be =IF(COUNT($B5:B$34)=0,"",AVERAGE(B$5:B$34)) -- Regards Roger Govier wrote in message ... I am doing a workbook for my patients. I have attached it here for anyone that cares to look at it. I have a series of symptoms down the left hand side and a date range across the top. I have averaged response by symptom by the days in the month the patient places a numerical value in the cell, and averaged all symptoms responded to with a numerical value by day. What is stumping me is how to average all of the daily averages. What I want to end up with is a running average by symptom, a daily average of all symptoms, and a monthly average of all daily averages. What is the formula. Notice the ORANGE box. This is where I want the last calculation to go. I have tried =AVERAGE(B35:AF35), and =AVERAGE(AG5:AG34) with no luck. It does place the function in the cell, but when I start inputing numerical values in the cells, it auto averages symptom by month, and all symptoms by day, but nothing appears in the cell for the monthly average except the ## sign for the function! I understand that Excel auto rounds out based on the width of the cell, so I opened up the cell wider to see if that is the problem, but it is not. Does this require a different formula, because I am averaging a series of averages. I thought I could just choose a range of any cells and it would average it??? Can anyone help. It did not work? I got an error message? Why can't I just do: =AVERAGE(AG5:AG34)? Would that not average the columns. It does display the function in the cell. It just does not compute when I enter number into the cells? I am confused :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorry Uploaded The Wrong File :) Here is the right one!! - DailyMoodTool.xls (1/1) | Excel Discussion (Misc queries) | |||
averaging | Excel Discussion (Misc queries) | |||
Averaging Cells from another workbook | Excel Worksheet Functions | |||
Help with averaging... | Excel Discussion (Misc queries) | |||
More Averaging | Excel Worksheet Functions |