How Can I Add Numbers Separated by a Hyphen?

[pikedrop]

I have 12 scores that are entered in separate cells as shown. We calculate the season ending total based on the 7 highest scores. I need to have the 7 highest scores displayed in bold. In addition, the 'running' total needs to be computed based on the 7 highest scores even when there isn't seven scores entered - like early in the season.

For instance:

200-14 200-13 200-20 200-20 200-16 200-17 200-18 200-20 200-20 200-12 200-19 200-20

So the season ending total for this person would be: 1400-137.

I hope that I was clear enough as to what I am after. I would greatly appreciate any help to get this solved!

Matt

[Special-K]

Will the first number in your score ALWAYS be 200 ?

[pikedrop]

No they will not be. The first number can vary from 0 to 200 and the second number that is separated by the hyphen can vary from 0 to 20.

[Special-K]

So how do you work out a highest score?

100-7

99-8

Which is the highest score and why?

What happens if there are more than 7 high scores? Just take the first 7 high scores?

Do you want Condtional Formatting as well as you say you "need the 7 highest to appear in bold" but make no mention of this elsewhere nor in the thread subject.

[pikedrop]

The 100-7 would be the higher score compared to the 99-8. Same as 200-15 would be the higher score compared to 199-19.

[Special-K]

You havent said why, it was part of the same question.

[pikedrop]

The 100-7 would be the higher score compared to the 99-8. Same as 200-15 would be the higher score compared to 199-19. The first value is the primary number and the second number is the secondary value. The secondary number is typically used to break ties. The total for each person will be based on his/her 7 highest scores. Yes, the 7 highest scores needs to be displayed in bold.

[Special-K]

Here's one idea, not sure I'll have time to complete it.

The first number ranges from 1 to 3 digits.
The second number ranges from 1 to 2 digits.

If you right justify each part of the number you can put the two parts of the number together and rank it completely as a 5 digit number

e.g.

=TEXT(LEFT(A1,FIND("-",A1)-1),"000")&TEXT(RIGHT(A1,LEN(A1)-FIND("-",A1)),"00")+0

You may need a Helper row to do this

[pikedrop]

I am sorry but you lost me. Unfortunately, I am not Excel literate darn it anyway. I don't know how to right justify the two numbers in one cell. I don't know what you mean by a 'helper row'.

[danielexcelvba]

If you upload a sample workbook it will be easier to explain if Special-K doesn't have time. What they mean but a "helper row" is that if you have a row of your values (e.g. 222-22, 111-11), you would have a row under them which will show 22222, 11111 (using their formula). This would allow you to sort the values, or find the max values as they will be interpreted as numbers. You can not compare 222-22 and 111-11 directly, but 22222 is clearly larger than 11111

Edit: one suggestion I would make is to change the formula to
=NUMBERVALUE(TEXT(LEFT(A1,FIND("-",A1)-1),"000")&TEXT(RIGHT(A1,LEN(A1)-FIND("-",A1)),"00")+0)
so that the helper row or helper column can be sorted directly

[pikedrop]

Here is a sample.
200-20 200-18 199-19 200-14 200-13 200-20 200-11 200-20 200-15 200-13 200-17 200-18 1400-113
200-17 200-13 200-13 200-17 199-12 200-16 200-15 200-19 200-16 199-13 199-10 200-12 1400-100
200-13 200-19 200-16 200-13 200-11 200-10 199-11 200-13 200-17 198-12 200-10 199-3 1400-104
200-16 200-11 200-15 200-16 200-13 200-9 198-5 200-10 200-18 200-10 198-9 200-10 1400-99

I need the top 7 scores highlighted in bold in each row. Then those 7 scores need to be totaled to the right in the same format XXX-XXX. Unfortunately, I cannot make the totals appear lined up on the right hand side. But this is the jest of it.

[danielexcelvba]

I mean a sample workbook, so that people can make changes to it and show you the results

[pikedrop]

How do I go about copying my excel sheet and insert it in a reply?

[pikedrop]

I finally figured out how to attach the file. I sure hope someone can help me!

[pikedrop]

This file has the 7 highest scores in Bold. That is the way i would like them to appear.

[AliGW]

There are scores there without hyphens - should they ALL have hyphens?

[pikedrop]

Yes, they should all have hyphens.
Thank you!

[pikedrop]

I have added the hyphens that I mistakenly left out.

[pikedrop]

Looks like I have everyone stumped! Oh well, I will find another way.

[FlameRetired]

I think this takes care of the conversions and sums, but I am really stumped on the CF.

In A7 filled down and across.

Please Login or Register  to view this content.

For the sums in N7 and filled down.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	200-15	200-15	200-15	200-18	200-16	200-16	200-15	200-15	200-15	200-18	200-16	200-16		1400-115
2	200-17	200-18	199-14	199-14	200-16	200-19	200-17	200-18	199-14	199-14	200-16	200-19		1400-124
3	200-16	200-11	198-10	200-15	200-12	199-11	200-16	200-11	198-10	200-15	200-12	199-11		1400-97
4	200-11	200-9	198-15	200-14	200-11	198-13	200-11	200-9	198-15	200-14	200-11	198-13		1400-81
5	200-10	200-8	200-14	200-15	199-13	200-11	200-10	200-8	200-14	200-15	199-13	200-11		1400-90
6
7	200015	200015	200015	200018	200016	200016	200015	200015	200015	200018	200016	200016		1400-115
8	200017	200018	199014	199014	200016	200019	200017	200018	199014	199014	200016	200019		1400-124
9	200016	200011	198010	200015	200012	199011	200016	200011	198010	200015	200012	199011		1400-97
10	200011	200009	198015	200014	200011	198013	200011	200009	198015	200014	200011	198013		1400-81
11	200010	200008	200014	200015	199013	200011	200010	200008	200014	200015	199013	200011		1400-90

[FlameRetired]

Here's the file with the CF figured out. At the bottom A13:L17 is where I worked out the CF formula. It has no function beyond that. If you are satisfied with this those can be deleted. A7:L11 need to stay. Those are helper cells for the CF. Formulas are in post #20.

The CF formula is

Please Login or Register  to view this content.