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 |
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 |
=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 |
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 |
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 |
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 |
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 |
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 |
"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 ---- |
All times are GMT +1. The time now is 03:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com