Excel formula please

ashliiii

Board Regular
Joined
Feb 27, 2011
Messages
69
Suppose I have a sheet as follows:

Cell A1 = 25
Cell A2 = 56
Cell A3 = 31
Cell A4 = 77
Cell A5 = 9
Cell A6 = 45

Cell B1 = large(A$1:A$6, 1)
Cell B2 = large(A$1:A$6, 2)
Cell B3 = large(A$1:A$6, 3)
Cell B4 = large(A$1:A$6, 4)
Cell B5 = large(A$1:A$6, 5)
Cell B6 = large(A$1:A$6, 6)

What is the formula in cell C1 to show the highest value is in cell A4. And when copied down, cell C2 to show the second highest value is in cell A2 etc.

C1 = A4
C2 = A2
C3 = A6 and so on.

Thank you for your time.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
In case you have equal values in column A:

Dante Amor
ABC
12577A4
25656A2
33145A6
47731A3
53131A5
64525A1
24%
Cell Formulas
RangeFormula
B1:B6B1=LARGE(A$1:A$6, ROW(A1))
C1:C6C1=ADDRESS(LARGE(($A$1:$A$6=B1)*(ROW($A$1:$A$6)),COUNTIF(B1:B$6,B1)),1,4,1)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,576
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top