Start a ranked list from a value other than 1

[BOBBY RUSSELL]

Hi Everyone,


With regards to the simple excel rank function.

Anyone have an idea on how to start the order from a particular given number as opposed to starting from 1?

i.e Manipulate the order part of the formula where the number specifies how to rank values.

The only two options with rank eq appear to be:

If 0 or omitted, the values are ranked in descending order, i.e. from largest to smallest.
If 1 or any other non-zero value, the values are ranked in ascending order, i.e. from smallest to largest.


Many Thanks,


Bobby.

[Glenn Kennedy]

Just add the number.

=RANK(whatever)+number

[BOBBY RUSSELL]

Hiya Glenn,


Many thanks for your swift reply.

That seems to have worked quite well.

Any idea how to get rid of the cell displaying #VALUE!

I understand it's asking for a value to execute the calculation but I would rather prefer seeing it blank until value is input?


Many Thanks,


Bobby...

[Glenn Kennedy]

=IFERROR(yourformula,"")

will do that.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[BOBBY RUSSELL]

Hiya Glenn,


Below is my example formula which is working correctly:

=IF(C26<>"",RANK.EQ(C26,C$26:C$35,1),"")+6

However if i apply IFERROR to my formula above, excel is now stating I have entered too many arguments for this function?


Thanks,


Bobby.

[Glenn Kennedy]

You put the error trap in the wrong place:

=IF(C26<>"",RANK.EQ(C26,C$26:C$35,1)+6,"")

[AliGW]

In future, please use more descriptive thread titles (see our rules). Thanks.

[Glenn Kennedy]

Fair point, ali.

Hi. A good thread title is the sort of thing you would use as a search term on Google. Your title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be Google-friendly. Many people search the forum answers looking for help and your very general title wouldn't help (see also Forum Rule 1... which is very short!!).

You are a new user of this site. On this occasion, I have changed it for you.

However, if you continue to use weak titles, you can expect to have your thread blocked until you change iy yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

Regards,

Glenn.

[BOBBY RUSSELL]

Hiya Glenn,

Many thanks for correcting my error.

I really appreciate all your assistance.

I have also added positive feedback to your reputation.

And apologies for for the thread title. I will definately be more descriptive next time.

Kind Regards,

Bobby...

[Glenn Kennedy]

No problem. Enjoy lockdown!!