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

As per your original post:

cell a1 Jones---56
cell b1 Johnson---45
The final result would look like this; a1 Jones----56, b1 Johnson----45, c1
Johnson----6

I tried the formula you sent me but the names are reversed.

Not according to your original post. The formula does EXACTLY what you asked
for.

If both are equal then nothing or a zero is shown in the c cell

If both are equal, to return a 0:

=IF(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,0,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))

You keep changing things!

You show examples with 3"-", examples with 4"-", examples with no "-" but a
space, the result should be in C3 then C1, the raw data is in A1 and A2 then
it's in A1 and B1 then it's on different sheets in separate cells.
???????????

Tip when posting looking for help: tell us EXACTLY where things are and tell
us EXACTLY what the data looks like. Try to provide enough representative
samples so that we can get a good "picture" of your problem. It's easy for
you, you have the file right in front of you. We don't! We have to try to
recreate the situtation form your explanation.

Biff

"wally" wrote in message
ups.com...
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

Hey Biff, I want to apologize for not following the basic rules of
posting and did not give you all of the specific information when I
first posted. I have learned a lesson on just how to ask for assistance
when I need help. I now realize how much you are at a disadvantage when
the file is in front and I know exactly what I want to accomolish. The
formula you provided works just great, (I hate to sound nitpicky) but
there is one more item you may be able to clear up. It is; when the two
numbers are just one apart (a1 Jones----56 b1 Johnson----55) then cell
c1 shows Johnson----0. I know the zero shows up because it is rounded
down after taking 60% of the difference. Is there a way for it not to
show the name next to the zero, but still show the zero? Again, sorry
for the trouble and thanks for the great assistance.
Wally
Biff wrote:
As per your original post:

cell a1 Jones---56
cell b1 Johnson---45
The final result would look like this; a1 Jones----56, b1 Johnson----45, c1
Johnson----6

I tried the formula you sent me but the names are reversed.

Not according to your original post. The formula does EXACTLY what you asked
for.

If both are equal then nothing or a zero is shown in the c cell

If both are equal, to return a 0:

=IF(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,0,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))

You keep changing things!

You show examples with 3"-", examples with 4"-", examples with no "-" but a
space, the result should be in C3 then C1, the raw data is in A1 and A2 then
it's in A1 and B1 then it's on different sheets in separate cells.
???????????

Tip when posting looking for help: tell us EXACTLY where things are and tell
us EXACTLY what the data looks like. Try to provide enough representative
samples so that we can get a good "picture" of your problem. It's easy for
you, you have the file right in front of you. We don't! We have to try to
recreate the situtation form your explanation.

Biff

"wally" wrote in message
ups.com...
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

Ok, this is starting to get "unwieldy" !!!!

=IF(OR(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)=0),0,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))

You're approaching "Monster formula" status and this thing doesn't even have
any error checking in it!

Biff

"wally" wrote in message
ps.com...
Hey Biff, I want to apologize for not following the basic rules of
posting and did not give you all of the specific information when I
first posted. I have learned a lesson on just how to ask for assistance
when I need help. I now realize how much you are at a disadvantage when
the file is in front and I know exactly what I want to accomolish. The
formula you provided works just great, (I hate to sound nitpicky) but
there is one more item you may be able to clear up. It is; when the two
numbers are just one apart (a1 Jones----56 b1 Johnson----55) then cell
c1 shows Johnson----0. I know the zero shows up because it is rounded
down after taking 60% of the difference. Is there a way for it not to
show the name next to the zero, but still show the zero? Again, sorry
for the trouble and thanks for the great assistance.
Wally
Biff wrote:
As per your original post:

cell a1 Jones---56
cell b1 Johnson---45
The final result would look like this; a1 Jones----56, b1 Johnson----45,
c1
Johnson----6

I tried the formula you sent me but the names are reversed.

Not according to your original post. The formula does EXACTLY what you
asked
for.

If both are equal then nothing or a zero is shown in the c cell

If both are equal, to return a 0:

=IF(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,0,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))

You keep changing things!

You show examples with 3"-", examples with 4"-", examples with no "-" but
a
space, the result should be in C3 then C1, the raw data is in A1 and A2
then
it's in A1 and B1 then it's on different sheets in separate cells.
???????????

Tip when posting looking for help: tell us EXACTLY where things are and
tell
us EXACTLY what the data looks like. Try to provide enough representative
samples so that we can get a good "picture" of your problem. It's easy
for
you, you have the file right in front of you. We don't! We have to try to
recreate the situtation form your explanation.

Biff

"wally" wrote in message
ups.com...
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

Hi,
I agree, this is getting past monster stage. I'll go with what you gave
me, it's working just great. Thanks again for your help. Wally
Biff wrote:
Ok, this is starting to get "unwieldy" !!!!

=IF(OR(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)=0),0,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))

You're approaching "Monster formula" status and this thing doesn't even have
any error checking in it!

Biff

"wally" wrote in message
ps.com...
Hey Biff, I want to apologize for not following the basic rules of
posting and did not give you all of the specific information when I
first posted. I have learned a lesson on just how to ask for assistance
when I need help. I now realize how much you are at a disadvantage when
the file is in front and I know exactly what I want to accomolish. The
formula you provided works just great, (I hate to sound nitpicky) but
there is one more item you may be able to clear up. It is; when the two
numbers are just one apart (a1 Jones----56 b1 Johnson----55) then cell
c1 shows Johnson----0. I know the zero shows up because it is rounded
down after taking 60% of the difference. Is there a way for it not to
show the name next to the zero, but still show the zero? Again, sorry
for the trouble and thanks for the great assistance.
Wally
Biff wrote:
As per your original post:

cell a1 Jones---56
cell b1 Johnson---45
The final result would look like this; a1 Jones----56, b1 Johnson----45,
c1
Johnson----6

I tried the formula you sent me but the names are reversed.

Not according to your original post. The formula does EXACTLY what you
asked
for.

If both are equal then nothing or a zero is shown in the c cell

If both are equal, to return a 0:

=IF(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,0,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))

You keep changing things!

You show examples with 3"-", examples with 4"-", examples with no "-" but
a
space, the result should be in C3 then C1, the raw data is in A1 and A2
then
it's in A1 and B1 then it's on different sheets in separate cells.
???????????

Tip when posting looking for help: tell us EXACTLY where things are and
tell
us EXACTLY what the data looks like. Try to provide enough representative
samples so that we can get a good "picture" of your problem. It's easy
for
you, you have the file right in front of you. We don't! We have to try to
recreate the situtation form your explanation.

Biff

"wally" wrote in message
ups.com...
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

You're welcome. Thanks for the feedback!

Biff

"wally" wrote in message
ups.com...
Hi,
I agree, this is getting past monster stage. I'll go with what you gave
me, it's working just great. Thanks again for your help. Wally
Biff wrote:
Ok, this is starting to get "unwieldy" !!!!

=IF(OR(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)=0),0,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))

You're approaching "Monster formula" status and this thing doesn't even
have
any error checking in it!

Biff

"wally" wrote in message
ps.com...
Hey Biff, I want to apologize for not following the basic rules of
posting and did not give you all of the specific information when I
first posted. I have learned a lesson on just how to ask for assistance
when I need help. I now realize how much you are at a disadvantage when
the file is in front and I know exactly what I want to accomolish. The
formula you provided works just great, (I hate to sound nitpicky) but
there is one more item you may be able to clear up. It is; when the two
numbers are just one apart (a1 Jones----56 b1 Johnson----55) then cell
c1 shows Johnson----0. I know the zero shows up because it is rounded
down after taking 60% of the difference. Is there a way for it not to
show the name next to the zero, but still show the zero? Again, sorry
for the trouble and thanks for the great assistance.
Wally
Biff wrote:
As per your original post:

cell a1 Jones---56
cell b1 Johnson---45
The final result would look like this; a1 Jones----56, b1
Johnson----45,
c1
Johnson----6

I tried the formula you sent me but the names are reversed.

Not according to your original post. The formula does EXACTLY what you
asked
for.

If both are equal then nothing or a zero is shown in the c cell

If both are equal, to return a 0:

=IF(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,0,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))

You keep changing things!

You show examples with 3"-", examples with 4"-", examples with no "-"
but
a
space, the result should be in C3 then C1, the raw data is in A1 and
A2
then
it's in A1 and B1 then it's on different sheets in separate cells.
???????????

Tip when posting looking for help: tell us EXACTLY where things are
and
tell
us EXACTLY what the data looks like. Try to provide enough
representative
samples so that we can get a good "picture" of your problem. It's easy
for
you, you have the file right in front of you. We don't! We have to try
to
recreate the situtation form your explanation.

Biff

"wally" wrote in message
ups.com...
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