I'm trying to assign a number value to a previously assigned rank. For instance, ranking #1 receives a value of 30, ranking #2 receives a value of 27, ranking #3 receives a value of 24, etc. I can't imagine this being difficult but I just don't know how to write this formula.

Perhaps I misinterpreted the OP's requirements.Big_Event mathetes formula will put values in a range, where as mine is assuming you have that in place and want to enter a number to return said placed values by rank. Rather than continue with two disparate solutions, it would help if you could qualify your needs exactly.

Nice job.You could also make the step value into a formula, thus only having to input the ceiling/highest value.=-ROUNDDOWN(HighValue/TotalCount,0)

Assuming for the moment you have these values assigned somewhere, say 'Rank' in column A with values starting in A2 (1, 2, 3, etc.), and 'Value' in column B with values starting in B2 (30, 27, 24, etc.), going down to row 11 (can be however long you want), you could use the following type of formula:=LOOKUP(E1,A2:A11,B2:B11)My assumption here was the value to be looking up was in cell E1.

Big_Event Assuming you want these in columns, the SEQUENCE function can do it: =SEQUENCE(30,,30,-1)If you want them in a row, =SEQUENCE(,30,30,-1) You might need to have an Office 365 subscription; I'm not sure whether this function is generally available outside of that.

Shouldn't there be a way to look at a column of values and be able to say, if 1 then 30, if 2 then 27, if 3 then 24, if 4 then 21, etc?

IF Function | Microsoft Community Hub

18 Replies

Big_Event
Copper Contributor
Apr 30, 2020
But%20you%20showed%20how%20to%20do%20it%20using%20a%20single%20cell%20of%20%22E1%22.%20%20How%20do%20I%20look%20at%20the%20other%20cells%20like%20E2%2C%20and%20E3%2C%20etc%20and%20still%20keep%20the%20same%20search%20criteria.%20%20I%20even%20tried%20to%20use%20the%20%22%24%22%20before%20the%20search%20criteria%20and%20couldn't%20seem%20to%20get%20it%20to%20work%20the%20same%20way
- Zack Barresse
  Iron Contributor
  Apr 30, 2020
  =LOOKUP(E1,$A$2:$A$11,$B$2:$B$11)
  
  Using the $ it locks in that reference. This is called absolute referencing. Without the $ it's called relative referencing. There are 4 types of [A1 notation] referencing:
  
  A1 - column and row are relative
  $A1 - column is absolute, row is relative
  A$1 - column is relative, row is absolute
  $A$1 - column and row are absolute
  
  When you copy these types of formulas down/across with relative referencing, those references will move as the destination cell moves. Conversely, as you move the formulas, those absolute parts of cell referencing will not move.
  
  In the above formula, the ranges ($A$2:$A$11,$B$2:$B$11) will not move. Column A should house your rank values, column B should house the return/associated values.
Big_Event
Copper Contributor
Apr 30, 2020
Shouldn't there be a way to look at a column of values and be able to say, if 1 then 30, if 2 then 27, if 3 then 24, if 4 then 21, etc?
- mathetes
  Gold Contributor
  Apr 30, 2020
  Big_Event
  =SEQUENCE(30,,30,-3) counts down by 3.
  
  I am assuming the adjacent column is already sorted in rank order...so there's no need then to look at it.
  - Zack Barresse
    Iron Contributor
    Apr 30, 2020
    Perhaps I misinterpreted the OP's requirements.
    
    Big_Event mathetes formula will put values in a range, where as mine is assuming you have that in place and want to enter a number to return said placed values by rank. Rather than continue with two disparate solutions, it would help if you could qualify your needs exactly.
- Zack Barresse
  Iron Contributor
  Apr 30, 2020
  That's what I posted... ?
mathetes
Gold Contributor
Apr 30, 2020
Big_Event

Assuming you want these in columns, the SEQUENCE function can do it: =SEQUENCE(30,,30,-1)
If you want them in a row, =SEQUENCE(,30,30,-1)

You might need to have an Office 365 subscription; I'm not sure whether this function is generally available outside of that.
Zack Barresse
Iron Contributor
Apr 30, 2020
Assuming for the moment you have these values assigned somewhere, say 'Rank' in column A with values starting in A2 (1, 2, 3, etc.), and 'Value' in column B with values starting in B2 (30, 27, 24, etc.), going down to row 11 (can be however long you want), you could use the following type of formula:

=LOOKUP(E1,A2:A11,B2:B11)

My assumption here was the value to be looking up was in cell E1.

Forum Discussion

IF Function

18 Replies

Resources