Finding the highest number

K

kipper19

Board Regular
Joined
Apr 12, 2014
Messages
92
Office Version
  1. 365
Platform
  1. Windows
Hi all,
Looking for some help on a formula to find the highest values in a range then returning a 1 against the name.


Highest.png
 
Sort by date Sort by votes
kipper19 said:
Thank you kindly Peter and to all for your help, works well
Click to expand...
Glad it worked for you.

I think the adjustments to Mike's formula could be
=IF(RANK(B1,B:B)<6,1-(RANK(B1,B:B)-1)*0.2, "")
or
=IF(RANK(B1,B:B)<6,1.2-RANK(B1,B:B)*0.2, "")
 
Last edited:
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
mikerickson said:
Small typo

=IF(RANK(B1,B:B) < 6, 1-(5-RANK(B1,B:B))*.2, "")
Click to expand...
Mike
I think there is more to it than that. For the OP's sample data that gives 0.2 for Helen who has the highest score & so should get "1" according to post #5 . Have you considered my suggestions in post #11 ?
 
Upvote 0
Peter,

I have noticed that as I have quiet a bit of data ie numbers in Column D that the formula will calculate the ranking over the whole column, how could I use a look up function to check Columns A & B then only average the data in Column D that corresponds to Column A & B etc

LookUp.png
 
Upvote 0
We can't copy the sample data from an image like that. Can you re-post the sample data and expected results in a form that we can copy to test with? The 'Look here' link in my signature block below has some suggestions for how you can do that.
 
Upvote 0
Peter,

I installed the addins VBHTML Maker into my excel addin folder but cannot get it work to post the file so don't worry to much, appreciate you trying to help
 
Upvote 0
kipper19 said:
I installed the addins VBHTML Maker into my excel addin folder but cannot get it work to post the file so don't worry to much, appreciate you trying to help
Click to expand...
You followed the wrong link in my signature. Follow 'Look here' not 'VBHTML Maker'
 
Upvote 0
[TABLE="width: 285"]
<colgroup><col width="124" style="width: 93pt; mso-width-source: userset; mso-width-alt: 4534;"> <col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="class: xl64, width: 124, bgcolor: #F2F2F2"]Peter I can only post as this which I think can be copied

Monday[/TD]
[TD="class: xl64, width: 64, bgcolor: #F2F2F2"]




1st[/TD]
[TD="width: 64, bgcolor: transparent"]




John[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]




12[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]




0.2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Julie[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]0.4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Jim[/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[TD="bgcolor: transparent, align: right"]0.8[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Steve[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Helen[/TD]
[TD="bgcolor: transparent, align: right"]32[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Tomas[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Paul[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]0.2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Peter[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Jon[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Monday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]1st[/TD]
[TD="bgcolor: transparent"]Jerry[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="class: xl65, bgcolor: #F2F2F2"]2nd[/TD]
[TD="class: xl63, bgcolor: transparent"]John[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0.4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]2nd[/TD]
[TD="bgcolor: transparent"]Julie[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]2nd[/TD]
[TD="bgcolor: transparent"]Jim[/TD]
[TD="bgcolor: transparent, align: right"]45[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]2nd[/TD]
[TD="bgcolor: transparent"]Steve[/TD]
[TD="bgcolor: transparent, align: right"]33[/TD]
[TD="bgcolor: transparent, align: right"]0.8[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]2nd[/TD]
[TD="bgcolor: transparent"]Helen[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]0.4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]2nd[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]2nd[/TD]
[TD="bgcolor: transparent"]Tomas[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]2nd[/TD]
[TD="bgcolor: transparent"]Paul[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]2nd[/TD]
[TD="bgcolor: transparent"]Peter[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl65, bgcolor: #F2F2F2"]3rd[/TD]
[TD="class: xl63, bgcolor: transparent"]John[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Julie[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]0.2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Jim[/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Steve[/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent, align: right"]0.8[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Helen[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]0.4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Tom[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Tomas[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Paul[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]0.2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Peter[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Jon[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Jerry[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Sam[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="class: xl64, bgcolor: #F2F2F2"]3rd[/TD]
[TD="bgcolor: transparent"]Stan[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
kipper19 said:
Peter I can only post as this which I think can be copied
Click to expand...
I can copy that but did you see post #18 ? The options make much neater screen shots. ;)


Book1
ABCDE
1Monday1stJohn120.2
2Monday1stJulie140.4
3Monday1stJim230.8
4Monday1stSteve8
5Monday1stHelen321
6Monday1stTom9
7Monday1stTomas8
8Monday1stPaul120.2
9Monday1stPeter150.6
10Monday1stJon7
11Monday1stJerry5
12Tuesday2ndJohn160.4
13Tuesday2ndJulie5
14Tuesday2ndJim451
15Tuesday2ndSteve330.8
16Tuesday2ndHelen120.2
17Tuesday2ndTom5
18Tuesday2ndTomas7
19Tuesday2ndPaul6
20Tuesday2ndPeter220.6
21Wednesday3rdJohn8
22Wednesday3rdJulie90.2
23Wednesday3rdJim231
24Wednesday3rdSteve210.8
25Wednesday3rdHelen110.4
26Wednesday3rdTom2
27Wednesday3rdTomas5
28Wednesday3rdPaul90.2
29Wednesday3rdPeter140.6
30Wednesday3rdJon6
31Wednesday3rdJerry3
32Wednesday3rdSam6
33Wednesday3rdStan6
Top 5 (2)
Cell Formulas
RangeFormula
E1=LOOKUP(SUMPRODUCT(($A$1:$A$33=A1)*($B$1:$B$33=B1)*(D1<$D$1:$D$33))+1,{1,2,3,4,5,6},{1,0.8,0.6,0.4,0.2,""})
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Find Highest Value of a Row and make it a 1 If less than highest leave blank.
2
Replies
15
Views
294
Fluff
Fluff
L
  • Solved
Return column number from a table of data
Replies
3
Views
120
Fluff
Fluff
P
  • Question Question
Return highest value
Replies
6
Views
167
Peter1973
P
Red over White
  • Question Question
Finding the highest numbered worksheet within a booklet
Replies
2
Views
162
Red over White
Red over White
O
  • Solved
Formula to return the highest value of a serie without considering abnormal values
Replies
8
Views
287
onche414
O

Forum statistics

Threads
1,223,708
Messages
6,174,002
Members
452,542
Latest member
Bricklin

Share this page

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