Excel Array Formula Series 14.1: Stock Returns

1. Enter an Excel worksheet cell to construct a formula and thereof
Insert a Named Formula via the Define Name dialog box.

2. Partial evaluation pertaining to that part of the Named Formula
(inside the cell formula) shows a variety of observations such as the
following :-

3a. The most notable ones could be {3} and such like (namely, an
integer or a real number, depicted as a single element array).

3b. Another surprising case would be {1;2;3;4;5;6;7;8;9} (i.e., an
array of multiple elements).

3c. Less than a spectacle is a plain number (without being clad in a
pair of curly brackets).

4. And so, the question is, how is it possible to determine and
ascertain beforehand which Excel flavour would (likely) to be returned
for a Named Formula ?

5. After all, it is rather not quite possible to have a Named Formula
clad in a pair of curly brackets without invoking an annoying error
message to follow suit.

6. Regards.

When I add a new series to an Excel stock chart, the added series has stretched some of the high-low lines of the stock H-L-C bars. (they are stretched so as to meet the value of the new series)

Bob Peltier describes on a webpage how to deal with this for some circumstances. However, the example he shows to create a chart with H -L-C bars with an added series is not detailed well on this webpage so I don't understand what he did to get the H-L-C chart with the Index that he shows. It is not clear to me how to fix the problem. The webpage is:

http://peltiertech.com/Excel/Charts/StockChartPlus.html

Maybe I missed something.

Can anyone in this forum offer a suggestion?

Thanks,

Art

I can't get this array =SUM(IF(Test_Query!$A$2:$A$726="1",IF(Test_Query!$M$2:$M$726="97",IF(Test_Query!$H$2:$H$726="EACH",T est_Query!$G$2:$G$726)))) to return the correct value. What I was hoping the formula would do is look at Column A for a criteria "1", then look in Column M for criteria "97", then look in Column H for criteria "EACH", and then sum all rows in Column G that meet all 3 criteria.
Also, the formula is on the same page as the data and the data that is being analyzed in has subtotals (Using the subtotal function).
Thanks for any help on this.

I have a simple array formula in "sheet2" that pulls data from "sheet1" in the range of -1 to 1. It looks like this:

=SUM((Sheet2!$B10=Sheet1!$A$12:$A$13)*(Sheet2!C$9=Sheet1!$B$11:$G$11)*(Sheet1!$B$12:$G$13))

Every month, more data is added, but I want to be able to drag the formula for the rest of the year even though the data for the next 4 months does not exist yet.

The problem is my array formula returns empty cells as 0. I tried using an IF statement to put zeros as NA(), but some of my data includes zeros and I want those zeros to show up.

Finally, the reason I want zeros to show up, but not empty cell zeros is because I have the array data automatically charted on a line graph and I don't want the line dropping to zero just because the data is nonexistent.

Thanks in advance for any help!

Cross post here

I am posting this message for two reasons: (1) partially to confirm the existence of an Excel 2007 VBA bug that appeared after applying Office 2007 SP1, but (2) mostly to highlight the existence of such a bug so that others are aware of it. (I'm pretty sure it's a real bug, and not just an issue with my PC.)

When there are two or more series on a scatter plot, it appears that a call to Series.XValues(n) will return "n" rather than the value of the nth point. For example, if there is a Series object named "mySrs", then Code:

mySrs.XValues(5)

will incorrectly return "5", rather than the x-value of the fifth point in the series. This seems to occur only when there are two or more series; if there is only one series, Series.XValues(n) returns the proper value.

Here are the steps to recreate the issue in Excel 2007 with SP1:
* Create a new spreadsheet
* Populate two columns of data for the X and Y values for the first series in the scatter plot. Make sure that the x values are not equally-spaced. (I.e., don't use "1, 2, 3, 4, 5..." for the x values, but instead use someting like "1, 2.3, 4, 4.8, 5.2...")
* Create the scatter plot
* Run a macro to determine the XValues for each point in the series (an example is provided below)

You should notice that--at this point--everything works as expected. Continuing on...
* Populate two additional columns of data for the second series in the scatter plot.
* Add the second series to the scatter plot
* Run the same macro to determine the XValues for each point in the series

At this point you should see that the XValues are simply (and incorrectly) "1, 2, 3, 4..."

Here is the sample macro code that I used to discover the bug:
Code:

Sub CheckChart()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer
      
      Set myCht = ActiveSheet.ChartObjects(1).Chart
      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.Count
      
      Dim NumPts As Integer
      For NumPts = 1 To Npts
        Debug.Print "Point " & NumPts & ": XValue=" & mySrs.XValues(NumPts) & ", Value=" & mySrs.Values(NumPts)
      Next
      
      Debug.Print vbCrLf & "=================================" & vbCrLf
      
End Sub

The take-away is that if you are using VBA code that depends on the XValues of a series (such as Jon Peltier's excellent line and fill effects procedures), your code will not work properly.

It would be helpful if someone could validate this bug, but—as I indicated above—I'm pretty sure it's real.

I did submit feedback to Microsoft (here), but I'm not confident that it will result in a fix anytime soon.

Hope this helps you avoid hours of frustration and troubleshooting!

Cross post here

I am posting this message for two reasons: (1) partially to confirm the existence of an Excel 2007 VBA bug that appeared after applying Office 2007 SP1, but (2) mostly to highlight the existence of such a bug so that others are aware of it. (I'm pretty sure it's a real bug, and not just an issue with my PC.)

When there are two or more series on a scatter plot, it appears that a call to Series.XValues(n) will return "n" rather than the value of the nth point. For example, if there is a Series object named "mySrs", then Code:

mySrs.XValues(5)

will incorrectly return "5", rather than the x-value of the fifth point in the series. This seems to occur only when there are two or more series; if there is only one series, Series.XValues(n) returns the proper value.

Here are the steps to recreate the issue in Excel 2007 with SP1:
* Create a new spreadsheet
* Populate two columns of data for the X and Y values for the first series in the scatter plot. Make sure that the x values are not equally-spaced. (I.e., don't use "1, 2, 3, 4, 5..." for the x values, but instead use someting like "1, 2.3, 4, 4.8, 5.2...")
* Create the scatter plot
* Run a macro to determine the XValues for each point in the series (an example is provided below)

You should notice that--at this point--everything works as expected. Continuing on...
* Populate two additional columns of data for the second series in the scatter plot.
* Add the second series to the scatter plot
* Run the same macro to determine the XValues for each point in the series

At this point you should see that the XValues are simply (and incorrectly) "1, 2, 3, 4..."

Here is the sample macro code that I used to discover the bug:
Code:

Sub CheckChart()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer
      
      Set myCht = ActiveSheet.ChartObjects(1).Chart
      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.Count
      
      Dim NumPts As Integer
      For NumPts = 1 To Npts
        Debug.Print "Point " & NumPts & ": XValue=" & mySrs.XValues(NumPts) & ", Value=" & mySrs.Values(NumPts)
      Next
      
      Debug.Print vbCrLf & "=================================" & vbCrLf
      
End Sub

The take-away is that if you are using VBA code that depends on the XValues of a series (such as Jon Peltier's excellent line and fill effects procedures), your code will not work properly.

It would be helpful if someone could validate this bug, but—as I indicated above—I'm pretty sure it's real.

I did submit feedback to Microsoft (here), but I'm not confident that it will result in a fix anytime soon.

Hope this helps you avoid hours of frustration and troubleshooting!

Hi Everyone,

The attached file has 3 sheets. Sheet 2 uses array formual to retunr values from sheet 3.
On sheet one, I want to add a combo box (currently ive used data validation) but would prefer a combo box. Depending on what is selected fromt he combo boxes, I automatically want the result cells (pink) to return the values from the array. I have added notes and highlighted what the answers should be.

I have palyed around with Index and match without much sucess!

Anmy help is greatly appreciated.

Regards

I want to measure the stock returns (SP500) after "x" number of down days.
And i want to know how often these events occurs.

Example when the market goes 6 weeks in a row, the SP500 has been lower/higher 6 months later by what percentage. And its gone down 6 weeks in a row, 5 times in the last 10 years

Hey guys,

I am using a max function with array that returns the maximum value of column A when column B reads "Yes" (column B is either "Yes" or blank). The formula works when at least one row in column A is positive for rows with a "Yes" for column B. However, when all of the column A values assocaited with a "Yes" in B are negative, my formula returns a 0. I understand this is because the blanks in row B is returns a 0 in the formula which gets multiplied with the value in column A, resulting 0s for all rows when column B is blank. But, how should I fix this?

Thanks!