Help with formula to skip NA cells & account for gaps in dates

[almat514]

Hi All!

When charting the attached stock data points - how do I formulate that I want any NA data to return the figure above?
And also, because those are stock figures, there are date gaps for weekends and holidays. How do I account for that in the chart formatted data?
I'm still learning formulas & feel comfortable with basic Lookups, but have no clue on how to combine!

Thanks!!

ETA: One more thing...
I need the chart title from resulting data to display MoM and YoY percent change like so:
DJIA` "DJIA
Sep 05, 2017 to Oct 05, 2017, 4.70% Increase Oct 05, 2016 to Oct 05, 2017, 24.58% Increase"

S&P "S&P 500
Sep 05, 2017 to Oct 05, 2017, 3.83% Increase Oct 05, 2016 to Oct 05, 2017, 18.17% Increase"

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[almat514]

Thanks, Glenn!
I've attached the worksheet in the original post.

[Glenn Kennedy]

To be honest, I'm not sure what your problem is. Firstly, #N/A errors are ignored by Excel. Secondly, with so many datapoints, you will never see any gaps resulting from holidays.

[almat514]

I guess in summary, I need the data from both stocks to automatically populate in a chart within one year range and for that one year range to update everytime a new date is added. It also needs to show in title what the % change is between the periods i mentioned in the original post.
When just chart the figures, as you suggest, I get a graph with gaps & the % changes wouldn't be there (I just copied the original chart and didn't mess with the title)

[Glenn Kennedy]

Ahhhh... The penny drops... or at least begins to. It's the area plot that does it!! Weekends don't matter, the NA's do.... Is this a start in the right direction for Dow Jones?

How are the MoM and YoY to be calculated?

[Glenn Kennedy]

I just saw post 5. We'll sort the dyamic year bit out later. lets get the right appearance first.

[almat514]

Uh that is beautiful. It's also way nicer than the chart I had going.
Yes.

Thanks for naming it, dynamic year is what I want the charts to display and for the chart title to show.

ETA: So for the MoM and YoY calculations - I want to know what the % change (increase or decrease) is between say YoY 10/12/2017 and 10/12/2016 ....and MoM 10/12/2017 and 9/12/2017. I don't know how to account for the fact that sames dates in different months/years could not exist (because it could have been a weekend/holiday)?

[Glenn Kennedy]

Next step forward.

Two named ranges (CTRL-F3 to view). This is one of them, called "date":
=Stocks!$P$8:INDEX(Stocks!$P:$P,MATCH(1E+100,Stocks!$P:$P))

These automatically adjust the formula ranges down to the last row.

I tehen modified the formulae in V & W to pull over only the last year's worth of numbers, excluding the #N/A's:

=IFERROR(INDEX(P:P,SMALL(IF(ISNUMBER(Value),IF(Date>=EDATE(TODAY(),-12),ROW(Value))),ROWS(V$8:V8))),"")

It's an array formula.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

What's next?

[Glenn Kennedy]

Now with a dynamic legend

[almat514]

Now with a dynamic legend

My goodness. I'm not sure how to thank you. Usually I'd send you a wine basket but a digital one doesn't seem as exciting.

I can't access the full sheet now from my phone but other than the strange dates in x axis this seems to be more than I asked for. Thanks a million.

[almat514]

Now with a dynamic legend

I'll attempt to replicate your magic on the SP data & maybe learn some of this to do on my own next time. Maybe.

[Glenn Kennedy]

I see no value in virtual wine baskets... sadly.

But basically...


You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.