rank formula

Hello - In my example below, I'm trying to use an array formula to filter the Top 5 "Accounts" in column AA based on the "Sumif Labor Cost" in column AB. I want to ultimately do this with a single array formula in cell AA13. I've provided the rank in column AC to show the top 5 items that I want...

Trying to rank data based on a % to last years sales. The formula I am using here is =IFERROR((IF(AND(F4=0,E4>0),E4/100,IF(AND(F4<0,E4>0),SUM(E4,ABS(F4))/100,(E4-F4)/F4))),"-"). Certain instances have 0 is both columns resulting in the "-". EX here : Sales (column E) LY Sales (column F) % to...

Here's a little table. Id like the formula in the first cell in rank to work: '=rank([@a],FILTER([a],[g]=[@g])) I tested this with sum sum(filter([a],[g]=[@g])) and it worked fine. I'm using mac excel though. Maybe I have to use a real excel?

Hi everyone, I’m new here and this is my first post. I’m trying to build a simple risk registry to rank my risks. I’m not that skilled in Excel, but I have tried as much I can. I have now reached my limit of knowledge trying to build several top-10 lists. I think I have managed to build one...

I have lost hope of me being able to work this one out, although sure easy for you all I am trying to rank each product individually by date in excel, so effectively each product in the list will have its own rank by date. For example Tomato Sauce rank 1, 2, 3, 4 etc. Black Bean CPU rank 1...

Hi, I have had a search through previous posts and none come out with what i need for this one. I need to rank some items based off what Area it is in (Column A) and its relevant score (Column H), I need to rank the top 5 items by each area. I can get the base ranking fine but the problem is...

Hello, I've extensively studied the =rank page and I am still struggling, I have Product names in columns B a unique identifier in A, this is used for Vlookups of prices from 6 different suppliers and a pre determined "winner" of a price cascade. What I need to be able to do is then, for...

I want to create a VBA function to calculate the rank, like that Function CALCULATERANKING(score As Double, list As Range) As Integer Dim row As Integer Dim column As Integer CalculateRanking = 1 For row = 1 To list.Rows.count If list.Cells(row, 1).Value > score Then...

Hi, I have a monthly/quarterly report that shows top 25 companies that we gained employees from. What I need to build a dynamic list that can be refreshed whenever I dump the updated data file and formula can be refreshed to update my list. I know using some formulas like rank, filter and other...

I am trying to build out a DAX RANKX formula that will rank Employee ID by Profit. Here is the formula I have today see below... However, this formula messes up when I start to layer in multiple dimensions into rows. Rank - DO Profit by Employee:=IF(ISBLANK([DO...

I have a formula that makes a list of unique employee names that I am trying to alphabetize. The problem is there can be 2-50 (range A1:A50) employees within the list causing the alphabetize rank formula to count blank or 0 cells due to having a formula in that cell. Is there a way to use a...

Hi, I'm trying to rank data based on 'Groups' & 'Score'. I've tried using SUMPRODUCT with no success. There are blank cells in the score column but the formula is labelling them with a rank of 1. I just need a unique rank 1-? for each group if there's a score with 1 being the best (see...

Hello Forum. Police officers have to qualify twice a year at the gun range. There are 3 dates to choose from in the spring and 3 dates in the fall. Based on seniority/rank, I want to create a formula that will select the officer's first choice if the target number for that range date is not...

Hi All, Please could you assist me with the following code. Function RankOverallTotal() Set Ranklist = Range("T3:T50") For Each Rcell In Ranklist Range(Rcell.Address).Offset(0, 1).Value = WorksheetFunction.Rank(Rcell.Value, Ranklist, 1) ' change 1 to 0 for desceending rank Next Rcell...