+ Reply to Thread
Results 1 to 12 of 12

% query

  1. #1
    wally
    Guest

    % query

    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


  2. #2
    Biff
    Guest

    Re: % query

    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" <urnotwatu8@att.net> wrote in message
    news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    >




  3. #3
    Biff
    Guest

    Re: % query

    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" <biffinpitt@comcast.net> wrote in message
    news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    > 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" <urnotwatu8@att.net> wrote in message
    > news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    >>

    >
    >




  4. #4
    Biff
    Guest

    Re: % query

    So, what happens if both are equal?

    Jones---50
    Smith---50

    Or, will one ALWAYS be less?

    Biff

    "Biff" <biffinpitt@comcast.net> wrote in message
    news:OvNXrkmvGHA.4576@TK2MSFTNGP03.phx.gbl...
    > 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" <biffinpitt@comcast.net> wrote in message
    > news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    >> 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" <urnotwatu8@att.net> wrote in message
    >> news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    >>>

    >>
    >>

    >
    >




  5. #5
    Biff
    Guest

    Re: % query

    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" <biffinpitt@comcast.net> wrote in message
    news:uMlQ$smvGHA.4612@TK2MSFTNGP02.phx.gbl...
    > So, what happens if both are equal?
    >
    > Jones---50
    > Smith---50
    >
    > Or, will one ALWAYS be less?
    >
    > Biff
    >
    > "Biff" <biffinpitt@comcast.net> wrote in message
    > news:OvNXrkmvGHA.4576@TK2MSFTNGP03.phx.gbl...
    >> 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" <biffinpitt@comcast.net> wrote in message
    >> news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    >>> 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" <urnotwatu8@att.net> wrote in message
    >>> news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >




  6. #6
    wally
    Guest

    Re: % query

    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" <biffinpitt@comcast.net> wrote in message
    > news:uMlQ$smvGHA.4612@TK2MSFTNGP02.phx.gbl...
    > > So, what happens if both are equal?
    > >
    > > Jones---50
    > > Smith---50
    > >
    > > Or, will one ALWAYS be less?
    > >
    > > Biff
    > >
    > > "Biff" <biffinpitt@comcast.net> wrote in message
    > > news:OvNXrkmvGHA.4576@TK2MSFTNGP03.phx.gbl...
    > >> 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" <biffinpitt@comcast.net> wrote in message
    > >> news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    > >>> 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" <urnotwatu8@att.net> wrote in message
    > >>> news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    > >>>>
    > >>>
    > >>>
    > >>
    > >>

    > >
    > >



  7. #7
    wally
    Guest

    Re: % query

    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" <biffinpitt@comcast.net> wrote in message
    > news:uMlQ$smvGHA.4612@TK2MSFTNGP02.phx.gbl...
    > > So, what happens if both are equal?
    > >
    > > Jones---50
    > > Smith---50
    > >
    > > Or, will one ALWAYS be less?
    > >
    > > Biff
    > >
    > > "Biff" <biffinpitt@comcast.net> wrote in message
    > > news:OvNXrkmvGHA.4576@TK2MSFTNGP03.phx.gbl...
    > >> 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" <biffinpitt@comcast.net> wrote in message
    > >> news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    > >>> 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" <urnotwatu8@att.net> wrote in message
    > >>> news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    > >>>>
    > >>>
    > >>>
    > >>
    > >>

    > >
    > >



  8. #8
    Biff
    Guest

    Re: % query

    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" <urnotwatu8@att.net> wrote in message
    news:1155467912.074353.245130@i3g2000cwc.googlegroups.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" <biffinpitt@comcast.net> wrote in message
    >> news:uMlQ$smvGHA.4612@TK2MSFTNGP02.phx.gbl...
    >> > So, what happens if both are equal?
    >> >
    >> > Jones---50
    >> > Smith---50
    >> >
    >> > Or, will one ALWAYS be less?
    >> >
    >> > Biff
    >> >
    >> > "Biff" <biffinpitt@comcast.net> wrote in message
    >> > news:OvNXrkmvGHA.4576@TK2MSFTNGP03.phx.gbl...
    >> >> 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" <biffinpitt@comcast.net> wrote in message
    >> >> news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    >> >>> 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" <urnotwatu8@att.net> wrote in message
    >> >>> news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    >> >>>>
    >> >>>
    >> >>>
    >> >>
    >> >>
    >> >
    >> >

    >




  9. #9
    wally
    Guest

    Re: % query

    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" <urnotwatu8@att.net> wrote in message
    > news:1155467912.074353.245130@i3g2000cwc.googlegroups.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" <biffinpitt@comcast.net> wrote in message
    > >> news:uMlQ$smvGHA.4612@TK2MSFTNGP02.phx.gbl...
    > >> > So, what happens if both are equal?
    > >> >
    > >> > Jones---50
    > >> > Smith---50
    > >> >
    > >> > Or, will one ALWAYS be less?
    > >> >
    > >> > Biff
    > >> >
    > >> > "Biff" <biffinpitt@comcast.net> wrote in message
    > >> > news:OvNXrkmvGHA.4576@TK2MSFTNGP03.phx.gbl...
    > >> >> 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" <biffinpitt@comcast.net> wrote in message
    > >> >> news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    > >> >>> 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" <urnotwatu8@att.net> wrote in message
    > >> >>> news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    > >> >>>>
    > >> >>>
    > >> >>>
    > >> >>
    > >> >>
    > >> >
    > >> >

    > >



  10. #10
    Biff
    Guest

    Re: % query

    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" <urnotwatu8@att.net> wrote in message
    news:1155590025.131788.63360@75g2000cwc.googlegroups.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" <urnotwatu8@att.net> wrote in message
    >> news:1155467912.074353.245130@i3g2000cwc.googlegroups.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" <biffinpitt@comcast.net> wrote in message
    >> >> news:uMlQ$smvGHA.4612@TK2MSFTNGP02.phx.gbl...
    >> >> > So, what happens if both are equal?
    >> >> >
    >> >> > Jones---50
    >> >> > Smith---50
    >> >> >
    >> >> > Or, will one ALWAYS be less?
    >> >> >
    >> >> > Biff
    >> >> >
    >> >> > "Biff" <biffinpitt@comcast.net> wrote in message
    >> >> > news:OvNXrkmvGHA.4576@TK2MSFTNGP03.phx.gbl...
    >> >> >> 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" <biffinpitt@comcast.net> wrote in message
    >> >> >> news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    >> >> >>> 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" <urnotwatu8@att.net> wrote in message
    >> >> >>> news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    >> >> >>>>
    >> >> >>>
    >> >> >>>
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >

    >




  11. #11
    wally
    Guest

    Re: % query

    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" <urnotwatu8@att.net> wrote in message
    > news:1155590025.131788.63360@75g2000cwc.googlegroups.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" <urnotwatu8@att.net> wrote in message
    > >> news:1155467912.074353.245130@i3g2000cwc.googlegroups.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" <biffinpitt@comcast.net> wrote in message
    > >> >> news:uMlQ$smvGHA.4612@TK2MSFTNGP02.phx.gbl...
    > >> >> > So, what happens if both are equal?
    > >> >> >
    > >> >> > Jones---50
    > >> >> > Smith---50
    > >> >> >
    > >> >> > Or, will one ALWAYS be less?
    > >> >> >
    > >> >> > Biff
    > >> >> >
    > >> >> > "Biff" <biffinpitt@comcast.net> wrote in message
    > >> >> > news:OvNXrkmvGHA.4576@TK2MSFTNGP03.phx.gbl...
    > >> >> >> 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" <biffinpitt@comcast.net> wrote in message
    > >> >> >> news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    > >> >> >>> 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" <urnotwatu8@att.net> wrote in message
    > >> >> >>> news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    > >> >> >>>>
    > >> >> >>>
    > >> >> >>>
    > >> >> >>
    > >> >> >>
    > >> >> >
    > >> >> >
    > >> >

    > >



  12. #12
    Biff
    Guest

    Re: % query

    You're welcome. Thanks for the feedback!

    Biff

    "wally" <urnotwatu8@att.net> wrote in message
    news:1155643636.326049.295380@75g2000cwc.googlegroups.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" <urnotwatu8@att.net> wrote in message
    >> news:1155590025.131788.63360@75g2000cwc.googlegroups.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" <urnotwatu8@att.net> wrote in message
    >> >> news:1155467912.074353.245130@i3g2000cwc.googlegroups.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" <biffinpitt@comcast.net> wrote in message
    >> >> >> news:uMlQ$smvGHA.4612@TK2MSFTNGP02.phx.gbl...
    >> >> >> > So, what happens if both are equal?
    >> >> >> >
    >> >> >> > Jones---50
    >> >> >> > Smith---50
    >> >> >> >
    >> >> >> > Or, will one ALWAYS be less?
    >> >> >> >
    >> >> >> > Biff
    >> >> >> >
    >> >> >> > "Biff" <biffinpitt@comcast.net> wrote in message
    >> >> >> > news:OvNXrkmvGHA.4576@TK2MSFTNGP03.phx.gbl...
    >> >> >> >> 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" <biffinpitt@comcast.net> wrote in message
    >> >> >> >> news:%232xCefmvGHA.1372@TK2MSFTNGP04.phx.gbl...
    >> >> >> >>> 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" <urnotwatu8@att.net> wrote in message
    >> >> >> >>> news:1155422988.361646.118830@m79g2000cwm.googlegroups.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
    >> >> >> >>>>
    >> >> >> >>>
    >> >> >> >>>
    >> >> >> >>
    >> >> >> >>
    >> >> >> >
    >> >> >> >
    >> >> >
    >> >

    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1