Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mlowry
 
Posts: n/a
Default Excel CHALLENGE...3 questions(indirect,rank,array formula)...


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
------------------------------------------------------------------------
Mlowry's Profile: http://www.excelforum.com/member.php...o&userid=25757
View this thread: http://www.excelforum.com/showthread...hreadid=391709

  #2   Report Post  
duane
 
Posts: n/a
Default


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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=391709

  #3   Report Post  
Mlowry
 
Posts: n/a
Default


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

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

Any ideas?


--
Mlowry
------------------------------------------------------------------------
Mlowry's Profile: http://www.excelforum.com/member.php...o&userid=25757
View this thread: http://www.excelforum.com/showthread...hreadid=391709

  #4   Report Post  
duane
 
Posts: n/a
Default


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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=391709

  #5   Report Post  
duane
 
Posts: n/a
Default


no quotes on menu data either.


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=391709



  #6   Report Post  
Mlowry
 
Posts: n/a
Default


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

  #7   Report Post  
Mlowry
 
Posts: n/a
Default


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.


--
Mlowry
------------------------------------------------------------------------
Mlowry's Profile: http://www.excelforum.com/member.php...o&userid=25757
View this thread: http://www.excelforum.com/showthread...hreadid=391709

  #8   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in
message ...

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



  #9   Report Post  
Max
 
Posts: n/a
Default

"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$10 0)),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 <atyahoo<dotcom
----


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) Alain79 Excel Discussion (Misc queries) 4 June 14th 05 07:34 AM
Stop Excel Rounding Dates leinad512 Excel Discussion (Misc queries) 1 April 20th 05 04:19 PM
Hints And Tips For New Posters In The Excel Newsgroups Gary Brown Excel Worksheet Functions 0 April 15th 05 05:47 PM
Excel error - Startup (and Acrobat PDFMaker) gxdata Setting up and Configuration of Excel 0 February 4th 05 04:44 AM
Excel 2002 and 2000 co-install. Control Which Starts ? cnuk Excel Discussion (Misc queries) 2 January 17th 05 09:07 PM


All times are GMT +1. The time now is 12:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"