Average price between dates

[PaulinaDi]

Could someone help me with the following?
I still do not understand all the steps. Basically the last one of the
second option.

........
....You now have a summary report for average per date. So you're not quite
done.
Right click on the Date header in the pivottable.
Choose Group and show detail.
Then choose Group.
And group by months and years.

_______________________________

You have at least a couple of choices.

One way is to make sure you have headers and then add another helper column:
=text(b7,"yyyymm")
(with my USA settings and my English function and yyyymm abbreviations!)

Then sort your data (all the columns!) by this helper column.

Then use Data|Subtotal (xl2003 menus). Be sure to use Average as the subtotal
function.

========
Another way is to use data|pivottable (also xl2003 menus).

Add those headers if you don't have them, but don't bother sorting the data
(it's not required).

Then select the range
Data|Pivottable
Follow the wizard until you get to a step with a Layout Button on it.
Click that layout button
Drag the header for the date to the row area
Drag the header for the price to the Data area
(rightclick on it and choose Average)
and finish up the wizard.

You now have a summary report for average per date. So you're not quite done.
Right click on the Date header in the pivottable.
Choose Group and show detail.
Then choose Group.
And group by months and years.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

PaulinaDi wrote:

I would like to get the average number on a specific range.
I have B7:B172 with dates (from 01/01/08 to 25/08/08) sorted descending. I
have D7172 with cooper prices for these days.
I would like to get the average price per month.
Be aware that I use spanish dates so I use dd/mm/yyyy.

 

[Dave Peterson]

What's the specific question?

Maybe Debra Dalgleish's site will help:
http://contextures.com/xlPivot07.html

 

[Sean Timmons]

If you want to do without pivots...

assuming your taqble will be in A2 through, say, A6,

Enter the Dates you want to separate by (i.e. - 1/10/08, 1/11/08, etc.) down
your rows.

Then, in your first cell, enter:

=SUMPRODUCT(--(B7:B172>=A2),--(B7:B172<A3),(D7172))/SUMPRODUCT(--(B7:B172>=A2),--(B7:B172<A3))

Change A2 and A3 to your atual first date and 2nd date cells.

Hope this helps!

 

[PaulinaDi]

This worked excellent. Just to finish this formula, what can I do if I have
no values
for October, November and December (as they haven´t already passed) and I
want 2 things, not to get the #DIV/0! result displayed but a blank cell with
the formula just in there and I want the graphic not to take this value as a
0. I learned something about using NA() but when I try using this in the
formula I don´t get anything good.

 

[Sean Timmons]

=IF(SUMPRODUCT(--(B7:B172>=A2),--(B7:B172<A3)))=0,"",SUMPRODUCT(--(B7:B172>=A2),--(B7:B172<A3),(D7172))/SUMPRODUCT(--(B7:B172>=A2),--(B7:B172<A3)))

If DIV/0, this will verify denominator is not 0. If it is, will display
nothing.

You could alternately use ISERROR() around the entire formula to capture all
errors...

 

[PaulinaDi]

Excellent. I got a quite big formula but it works really very well.

But now (and I'll get you crazy with all this situation, sorry) if I
substitute the "" for a NA() I get the graphic with no data where I haven´t
captured it (EXCELLENT) that is what I really want, but I get the #NA data on
my table or I may leave the "" and I get the table with blank spaces but the
graphic with lines in 0.

Am I not able to have both, the graphic with the formula but not the #NA
data on it and the graphic with no line on those months I have no info
(still)? or may I just be able to have the table with a blank space when info
haven´t been captured on my other table and having the graphic with just the
lines on those months filled in?

I hope you understand me because I'm getting crazy with this situation.
UPS!!!!!!!

 

[Peo Sjoblom]

Use conditional formatting in the cells that display #N/A

formula is

then use

=ISNA(1)

click the format button and select a white font

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

ISNA(A1)

where A1 is the cell with the #N/A

--


Regards,


Peo Sjoblom

 

[PaulinaDi]

Thanks a lot Sean. With Peo's answer I could complete this task. I really
appreciate your help.

 

[PaulinaDi]

WOW! This was just too simple but so helpful. Thanks a lot.
I'll also take note of this useful tool for the future.
Thanks again!!!!!!! =)