# Find highest/lowest value and assign points

#### ponytrice2

##### New Member
Good afternoon peeps.
i have a table with data in. I need to find say the highest value of data range in A2 to A17 and give it one point and all others lower get zero. Then in the range of B2 to B17 find the lowest and give that a point and the other that are higher get zero.

might not be the best explanation above so here we go.
Let's say I have three pizza shops and I am giving the best shop points based on data. Firstly (all a cells) are average order price (the higher the better) lets say A2 shop has £29.99, A3 shop has £15.00 and A4 shop has £21.75. Is there a formula I can use to get the highest on and give it one point in a competition style table?

Tony

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Good afternoon peeps.
i have a table with data in. I need to find say the highest value of data range in A2 to A17 and give it one point and all others lower get zero. Then in the range of B2 to B17 find the lowest and give that a point and the other that are higher get zero.

might not be the best explanation above so here we go.
Let's say I have three pizza shops and I am giving the best shop points based on data. Firstly (all a cells) are average order price (the higher the better) lets say A2 shop has £29.99, A3 shop has £15.00 and A4 shop has £21.75. Is there a formula I can use to get the highest on and give it one point in a competition style table?

Tony

Hello and welcome.
If you have B column populate where would you like to see the points?
Coudl you post some data?
Please see my signature on how to do it.

Hiya. Ummmmmm. Unfortunately I am writing on my iPad so haven't got excel sheet on here to screen shot. I would like my points to come below the main table in a secondary (hidden) table as the store don't need to see that bit. But what I would like is each store list in this bottom table then in the cells next to there name to have a formula which will take its value, compare it with the others and if it finds it is the best then to return one point. The points can be shown in that cell as I will be having a cell at the end of each stores data row with the total points earnt.

col. A. Col. B. col. C (let's put these points bit here for now)
barnstaple. 19.99. If Barnstaple is best then one if not then zero
barry. 25.00. As above for Barry
weston supermare. 31.00. As above for Weston susupermare

sorry it's basic but not overly great with computers lol.
thanks for help

I'm still not sure about the layout .
Look at this as example:
Excel Workbook
ABC
1
229.991
3150
421.750
5
Sheet1

Looking at the result window in the example, it seems to do exactly what I wish. I shall use the formula like this and give it a go. Thank you for your help. I will post back with the results once I put it all in.

Looking at the result window in the example, it seems to do exactly what I wish. I shall use the formula like this and give it a go. Thank you for your help. I will post back with the results once I put it all in.

You are welcome.
If it does not work post an example of your data.

Replies
0
Views
135
Replies
4
Views
194
Replies
19
Views
191
Replies
2
Views
208
Replies
7
Views
205

1,203,605
Messages
6,056,269
Members
444,853
Latest member
sam69

### 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.

### Which adblocker are you using?

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

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