Dynamic LINE CHART for data with expanding SERIES AND DATES
Below is a sample of 5 tech stocks quotes for the last 30 days that I pulled via STOCKHISTORY function. From the above data I need to create a TREND (LINE) CHART that starts with 100 so I can compare price performance across stocks under the same baseline. The problem I have is: the above sample covers 5 stocks over 21 market days, but on my real world that stock list may grow/shrink depending on an user's watchlist, and same thing goes with the dates span, which may grow/shrink as per the user's choice (i.e.: 1 week, 1 month, 3 months, 1 year, 5 years, etc.). So we're talking about a 2-dimensional dynamic data array, and we all know that if I plot the chart just by highlighting the table and picking the chart type, Excel will just assume that static data array I had selected. Which means every time this data grows either horizontally (additional series) and/or vertically (larger dates span) the chart will just miss out on those new items. And in case the opposite happens the chart will just show empty series and/or an empty space on the horizontal axis. I already know the work around that prevents that from happening as far as DATES go, by setting up formula names for each data column spilled-range formula and putting them in place of each respective static data array for each series (since Excel chart STILL WON'T WORK WITH HASH REFERENCES - GO FIGURE!!!), as shown on the screenshots sequence that follows: 1D spilled range formula on row 4 for every column: Formula names created for the dates series and each stock trend data series: Chart series getting plotted via named formula: The above approach does work great, but only takes care of growing/shrinking date spans though. So I went ahead and created a 2D spilled range formula array as you may have already noticed at the above screenshots located on the right trend data set and named it "xALL". And then I tried to tweak the chart I had created using the 1D approach, got rid of all data series except one, and then switched its formula name to the 2D xALL named formula: But as I was expecting, line charts work only with single rows/columns, so I keep getting this error message: And then I thought: why not consolidate the entire tabular set (headers + date column + data series columns) with one single 2D spilled range via INDEX (named "xyALL"), highlight it, insert the line chart, and then replace the whole data set with this new named formula? Initially the plan seemed to work fine... But then when I checked whether Excel had converted that dynamic span into a static array, low and behold, after all Excel's smart but not THAT smart! And every time I go to the 'Chart data range' box and try to replace the static array "$N$3:$N$24" with the dynamic named formula "xyALL" Excel will just convert it back to the static range: And when I try to edit the SERIES references directly on the formula bar by replacing with the named formulas, I get the same expected "I can only take single row/column" error: So, now I'm stuck in neutral... Is there actually a way to do that? Would a pivot table/pivot chart approach do the trick here? I'm not that well versed with pivot tables and I guess that would require some advanced knowledge I currently don't have in case it can be done via such path. Thanks in advance for any help on this one! Leonardo
Chart with two Y Axes
Hello, I would like some assistance in creating a chart with two y-axes with the desired end result looking like this: Progress thus far: I've created a combo clustered bar chart with my two data series. Data series 1 (green): Far too large in scale and I must reduce this. The purpose of data series 1 is to visualize a comparison. Current year data - prior year = difference (data in series 1). When the difference is positive, the data would show on the right of the Y-axis and be shown in green (see first picture) When the difference is negative, data would show to the left of the y-axis and be shown in red. Quadrants 1 and 2 respectively if we're thinking of a typical coordinate plane. Honestly, I am at a bit of a loss and would appreciate any assistance. Thank you, Pat
Help to generate a simple table
Hi all I am looking for some assistance please. My team will manually input a start date and a duration. This will show an end date. Simple enough! What i would then like is a table create extracting this info. So if the Start Date is Mar 25 - Duration 12 Months The table would automatically be created below showing Mar 25, April 25, May 25, etc in independent cells. I will then take a value for a different place and spread this against the relevant months Thanks!
Huge geoCache attribute in a chart xml file
Hello everyone. Recently I have started working with map charts as detailed in the microsoft tutorial web page: https://support.microsoft.com/en-us/office/create-a-map-chart-in-excel-f2cfed55-d622-42cd-8ec9-ec8a358b593b I have noticed that the files created using this method, INCLUDING the downloadable example from the page I referenced above - have huge base64 included <binary> objects, that seem to be base64 of some unknown format. I would love to know what these blobs represent, how can i interpret them using say another program or application, and I would also love to know if deleting them has any effect on the resulting file, seeing as these blobs are rather huge.Populating a table based on data in another table
Hi, I'm creating a personal budget sheet where i have one tab that tracks all transactions (example entries below). This tab will have ALL transactions. I'd like to have a summary tab where I show calculations and graphs based on a selected year and month. Is it possible to filter for year and month and display those transactions only in the summary tab? The issue i'm having is if i use the FILTER function, it doesn't return an array so I can't reference the table again for something else. I also can't filter for both year and month, i've only been able to filter for one or the other with this function.
