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

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

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

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

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

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

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