Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
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/assistan...lconPT101.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 D7:D172 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
What's the specific question?
Maybe Debra Dalgleish's site will help: http://contextures.com/xlPivot07.html PaulinaDi wrote: 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/assistan...lconPT101.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 D7:D172 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
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),(D7:D172))/SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)) Change A2 and A3 to your atual first date and 2nd date cells. Hope this helps! "PaulinaDi" wrote: 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/assistan...lconPT101.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 D7:D172 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
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" wrote: 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),(D7:D172))/SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)) Change A2 and A3 to your atual first date and 2nd date cells. Hope this helps! "PaulinaDi" wrote: 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/assistan...lconPT101.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 D7:D172 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
=IF(SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)))=0,"",SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3),(D7:D172))/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" wrote: 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" wrote: 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),(D7:D172))/SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)) Change A2 and A3 to your atual first date and 2nd date cells. Hope this helps! "PaulinaDi" wrote: 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/assistan...lconPT101.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 D7:D172 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
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!!!!!!! "Sean Timmons" wrote: =IF(SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)))=0,"",SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3),(D7:D172))/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" wrote: 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" wrote: 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),(D7:D172))/SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)) Change A2 and A3 to your atual first date and 2nd date cells. Hope this helps! "PaulinaDi" wrote: 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/assistan...lconPT101.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 D7:D172 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
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 "PaulinaDi" wrote in message ... 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!!!!!!! "Sean Timmons" wrote: =IF(SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)))=0,"",SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3),(D7:D172))/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" wrote: 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" wrote: 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),(D7:D172))/SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)) Change A2 and A3 to your atual first date and 2nd date cells. Hope this helps! "PaulinaDi" wrote: 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/assistan...lconPT101.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 D7:D172 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
ISNA(A1)
where A1 is the cell with the #N/A -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... 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 "PaulinaDi" wrote in message ... 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!!!!!!! "Sean Timmons" wrote: =IF(SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)))=0,"",SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3),(D7:D172))/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" wrote: 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" wrote: 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),(D7:D172))/SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)) Change A2 and A3 to your atual first date and 2nd date cells. Hope this helps! "PaulinaDi" wrote: 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/assistan...lconPT101.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 D7:D172 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
Thanks a lot Sean. With Peo's answer I could complete this task. I really
appreciate your help. "Sean Timmons" wrote: =IF(SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)))=0,"",SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3),(D7:D172))/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" wrote: 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" wrote: 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),(D7:D172))/SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)) Change A2 and A3 to your atual first date and 2nd date cells. Hope this helps! "PaulinaDi" wrote: 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/assistan...lconPT101.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 D7:D172 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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average price between dates
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!!!!!!! =) "Peo Sjoblom" wrote: ISNA(A1) where A1 is the cell with the #N/A -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... 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 "PaulinaDi" wrote in message ... 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!!!!!!! "Sean Timmons" wrote: =IF(SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)))=0,"",SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3),(D7:D172))/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" wrote: 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" wrote: 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),(D7:D172))/SUMPRODUCT(--(B7:B172=A2),--(B7:B172<A3)) Change A2 and A3 to your atual first date and 2nd date cells. Hope this helps! "PaulinaDi" wrote: 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/assistan...lconPT101.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 D7:D172 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average price between dates | Excel Worksheet Functions | |||
average price | Excel Worksheet Functions | |||
calculate/convert volume price to monthly average price | Excel Worksheet Functions | |||
How do I get the average price per bid for an individual month? | Excel Discussion (Misc queries) | |||
Calculating Net Position and Average Price | Excel Worksheet Functions |