Highlighting best month in a trend chart

[greykitten]

Hi,

A tutorial on how to highlight the best month in a trend chart was recently sent and I have some questions on how I would apply this to my work. For my type of analysis, the lowest monthly values are the ones I would need to highlight in the chart. Looking at the downloaded example provided I have several questions on the actual formulas.


1) For cell AB3 showing $15,737, there is this formula =MAX(myData3[Month Total]). Where is the "myData3" coming from? Is it a named range?


2) On cell AC7 for Best Month, what action is the formula =IF([@[Month Num]]=bestMonth,[@Value],NA()) performing for that cell? Where in the file was the named range created so I can reproduce it?


3) Since I only want to use monthly data for several years' worth of results, which header columns on (V6:AE6) would I need to keep to do this?


4) How is the monthly portion in the example being formatted to blue? Just manually?


5) On cell AE3, what is formula =INDEX(myData3[Date],MATCH(bestMonth,myData3[Month Num],0)) saying?


Here is a link to the file [URL=https://hotfile.com/dl/183935369/212b43d/How_To_Highlight_Best_Week__Month.xls.htm


Thank you for your help,

greykitten

 

[Montrey]

Let me simplify it for you.

All it is, is another range which only displays the values meeting your criteria. All other values are hidden using #n/a.

You just add this range to your graph, select a color for it, and it will overlap the original range. Makeing it appear formatted.

 

[greykitten]

Anyone have a more informative expansive answer?


Thank you,

greykitten

 

[NARAYANK991]

Hi ,


1. You are right that myData and myData3 are named ranges ; specifically , they are tables ; you can see this by clicking on Formulas , Name Manager.


2. For AC7 , the formula in the file is :


=IF(myData[[#This Row],[Month Num]]=bestMonth,myData[[#This Row],[Value]],NA())


To understand the nomenclature of table formulae , refer to these links :


a. http://office.microsoft.com/en-in/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx


b. http://www.databison.com/index.php/table-formulas-in-excel/


c. http://blogs.office.com/b/microsoft-excel/archive/2005/10/28/tables-part-3-using-formulas-with-tables.aspx


3. You will find a facility called Trace Dependents in the Formulas , Formula Auditing section. You can place the cursor in any cell , and click on Trace Dependents , to find out which cells are using the value of the cell on which the cursor is placed. You will find that almost all the columns are required. Going by what you say , I think , in your case , your months will be in rows , the way the dates are present in column V.


4. Click on the chart , click on Select Data ; you will see that there are 3 series defined ; one for the best week , another for the best month , and the third for the remaining data.


5. The INDEX & MATCH combination is a very frequently used one ; there are a lot of sites which give detailed explanations of how this works :


a. http://www.contextures.com/xlFunctions03.html

b. http://www.randomwok.com/excel/how-to-use-index-match/

c. http://exceluser.com/blog/465/excels-best-lookup-method-index-match.html

d. http://www.myonlinetraininghub.com/excel-index-and-match-functions

e. http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2011/12/27/how-to-writing-index-match-and-index-match-match-formulas.aspx


Narayan