Excel CHALLENGE...3 questions(indirect,rank,array formula)...

**Mlowry** · 07-31-2005, 11:07 PM

Are YOU up to the challenge?

Okay guys I have a few questions here to ask you excel experts. These are questions I could not figure out on my excel test.

1. First is getting my INDIRECT function to work properly.

What I'm trying to do is refer to the row number in a specific column in a seperate worksheet using the value in a cell.

='Menu Data'!C&(INDIRECT("B85"))

So I want to basicaly display the value(a text string) in column C in the Menu Data worksheet, using the value in cell B85 as the ROW value.

2. I'm trying to use the rank function to rank the values in a column in ascending order AND DESCENDING order. I got the Ascending to work as follows:

=RANK(B51,B51:B59,0)...According to the excel help, to rank the values in DESCENDING order I simply replace the 0 in the third argument with a 1. I tried this but it still ranks it in ASCENDING order.

3. The third and most challenging question is regarding what I would imagine to be a wrather complex array formula.

Here is the text the question gives:

"Using Excel formulas, populate the following table for each keyword. For the word APPLE, for example, you'll have to determine: (1) the number of times it appears in the DESCRIPTION column on the MENU DATA tab, (2) the average price of items that have the word ""apple"" in their description, and (3) the MEDIAN price of items that have the word ""apple"" in their description.

HINT: you'll need to use array formulas for to fill out the AVERAGE and MEDIAN columns."

I think I need to use the Find function...populate an array with the price values of all descriptions that have the word apple in them. The problem is, description is in column D and price is in colum E. So say we find all the rows that contain the word apple, how do we then populate an array with their corresponding E and ROW#?

In the end I will be populating a table that looks something like this:

# of Incidences Avg. Price Median Price
Apple
Banana
Peach
Muffin

I'm at a complete loss on this one. The name of the worksheet that the menu data is on is "Menu Data".

Thank you all in advance, any help will be greatly appreciated. If I can figure this stuff out I may just be able to land a job doing these fun things all day!

**duane** · 07-31-2005, 11:23 PM

for the first one:

=indirect("C"&'Menu Data'!B85")

for the second one:

zero is the code for descending, any positive number should give you ascending

for the third one you should set up a formula like:

=average(if(apple range = "apple", data range))

where apple range is the range of your description column, and data range is the column with the numerics.

same principal applies to median - these are both array formulae

for the count, just use countif