How to compute stock yield using an array with data on a different sheet in excel formula

Stock yield is a measure of how much income a stock generates in relation to its price. It is usually expressed as a percentage. One way to calculate stock yield is to divide the annual dividend paid by the stock by its current price. For example, if a stock pays $2 per year in dividends and its price is $50, then its yield is 4% ($2 / $50).

To compute stock yield using an array with data on a different sheet in Excel formula, you can use the following steps:

1. Create a named range for the data on the other sheet. For example, if your data is on Sheet2, in cells A2:B10, where column A has the stock prices and column B has the dividends, you can name this range as StockData by typing StockData in the Name Box and pressing Enter.
2. On the sheet where you want to calculate the yield, enter the formula =B2/A2 in cell C2, where B2 is the dividend and A2 is the price of the first stock. This will give you the yield of the first stock as a decimal value.
3. To convert the decimal value to a percentage, you can format the cell as a percentage by clicking the Percent Style button on the Home tab, or by pressing Ctrl+Shift+%. This will display the yield as 4% for the first stock.
4. To calculate the yield for the rest of the stocks, you can copy the formula in C2 and paste it in the cells below. Alternatively, you can drag the fill handle (the small square at the bottom right corner of the cell) down to fill the formula in the adjacent cells.
5. To refer to the data on the other sheet using the named range, you can replace the cell references in the formula with the name StockData. For example, the formula in C2 can be changed to =INDEX(StockData,ROW(),2)/INDEX(StockData,ROW(),1). This will give you the same result as before, but it will use the data from the other sheet instead of the current sheet. The INDEX function returns the value in a given position of an array, where the first argument is the array, the second argument is the row number, and the third argument is the column number. The ROW function returns the row number of the current cell.

Here is an example of how the Excel table might look like:

The result of the scenario is that the average yield of the five stocks is 3.08%, which can be calculated by using the formula =AVERAGE(C2:C6).