I hope you can help. I have posted something similar to this before but
got no response. I'll try again. I have name and a number in this
format: cell a1 Jones---56. In cell b1 I have another name i.e.,
Johnson---45. How can I subtract 45 from 56, take 60% of the difference
and in cell c3 show the following; Johnson---6. All numbers are to
rounded down to 0. The final result would look like this;
a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells
with the same type of info in them. Example; a2 Smith----34, b2
Jones----78, c3 would show Smith----26.(rounded down) I always want to
subtract the lower number from the highest and take 60% of the
difference.
Thanks for any help.
Wallyb

Hi!

This would be really easy if you could enter the names in one cell and the
numbers in another.

As long as the number of dashes is the same for every entry:

=FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Biff

"wally" wrote in message
oups.com...
I hope you can help. I have posted something similar to this before but
got no response. I'll try again. I have name and a number in this
format: cell a1 Jones---56. In cell b1 I have another name i.e.,
Johnson---45. How can I subtract 45 from 56, take 60% of the difference
and in cell c3 show the following; Johnson---6. All numbers are to
rounded down to 0. The final result would look like this;
a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells
with the same type of info in them. Example; a2 Smith----34, b2
Jones----78, c3 would show Smith----26.(rounded down) I always want to
subtract the lower number from the highest and take 60% of the
difference.
Thanks for any help.
Wallyb

Ooops!

Hold on.......

show the following; Johnson---6.
would show Smith----26.

Hmmm......you don't WANT this to be easy do you? <VBG

Back to the drawing board!

Biff

"Biff" wrote in message
...
Hi!

This would be really easy if you could enter the names in one cell and the
numbers in another.

As long as the number of dashes is the same for every entry:

=FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Biff

"wally" wrote in message
oups.com...
I hope you can help. I have posted something similar to this before but
got no response. I'll try again. I have name and a number in this
format: cell a1 Jones---56. In cell b1 I have another name i.e.,
Johnson---45. How can I subtract 45 from 56, take 60% of the difference
and in cell c3 show the following; Johnson---6. All numbers are to
rounded down to 0. The final result would look like this;
a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells
with the same type of info in them. Example; a2 Smith----34, b2
Jones----78, c3 would show Smith----26.(rounded down) I always want to
subtract the lower number from the highest and take 60% of the
difference.
Thanks for any help.
Wallyb

So, what happens if both are equal?

Jones---50
Smith---50

Or, will one ALWAYS be less?

Biff

"Biff" wrote in message
...
Ooops!

Hold on.......

show the following; Johnson---6.
would show Smith----26.

Hmmm......you don't WANT this to be easy do you? <VBG

Back to the drawing board!

Biff

"Biff" wrote in message
...
Hi!

This would be really easy if you could enter the names in one cell and
the numbers in another.

As long as the number of dashes is the same for every entry:

=FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Biff

"wally" wrote in message
oups.com...
I hope you can help. I have posted something similar to this before but
got no response. I'll try again. I have name and a number in this
format: cell a1 Jones---56. In cell b1 I have another name i.e.,
Johnson---45. How can I subtract 45 from 56, take 60% of the difference
and in cell c3 show the following; Johnson---6. All numbers are to
rounded down to 0. The final result would look like this;
a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells
with the same type of info in them. Example; a2 Smith----34, b2
Jones----78, c3 would show Smith----26.(rounded down) I always want to
subtract the lower number from the highest and take 60% of the
difference.
Thanks for any help.
Wallyb

Try this:

This assumes that one will ALWAYS be less than the other:

=IF(MID(A1,FIND("-",A1)+3,255)+0<MID(B1,FIND("-",B1)+3,255)+0,LEFT(A1,FIND("-",A1)-1),LEFT(B1,FIND("-",B1)-1))&"---"&FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Isn't that a thing of beauty?

Now, don't you think you'd be better off separating the names from the
numbers?

Biff

"Biff" wrote in message
...
So, what happens if both are equal?

Jones---50
Smith---50

Or, will one ALWAYS be less?

Biff

"Biff" wrote in message
...
Ooops!

Hold on.......

show the following; Johnson---6.
would show Smith----26.

Hmmm......you don't WANT this to be easy do you? <VBG

Back to the drawing board!

Biff

"Biff" wrote in message
...
Hi!

This would be really easy if you could enter the names in one cell and
the numbers in another.

As long as the number of dashes is the same for every entry:

=FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Biff

"wally" wrote in message
oups.com...
I hope you can help. I have posted something similar to this before but
got no response. I'll try again. I have name and a number in this
format: cell a1 Jones---56. In cell b1 I have another name i.e.,
Johnson---45. How can I subtract 45 from 56, take 60% of the difference
and in cell c3 show the following; Johnson---6. All numbers are to
rounded down to 0. The final result would look like this;
a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells
with the same type of info in them. Example; a2 Smith----34, b2
Jones----78, c3 would show Smith----26.(rounded down) I always want to
subtract the lower number from the highest and take 60% of the
difference.
Thanks for any help.
Wallyb

Hi Biff,
If both are equal then nothing or a zero is shown in the c cell. I
can't separate the name from the number because the names and numbers
change each week which means there will be a different name and number
in cells a1 and a2. Next week it may have in a1 Kirby 98, a2 Petro 56,
cell a3 Petro 25. The names, numbers and cells change each week. The
way I gather the name and number now is in one cell. Right now I gather
the name and number from a separate sheet named 1,2,3, etc. On each
sheet in cell b1 is the name, in cell r3 on each sheet is the number. I
would want to show the end result on a different sheet. If you can
devise a formula to accomplish the same thing as I am looking for, feel
free to let me know. I'll try it. As you noted, I tried the formula you
sent me but the names are reversed.
Thanks, Wally
Biff wrote:
Try this:

This assumes that one will ALWAYS be less than the other:

=IF(MID(A1,FIND("-",A1)+3,255)+0<MID(B1,FIND("-",B1)+3,255)+0,LEFT(A1,FIND("-",A1)-1),LEFT(B1,FIND("-",B1)-1))&"---"&FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Isn't that a thing of beauty?

Now, don't you think you'd be better off separating the names from the
numbers?

Biff

"Biff" wrote in message
...
So, what happens if both are equal?

Jones---50
Smith---50

Or, will one ALWAYS be less?

Biff

"Biff" wrote in message
...
Ooops!

Hold on.......

show the following; Johnson---6.
would show Smith----26.

Hmmm......you don't WANT this to be easy do you? <VBG

Back to the drawing board!

Biff

"Biff" wrote in message
...
Hi!

This would be really easy if you could enter the names in one cell and
the numbers in another.

As long as the number of dashes is the same for every entry:

=FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Biff

"wally" wrote in message
oups.com...
I hope you can help. I have posted something similar to this before but
got no response. I'll try again. I have name and a number in this
format: cell a1 Jones---56. In cell b1 I have another name i.e.,
Johnson---45. How can I subtract 45 from 56, take 60% of the difference
and in cell c3 show the following; Johnson---6. All numbers are to
rounded down to 0. The final result would look like this;
a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells
with the same type of info in them. Example; a2 Smith----34, b2
Jones----78, c3 would show Smith----26.(rounded down) I always want to
subtract the lower number from the highest and take 60% of the
difference.
Thanks for any help.
Wallyb

Biff, I got it to work, I changed the < sign to and it works great.
Only one more step, when both names have the same number, a zero comes
up next to a name. Can you fix so only a zero shows up?. Thank you very
much.
Biff wrote:
Try this:

This assumes that one will ALWAYS be less than the other:

=IF(MID(A1,FIND("-",A1)+3,255)+0<MID(B1,FIND("-",B1)+3,255)+0,LEFT(A1,FIND("-",A1)-1),LEFT(B1,FIND("-",B1)-1))&"---"&FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Isn't that a thing of beauty?

Now, don't you think you'd be better off separating the names from the
numbers?

Biff

"Biff" wrote in message
...
So, what happens if both are equal?

Jones---50
Smith---50

Or, will one ALWAYS be less?

Biff

"Biff" wrote in message
...
Ooops!

Hold on.......

show the following; Johnson---6.
would show Smith----26.

Hmmm......you don't WANT this to be easy do you? <VBG

Back to the drawing board!

Biff

"Biff" wrote in message
...
Hi!

This would be really easy if you could enter the names in one cell and
the numbers in another.

As long as the number of dashes is the same for every entry:

=FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Biff

"wally" wrote in message
oups.com...
I hope you can help. I have posted something similar to this before but
got no response. I'll try again. I have name and a number in this
format: cell a1 Jones---56. In cell b1 I have another name i.e.,
Johnson---45. How can I subtract 45 from 56, take 60% of the difference
and in cell c3 show the following; Johnson---6. All numbers are to
rounded down to 0. The final result would look like this;
a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells
with the same type of info in them. Example; a2 Smith----34, b2
Jones----78, c3 would show Smith----26.(rounded down) I always want to
subtract the lower number from the highest and take 60% of the
difference.
Thanks for any help.
Wallyb