Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default Help Required 9th Dec 2015

Hi Team,

Greetings!!

Require a solution.

I have used a Rank Formlua which displays 1-8 Ranks.

if 1 is displayed it should reflect 1st in the cell
if 2 is displayed it should reflect 2nd in the cell
if 3 is displayed it should reflect 3rd in the cell
if 4 is displayed it should reflect 4th in the cell
if 5 is displayed it should reflect 5th in the cell
if 6 is displayed it should reflect 6th in the cell
if 7 is displayed it should reflect 7th in the cell
if 8 is displayed it should reflect 8th in the cell

Can someone give me the solution for the same.

Awaiting for your response.

Best Regards

Akash Maheshwari
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Help Required 9th Dec 2015

Hi,

Am Wed, 9 Dec 2015 10:13:36 -0800 (PST) schrieb Akash Maheshwari:

if 1 is displayed it should reflect 1st in the cell
if 2 is displayed it should reflect 2nd in the cell
if 3 is displayed it should reflect 3rd in the cell
if 4 is displayed it should reflect 4th in the cell
if 5 is displayed it should reflect 5th in the cell
if 6 is displayed it should reflect 6th in the cell
if 7 is displayed it should reflect 7th in the cell
if 8 is displayed it should reflect 8th in the cell


try:
=RANK(A20,$A$1:$A$20)&IF(RANK(A20,$A$1:$A$20)<4,CH OOSE(RANK(A20,$A$1:$A$20),"st","nd","rd"),"th")
or:
=RANK(A20,$A$1:$A$20)&VLOOKUP(RANK(A20,$A$1:$A$20) ,{1,"st";2,"nd";3,"rd";4,"th"},2,1)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default Help Required 9th Dec 2015

Thanks a tonn Mr. Claus.

Rgds

Akash Maheshwari

On Wednesday, December 9, 2015 at 11:57:56 PM UTC+5:30, Claus Busch wrote:
Hi,

Am Wed, 9 Dec 2015 10:13:36 -0800 (PST) schrieb Akash Maheshwari:

if 1 is displayed it should reflect 1st in the cell
if 2 is displayed it should reflect 2nd in the cell
if 3 is displayed it should reflect 3rd in the cell
if 4 is displayed it should reflect 4th in the cell
if 5 is displayed it should reflect 5th in the cell
if 6 is displayed it should reflect 6th in the cell
if 7 is displayed it should reflect 7th in the cell
if 8 is displayed it should reflect 8th in the cell


try:
=RANK(A20,$A$1:$A$20)&IF(RANK(A20,$A$1:$A$20)<4,CH OOSE(RANK(A20,$A$1:$A$20),"st","nd","rd"),"th")
or:
=RANK(A20,$A$1:$A$20)&VLOOKUP(RANK(A20,$A$1:$A$20) ,{1,"st";2,"nd";3,"rd";4,"th"},2,1)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF this cell is between 2/1/2015-2/28/15, then do something David Zman Excel Discussion (Misc queries) 2 February 19th 15 08:16 PM
Error 2015 Jeff Excel Programming 2 May 25th 06 08:38 PM
N/A # and error 2015 ina Excel Programming 8 April 27th 06 10:50 AM
Error 2015 gaba Excel Programming 4 December 17th 04 02:49 AM
Error 2015 from ConvertFormula Gordon[_13_] Excel Programming 0 April 29th 04 02:48 AM


All times are GMT +1. The time now is 09:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"