% query

[wally]

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]

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" <[email protected]> wrote in message
news:[email protected]...
>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]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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]

So, what happens if both are equal?

Jones---50
Smith---50

Or, will one ALWAYS be less?

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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]

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" <[email protected]> wrote in message
news:[email protected]...
> So, what happens if both are equal?
>
> Jones---50
> Smith---50
>
> Or, will one ALWAYS be less?
>
> Biff
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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
>>>>
>>>
>>>
>>
>>
>
>

[wally]

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" <[email protected]> wrote in message
> news:[email protected]...
> > So, what happens if both are equal?
> >
> > Jones---50
> > Smith---50
> >
> > Or, will one ALWAYS be less?
> >
> > Biff
> >
> > "Biff" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>>>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
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >

[wally]

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" <[email protected]> wrote in message
> news:[email protected]...
> > So, what happens if both are equal?
> >
> > Jones---50
> > Smith---50
> >
> > Or, will one ALWAYS be less?
> >
> > Biff
> >
> > "Biff" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>>>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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > So, what happens if both are equal?
>> >
>> > Jones---50
>> > Smith---50
>> >
>> > Or, will one ALWAYS be less?
>> >
>> > Biff
>> >
>> > "Biff" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >>> 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" <[email protected]> wrote in message
>> >>> news:[email protected]...
>> >>>>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
>> >>>>
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>

[wally]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > So, what happens if both are equal?
> >> >
> >> > Jones---50
> >> > Smith---50
> >> >
> >> > Or, will one ALWAYS be less?
> >> >
> >> > Biff
> >> >
> >> > "Biff" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >>> 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" <[email protected]> wrote in message
> >> >>> news:[email protected]...
> >> >>>>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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > So, what happens if both are equal?
>> >> >
>> >> > Jones---50
>> >> > Smith---50
>> >> >
>> >> > Or, will one ALWAYS be less?
>> >> >
>> >> > Biff
>> >> >
>> >> > "Biff" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> 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" <[email protected]> wrote in message
>> >> >> news:%[email protected]...
>> >> >>> 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" <[email protected]> wrote in message
>> >> >>> news:[email protected]...
>> >> >>>>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
>> >> >>>>
>> >> >>>
>> >> >>>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >
>

[wally]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > So, what happens if both are equal?
> >> >> >
> >> >> > Jones---50
> >> >> > Smith---50
> >> >> >
> >> >> > Or, will one ALWAYS be less?
> >> >> >
> >> >> > Biff
> >> >> >
> >> >> > "Biff" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> 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" <[email protected]> wrote in message
> >> >> >> news:%[email protected]...
> >> >> >>> 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" <[email protected]> wrote in message
> >> >> >>> news:[email protected]...
> >> >> >>>>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]

You're welcome. Thanks for the feedback!

Biff

"wally" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > So, what happens if both are equal?
>> >> >> >
>> >> >> > Jones---50
>> >> >> > Smith---50
>> >> >> >
>> >> >> > Or, will one ALWAYS be less?
>> >> >> >
>> >> >> > Biff
>> >> >> >
>> >> >> > "Biff" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> 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" <[email protected]> wrote in message
>> >> >> >> news:%[email protected]...
>> >> >> >>> 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" <[email protected]> wrote in message
>> >> >> >>> news:[email protected]...
>> >> >> >>>>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
>> >> >> >>>>
>> >> >> >>>
>> >> >> >>>
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >
>> >
>