gbpfan1231 Posted May 14, 2007 Share Posted May 14, 2007 Excel Questions: I have a table with monthly numbers for multiple divisions. The first column of my data is the division 2 nd column is Project name. I then have 12 months of numbers and a FY year column to the right (total of 15 columns). Each division has many projects. How would you rank the top 10 projects based on full year dollars for each division? I know how to rank using Excel but it ranks the whole table based on one number. i want it to know what range is specific to each division and then rank those projects. Put another way... If I have three divisons with division A having 12 projects B having 25 and C having 15 i would want one formula to rank A 1 - 12 B 1-25 and C 1-15. Please help. Quote Link to comment Share on other sites More sharing options...
Big Country Posted May 14, 2007 Share Posted May 14, 2007 Are you looking ot just sort by division, then by total dollars, or am I missing part of the question? If so. just select your entire table, then under Data-Sort, for first sort, sort by division, then for second sort, sort by total dollars. Somehow I have a feeling it is not this simple and I am missing part of the question, but, if not.... hope it helps. Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted May 14, 2007 Author Share Posted May 14, 2007 Are you looking ot just sort by division, then by total dollars, or am I missing part of the question? If so. just select your entire table, then under Data-Sort, for first sort, sort by division, then for second sort, sort by total dollars. Somehow I have a feeling it is not this simple and I am missing part of the question, but, if not.... hope it helps. Nope. Not that simple. I need to rank the top 10 projects for each division. I am using those projects for other tabs after I know that they are in the top 10. When you sort by division and then by dollars my table is still 52 rows. I can eye up what are the top 10 but I want a formula that knows what projects are division A and would rank them and return the ranking 1-15 and also know what projects are division B and rank those 1-25. i want this in the same formula. I thought of a way but don't know how to write the formula. I have written formulas that tell me what is Row number has the first record for division A and what row number has the last record for division A. I could then write a if Then with an embedded rank like - =IF(A1 = "A", rank(C5:C20),0). I don't know how to write the formula to get the row 5 and row 20 into my IF statement. if I could do that I could get around my issue. Anyone as confused as me now?? Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted May 14, 2007 Author Share Posted May 14, 2007 Can anyone at least answer my second part?... how do you put a formula into another formula for example... If the number 5 is in cell A1 and 10 is in Cell A2 is it possible to create a formula that would pull in whatever is in cell C5 by using the number 5 that is in Cell A1??? Something like =C(A1) to really act like =C5. understand? Quote Link to comment Share on other sites More sharing options...
detlef Posted May 14, 2007 Share Posted May 14, 2007 (edited) I'm having a really hard time following your question. I'm not saying this to be a pain, but the english is very poor in your questions so it is quite hard to follow along. Edited May 14, 2007 by detlef Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted May 14, 2007 Author Share Posted May 14, 2007 I'm having a really hard time following your question. I'm not saying this to be a pain, but the english is very poor in your questions so it is quite hard to follow along. Not sure of another way to try and explain my question. I want to use the data that is in a cell as part of a formula in another cell. My issue is that I have a range of cells that makes up one table of data. In that table I would like to rank more than one "thing" in that table. the problem I have is that the range can change. One time I may want to rank rows rows 1 thru 6 and also rank 7 thru 15. Rows 1 thru 6 are projects for one division and 7 thru 15 are projects for a different division. next month it might be 1 thru 10 and 11 thru 18. I don't want to have to change the =rank formula each month to match the range for the two divisions. I know this is hard to understand without actually seeing it but I at least appreciate that people are reading it and trying to understand. Quote Link to comment Share on other sites More sharing options...
Big Country Posted May 14, 2007 Share Posted May 14, 2007 (edited) Assuming Column A = Division Name, Column B=Total Cost and Column C is where you are putting the formula for the ranking: Can you nest an IF statement into your rank formula so that it would essentally be Rank this If this cell =Division A, then rank this if cell=Division B and finally rank this if cell=Division C. I'm, not able to figure out though how to get the rank portion to only look at the column B values that have A values of the appropriate division. It probably can be done with multiple nested if statements, but I can't put it together off the top of my head at the moment. Edited May 14, 2007 by Big Country Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted May 14, 2007 Author Share Posted May 14, 2007 Assuming Column A = Division Name, Column B=Total Cost and Column C is where you are putting the formula for the ranking: Can you nest an IF statement into your rank formula so that it would essentally be Rank this If this cell =Division A, then rank this if cell=Division B and finally rank this if cell=Division C. I'm, not able to figure out though how to get the rank portion to only look at the column B values that have A values of the appropriate division. It probably can be done with multiple nested if statements, but I can't put it together off the top of my head at the moment. You are totally headed down the right track. For the Rank function you need to give the range of the cells you want evaluated. That is where I am having trouble since the numbers of rows for each division changes. Quote Link to comment Share on other sites More sharing options...
Big Country Posted May 14, 2007 Share Posted May 14, 2007 You are totally headed down the right track. For the Rank function you need to give the range of the cells you want evaluated. That is where I am having trouble since the numbers of rows for each division changes. It would kind of have to be a rankif type function (ala sumif or countif), although, I don't know how to get it to do this, particularly as it appears even with the nested if function, it would bring back a rank within the entire range, not just the subset. Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted May 14, 2007 Author Share Posted May 14, 2007 It would kind of have to be a rankif type function (ala sumif or countif), although, I don't know how to get it to do this, particularly as it appears even with the nested if function, it would bring back a rank within the entire range, not just the subset. Kind of figured it out... I wrote formulas to tell me what column and what row Division A's data starts and Ends in and what column and row Division B's data starts and ends in and then you can use the Rank function with nested INDIRECT functions using the start and end cells as references. very confusing but it does work. thanks for the help. Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted May 14, 2007 Author Share Posted May 14, 2007 Kind of figured it out... I wrote formulas to tell me what column and what row Division A's data starts and Ends in and what column and row Division B's data starts and ends in and then you can use the Rank function with nested INDIRECT functions using the start and end cells as references. very confusing but it does work. thanks for the help. Big Country - Also found this.. If column A = Division and B = Dollars =1+SUMPRODUCT(($A$2:$A$5=A2)*($B$2:$B$5>B2)) Works perfect (not really sure how I interpret this formula but I tested it and it works perfect) and found it by Googling "Excel Rankif" Thanks you for giving me the idea of searching for the word rankif Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.