Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help creating average formula
I have a spreadsheet that has 29 clients information for the year 2004,
broken down by month and they've asked me to average out collections, services provided etc. I need to have this reflect the average per month. I first tried totalling all columns and then put basic average formula like this in =average(d4:d541) and figured, but I'm not sure that using this formula has given me the average per month. Would this have given me the average per month for the entire year? Thanks in advance for your help |
#2
|
|||
|
|||
Deb,
To accomplish this you should use the subtotal feature. Make sure your data is sorted by month (you say broken down by month but I am not sure how) and then go to Data Subtotals and change the box that reads SUM to AVERAGE, and then select "At each change in COLLECTIONS (or whatever you have called that column. Hope this helps. "Deb" wrote: I have a spreadsheet that has 29 clients information for the year 2004, broken down by month and they've asked me to average out collections, services provided etc. I need to have this reflect the average per month. I first tried totalling all columns and then put basic average formula like this in =average(d4:d541) and figured, but I'm not sure that using this formula has given me the average per month. Would this have given me the average per month for the entire year? Thanks in advance for your help |
#3
|
|||
|
|||
hi Deb-try taking your data and putting it in a pivot table. Select the
average function and the table will list the average permonth/cust and avg of the year. pat "Deb" wrote: I have a spreadsheet that has 29 clients information for the year 2004, broken down by month and they've asked me to average out collections, services provided etc. I need to have this reflect the average per month. I first tried totalling all columns and then put basic average formula like this in =average(d4:d541) and figured, but I'm not sure that using this formula has given me the average per month. Would this have given me the average per month for the entire year? Thanks in advance for your help |
#4
|
|||
|
|||
what months/years/customers are in the range d4:d541?
"Deb" wrote: I have a spreadsheet that has 29 clients information for the year 2004, broken down by month and they've asked me to average out collections, services provided etc. I need to have this reflect the average per month. I first tried totalling all columns and then put basic average formula like this in =average(d4:d541) and figured, but I'm not sure that using this formula has given me the average per month. Would this have given me the average per month for the entire year? Thanks in advance for your help |
#5
|
|||
|
|||
the spreadsheet is setup this way:
Member Name Rep Month/Yr Services Collections client name name may-05 $11,000 $8,000 the information contiues down the page with all May-05 together and then continues on with June and so on, down the sheet. Sorry, should have posted a sample of what I'm working on to make it easier to understand. Also this report has many pivot reports attached to it and one I've created I need to add conditional formatting to that reflects collections that drop consecutively for 3 months in a row or longer and everytime I hit refresh the information I have to go back in and redo the formatting, is there a way to avoid doing this each week? the conditional formatting works like this 1st month drop yellow, second month, orange and 3rd on will be red. "bj" wrote: what months/years/customers are in the range d4:d541? "Deb" wrote: I have a spreadsheet that has 29 clients information for the year 2004, broken down by month and they've asked me to average out collections, services provided etc. I need to have this reflect the average per month. I first tried totalling all columns and then put basic average formula like this in =average(d4:d541) and figured, but I'm not sure that using this formula has given me the average per month. Would this have given me the average per month for the entire year? Thanks in advance for your help |
#6
|
|||
|
|||
thanks for your assistance, here is a sample of how this form looks
the spreadsheet is setup this way: Member Name Rep Month/Yr Services Collections client name name may-05 $11,000 $8,000 the information contiues down the page with all May-05 together and then continues on with June and so on, down the sheet. Sorry, should have posted a sample of what I'm working on to make it easier to understand. Also this report has many pivot reports attached to it and one I've created I need to add conditional formatting to that reflects collections that drop consecutively for 3 months in a row or longer and everytime I hit refresh the information I have to go back in and redo the formatting, is there a way to avoid doing this each week? the conditional formatting works like this 1st month drop yellow, second month, orange and 3rd on will be red. "Deb" wrote: I have a spreadsheet that has 29 clients information for the year 2004, broken down by month and they've asked me to average out collections, services provided etc. I need to have this reflect the average per month. I first tried totalling all columns and then put basic average formula like this in =average(d4:d541) and figured, but I'm not sure that using this formula has given me the average per month. Would this have given me the average per month for the entire year? Thanks in advance for your help |
#7
|
|||
|
|||
two comments
one on initial question the other on pivot table question I still may be misunderstanding what you want. sorry if I repeat what You say but I work best that way. You want the average per month for the different data. If you have the whole years data you can of course sum it up and divide by 12. if you have less than a years data you could figure out what to divide by say first info Jan 1 and last info sept 12 = total to date/ (8+12/30) would give year to date average. If this is not what you want please give more detail Pivot tables. Pivot tables will not keep formating during a refresh. (Many people would like this option.) A coommon workaround is to copy a formated pivot table and paste it as values and format in a another sheet. Whenever you refresh the pivot table, copy the text version and paste special format over the refreshed Pivot table. Note this is a time I would use a macro to do the paste since with my keyboard capabilities I have been known to paste special values when I meant to paste special formats. "Deb" wrote: the spreadsheet is setup this way: Member Name Rep Month/Yr Services Collections client name name may-05 $11,000 $8,000 the information contiues down the page with all May-05 together and then continues on with June and so on, down the sheet. Sorry, should have posted a sample of what I'm working on to make it easier to understand. Also this report has many pivot reports attached to it and one I've created I need to add conditional formatting to that reflects collections that drop consecutively for 3 months in a row or longer and everytime I hit refresh the information I have to go back in and redo the formatting, is there a way to avoid doing this each week? the conditional formatting works like this 1st month drop yellow, second month, orange and 3rd on will be red. "bj" wrote: what months/years/customers are in the range d4:d541? "Deb" wrote: I have a spreadsheet that has 29 clients information for the year 2004, broken down by month and they've asked me to average out collections, services provided etc. I need to have this reflect the average per month. I first tried totalling all columns and then put basic average formula like this in =average(d4:d541) and figured, but I'm not sure that using this formula has given me the average per month. Would this have given me the average per month for the entire year? Thanks in advance for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Named SUM Formula with relative refernce(s) | Excel Worksheet Functions | |||
Compounded formula using SUM & AVERAGE | Excel Worksheet Functions | |||
Creating a formula using absolute referencing - I think!! | Excel Worksheet Functions | |||
revert formula insertion to old method | Setting up and Configuration of Excel |