Excel "for each"

[mcquam]

I have a column of dates and a column of numbers that relate to an average number of days. I don't want to use a pivot table but I need to average the number column for each month in the date column.

 

[ajrimmer]

Insert a column to calculate the month number eg = Month(A1) & copy down.

Sort the table by month. Then insert a subtotal to calculate the average for each month.

You'll need a header row for this to work.

Doubtless somebody will be along with something slicker.

 

[xlbo]

I don't want to use a pivot table

why? it's by far the easiest way to do it...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mcquam]

I can't use as pivot table as i need to perform a further calculation on my "average days" data column. I need to remove the top 20% first. So i need to have 2 figures, one for the average of 100% and another average excluding the top 20%. This means I will need to either sort or do some kind of lookup or foreach.

 

[xlbo]

yup - do a pivot table, copy & paste values then you simply have a list of months and average figures...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mcquam]

Worth saying again, I don't want to use a pivot table.

 

[xlbo]

yes but you havn't given an adequate explanation as to why. you say you want to be able to work with the dat afterwards - well you can do that by copy / pasting as values. After that, all you have is a list of data.

Illogical restrictions like that make me wonder if this is a homework problem...

If you don't want to use a pivot table then you will need to manually generate a list of months (you don't say whether year is a factor) and then use a SUMIF / COUNTIF type formula for each month to obtain the average. Quite a lot more work than just slapping a pivot table over the top of the data.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mcquam]

My report is based on a query using MS Query of a MS Access database. I would use a pivot table if I just needed the 100% average but I need to quote 2 separate average figures (100% and 80%). Even if it was a simple 80% of the total I would do it like that but I need to perform two separate calculations. The report needs to run automatically so I can't copy/paste. I have one shot at the calculation and it seems the best way is to use a SUMIF/COUNTIF as you say. But what formula?

 

[xlbo]

So how are you going to dynamically generate a list of months against which to put your formula?

SUMIF(MonthNames,"Jan",Values)/COUNTIF(MonthNames,"Jan")

is your basic concept

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

You could use MS Query to generate the list of dates (months, but use REAL DATES).

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[mcquam]

I think I can use percentrank for the calculation and I can easily get the dates but it's the selection based on the same month I can't figure out. What I want to say is "for each cell that contains the same month value as any other cell, do x".

 

[xlbo]

That's what my formula outline gives you - as per your 1st post:

I need to average the number column for each month in the date column

Just swap "MonthNames for the range where you have your list of months and "Values" for the range where you have your set of values. If the months are in A1:A100 and Values in B1:B100 then

SUMIF(A1:A100,"Jan",B1:B100)/COUNTIF(A1:A100,"Jan")

F1 is your friend

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mcquam]

I'm not sure I'm following you. I tried it and got a div/0 error. I also tried seeting up a named range of months. Do I need to do this formula over 12 columns - 1 for each month?

 

[xlbo]

ok - you have 2 columns: Dates & Values

You actually have 2 options

1: Add another column which holds the month number =MONTH(A2)

Then use =SUMIF(C1:C100,1,B1:B100)/COUNTIF(C1:C100,1)

You could also enter values 1-12 in say cells D112 and use

=SUMIF(C1:C100,D1,B1:B100)/COUNTIF(C1:C100,D1)

2: USe a SUMPRODUCT formula - no extra data needed - again, you can either hard code the months or use cell references but

=SUMPRODUCT((Month(A1:A100)=1)*(B1:B100))/SUMPRODUCT((Month(A1:A100)=1)*(1))

What happens when you go over a year end though? is there any need to split out years?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ajrimmer]

You might also want to include a check that the countif is greater than 0 to get round the div0 error:

if(COUNTIF(C1:C100,1)>0,SUMIF(C1:C100,1,B1:B100)/COUNTIF(C1:C100,1),"")
or
if(COUNTIF(C1:C100,1)>0,SUMIF(C1:C100,1,B1:B100)/COUNTIF(C1:C100,1),0)

Depending on your preference

 

[lionelhill]

There is a related thing that I've used, on similar problems (and it's very simple).

This works ONLY if your rows are all in date order (often true if it's a running list of things that happened)

Column A = date
Column B = value

Make Column C = month (as above, or by text manipulations, your choice)

Cell D2 contains the following formula
=if(C2<>C1, C2, D1+C2)
Copy this cell down as far as desired
This makes a running total for each month. If a new month starts, a new value is chosen. If it's just a new entry for the same month, the value is added to the previous running total. You'll need to sort out D1 if you want to deal with the general case where line 1 might be a month of its own.

If you want an average, do something similar in E2
=if(C2<>C1, 1, E1+1)
This creates a running count of how many entries there are so far for this month

A new column F, dividing D by E, will give you a running average. I suppose you could even use yet another "if" to show the result only when a month ends.