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!

[duane]

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

[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'!D3:D986,"apples")

is returning a value of 0.

The array formula:

=AVERAGE(IF('Menu Data'!D3:D986= "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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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

"Mlowry" <[email protected]> wrote in
message news:[email protected]...
>
> I tried..
>
> =INDIRECT("C"&Menu Data!B85)
>
> AND
>
> =INDIRECT("C"&'Menu Data'!B85)
>
> The second one gives a #REF! error.
>
>
> --
> Mlowry
> ------------------------------------------------------------------------
> Mlowry's Profile:
> http://www.excelforum.com/member.php...o&userid=25757
> View this thread: http://www.excelforum.com/showthread...hreadid=391709
>

[Max]

"Mlowry" wrote
> 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 B2:D2, 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----