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

[Mlowry]

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!

 

[Mlowry]

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

is returning a "this formula contains an error" message box.

Any ideas?

 

[duane]

sorry - no quotes around the b85 - I copied your material and did not
take them out

 

[duane]

no quotes on menu data either.

 

[Mlowry]

I tried..

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

AND

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

The second one gives a #REF! error.

 

[Mlowry]

The countif function:

=COUNTIF('Menu Data'!D3986,"apples")

is returning a value of 0.

The array formula:

=AVERAGE(IF('Menu Data'!D3986= "apple", 'Menu Data'!E3:E986))

is also returning 0 as well.

The indirect function:

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

is also returning a #REF! error

Does anyone have any ideas on these? I appreciate your suggestions so
far, but I cant seem to get them working.

 

[Biff]

Hi!

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

That is the correct syntax. Are you sure there's something in Menu Data B85?
I'm guessing that's the problem. If B85 is empty the formula would evaluate
to:

=C0

Since there is no C0, you get #REF!

Biff

 

[Max]

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.

Try: =INDIRECT("'MenuData'!C" & B85)

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.

One guess ...
In the starting cell, the range has to be made absolute*,
i.e. use: "$B$51:$B$59" in the RANK formula,
before copying down 9 cells
*otherwise you'd get some pretty weird results ..

Both of the formulas below should work correctly
when placed in the starting cell and copied down:

=RANK(B51,$B$51:$B$59,0)
=RANK(B51,$B$51:$B$59,1)

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".

One way ..
Suppose the summary table below is in say, Sheet1, in A1: D5

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

where
# of Incidences = col B
Avg. Price = col C
Median Price = col D

Put in B2:
=SUMPRODUCT(--ISNUMBER(SEARCH(A2,MenuData!$D$2:$D$100))

(Normal ENTER will do)

Put in C2:
=AVERAGE(IF(ISNUMBER(SEARCH(A2,MenuData!$D$2:$D$100)),MenuData!$E$2:$E$100))

(Array-enter the formula with CTRL+SHIFT+ENTER)

Put in D2:
=MEDIAN(IF(ISNUMBER(SEARCH(A2,MenuData!$D$2:$D$100)),MenuData!$E$2:$E$100))

(Array-enter the formula with CTRL+SHIFT+ENTER)

Select B22, fill down to D5 to populate the table

Adapt the ranges to suit. Note that all ranges should be identically
structured, and we can't use entire column references in the array formulas.