INDEX/MATCH formula

[excelbaby231]

Hi everyone,

Thank you for reading my post. I've perused just about every single Excel forum/tips/tutorials on the internet and still don't know how to get the correct answer. I've tried using variations on INDEX/MATCH, COUNTIF but none are working.

Here is the workbook as best as I can describe:
E1:E20 are individual stock tickers
F1:F20 are economic book values of the tickers ranging from .1 to 2.2
G1:G20 are free cash flow yields of the tickers ranging from -15% to 15%

The question is: How do I use Excel to find the tickers in column E that have price to economic book values of less than 1.5 and free cash flow yields of greater than 5%?

The second question is: How do I use Excel to find the median free cash flow yield for all tickers in column E with price to economic book values greater than 1


Thank you all in advance...

[NBVC]

Since this is a "homework" kind of question, where if we give you the actual formula, we would not be helping you really in learning.... I will give a couple of links that should give you an idea...

For Column H have a look at this site (about sumproduct - working with multiple criteria):

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

For Column J have a look at this site (about array formulas):

http://www.cpearson.com/excel/ArrayFormulas.aspx

and have a look at the MEDIAN function in Excel Help.

Good Luck

[NBVC]

Well I hope you learned something in the process of having someone hand feed you the formulas, (in another forum) for when the real world hits you.


Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this