Find highest/lowest value and assign points

[ponytrice2]

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?

Hope I have made sense.

thanks in advance.

Tony

 

[Robert Mika]

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?

Hope I have made sense.

thanks in advance.

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.

 

[ponytrice2]

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

 

[Robert Mika]

I'm still not sure about the layout .
Look at this as example:

Excel Workbook
	A	B	C
1
2	29.99	1
3	15	0
4	21.75	0
5
Sheet1

 

[ponytrice2]

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.

 

[Robert Mika]

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.