Excel MVP needed! Please help with an (Aggregate) RANK

[collegeitdept]

I have a sample workbook (attached by link) that has 5 worksheets of raw data. Column A contains dates (9/3/2013 - 10/24/2013). column headers are categories/variables (ie: sales, units, etc...)

I need a formula that will will give the me the rank for a date (10/24/2013) of of all the aggregate "sales" columns from the 5 worksheets please???


https://dl.dropboxusercontent.com/u/15717201/Excel Question.xlsx

(Caveats... It cannot be broken down into 2 steps... i can't add columns)


An Excel MVP is needed please.

 

[collegeitdept]

Can somebody please help?

Thanks!

 

[Smitty]

Where's the base data coming from? If you can get to it, then it would probably easier to use one dataset, and then just set up a Pivot Table. Is that possible?

 

[collegeitdept]

No unfortunately that's not possible.

I do know that sum product is able to do rank.... and it's also able to lookup... is there a way to do a sum product rank as a solution for this problem?

something like this: =(SUMPRODUCT(($C$3:$C$166=C3)*($F$3:$F$166>F3))+1)


(it's not that formula... but it does rank)??

 

[collegeitdept]

Can someone please help?

 

[collegeitdept]

Were you able to come closer to a solution please?

Where's the base data coming from? If you can get to it, then it would probably easier to use one dataset, and then just set up a Pivot Table. Is that possible?

 

[Marcelo Branco]

Hi,

I noted that you posted the same question on another forum and also has not gotten any reply.
> HELP with an (Aggregate) RANK formula

I also downloaded your file and spent some time to understand what you need. I could not.

At least for me, your question is not clear :
1. I did not understand what are the role of Aggregate Dates in C8:C18 and what are the expected results.
2. Your data is arranged in such way (different sheets) - that, sincerely, even just supposing what is your real problem, i could not imagine a possible solution.

I suppose you're in need of a solution with some urgency, so to increase your chances of getting help I would like to suggest

First
Observe rule #10 in Forum rules
http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Rule #10:
We prefer that members do not cross-post questions to other forums, but when this does occur members must make clear that they have cross-posted and must provide links to the cross-posts. Cross-posted questions that do not comply may be deleted or locked. For a discussion on the issues with cross-posting, see this link: Excelguru Help Site - A message to forum cross posters

Second
Try to explain more clearly what you need.

Third
Do not put so many restraints - no helper columns, no new data setup, etc.

Otherwise, I think, hardly anyone will be able to help you.

Regards,

M.

 

[Harry Flashman]

Actually this was the original thread that he made. I spent some time exploring the problem with him here.

http://www.mrexcel.com/forum/excel-questions/734678-rank-across-worksheet-columns-2.html#post3614104

The OP seems to want a single formula solution that will perform some kind of SUMIFS operation (based on value type such as "Sales" and "Date"), but which then simultaneously ranks the data. As has been pointed out a Pivot table is capable of such an operation fairly easily. But I am fairly sure that this cannot be done with a single formula.

To complicate matters a bit the dates include hours and minutes, so to SUMIF based on a particular date would require rounding the date to remove the hours and minutes.

As I have explained in the earlier thread, the problem could possibly be solved in two steps. Aggregate the data in one table, and then in another use a RANK function to rank those results. The OP has said that there must be no new columns - that is fine. The data could be summed on a hidden worksheet (if that is what presentation requires) and then summary page need only contain RANK formula that return the final results. I don't know why the OP insists the whole process must be done in a single step, he has not explained this properly.

 

[Marcelo Branco]

The OP seems to want a single formula solution that will perform some kind of SUMIFS operation (based on value type such as "Sales" and "Date"), but which then simultaneously ranks the data. As has been pointed out a Pivot table is capable of such an operation fairly easily. But I am fairly sure that this cannot be done with a single formula.

As I have explained in the earlier thread, the problem could possible be solved in two steps.

Harry,

Despite not having understood the problem, this seems the right way. (highlight in blue)

I cannot see a formula to sum across multiple sheets, with conditions, and, at the same time, build the rank. Without an intermediate step i cannot envisage a solution - maybe someone else can.

But I'm glad to know that you have stepped forward in the search for a solution.

I hope a solution is found, and if I can, I'll try to help.

M.

 

[collegeitdept]

Hi Marcelo,

The goal of column C is to show only the most recent dates.

and then to show for each of those dates the aggregate (sum) of all sales, units, and palettes data from each of the worksheets.

Once it has the sum of that date... take that sum and compare the ranking of the sum total for all dates available in the worksheets.


Please find the UPDATED workbook below:

https://www.dropbox.com/s/k35gt4exq33frsy/Excel Question.xlsx


(the old shared workbook had the time included with the date which prevented a date lookup to work properly without using the INT function).


Thanks!

Hi,

I noted that you posted the same question on another forum and also has not gotten any reply.
> HELP with an (Aggregate) RANK formula

I also downloaded your file and spent some time to understand what you need. I could not.

At least for me, your question is not clear :
1. I did not understand what are the role of Aggregate Dates in C8:C18 and what are the expected results.
2. Your data is arranged in such way (different sheets) - that, sincerely, even just supposing what is your real problem, i could not imagine a possible solution.

I suppose you're in need of a solution with some urgency, so to increase your chances of getting help I would like to suggest

First
Observe rule #10 in Forum rules
http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html



Second
Try to explain more clearly what you need.

Third
Do not put so many restraints - no helper columns, no new data setup, etc.

Otherwise, I think, hardly anyone will be able to help you.

Regards,

M.