+ Reply to Thread
Results 1 to 7 of 7

Formula for cell=exact(left...&2)

  1. #1
    nastech
    Guest

    Formula for cell=exact(left...&2)

    trying to make an equation for conditional format...?? where same cell has
    x2, need to test for Exact/ small "x",

    is this close, can't quite get: (where bj6 is 20)
    =T9=(EXACT(LEFT(T9,1),"x")&$BJ$6/10) comes up false for "x2", should be
    true.

    =EXACT(LEFT(T9,1),"x")&$BJ$6/10 gets a true2

    thanks.


  2. #2
    hans bal(nl)
    Guest

    RE: Formula for cell=exact(left...&2)

    Since =(EXACT(LEFT(T9,1),"x")&$BJ$6/10) returns true2 and T9 = x2,
    =T9=(EXACT(LEFT(T9,1),"x")&$BJ$6/10) must return false beacuse the 2 values
    are not equal.

    If you you just want to check on the small "x" you must use :
    =EXACT(LEFT(T9,1),"x")

    If you want to check the concatenation of "x"& BJ6/10 then use

    =EXACT(T9;"x"&$BJ$6/10)

    HTH

    Hans
    "nastech" wrote:

    > trying to make an equation for conditional format...?? where same cell has
    > x2, need to test for Exact/ small "x",
    >
    > is this close, can't quite get: (where bj6 is 20)
    > =T9=(EXACT(LEFT(T9,1),"x")&$BJ$6/10) comes up false for "x2", should be
    > true.
    >
    > =EXACT(LEFT(T9,1),"x")&$BJ$6/10 gets a true2
    >
    > thanks.
    >


  3. #3
    Bob Phillips
    Guest

    Re: Formula for cell=exact(left...&2)

    Try this

    =AND(EXACT(LEFT(T9),"x"),--RIGHT(T9,LEN(T9)-1)=$BJ$6/10)

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "nastech" <nastech@discussions.microsoft.com> wrote in message
    news:BFE5ECF6-4E1B-4849-BD0F-04DC5F99298B@microsoft.com...
    > trying to make an equation for conditional format...?? where same cell

    has
    > x2, need to test for Exact/ small "x",
    >
    > is this close, can't quite get: (where bj6 is 20)
    > =T9=(EXACT(LEFT(T9,1),"x")&$BJ$6/10) comes up false for "x2", should be
    > true.
    >
    > =EXACT(LEFT(T9,1),"x")&$BJ$6/10 gets a true2
    >
    > thanks.
    >




  4. #4
    macropod
    Guest

    Re: Formula for cell=exact(left...&2)

    Hi nastech,

    Perhaps
    =EXACT(LEFT(T9),"x")*$BJ$6/10>0

    Cheers
    --
    macropod
    [MVP - Microsoft Word]


    "nastech" <nastech@discussions.microsoft.com> wrote in message
    news:BFE5ECF6-4E1B-4849-BD0F-04DC5F99298B@microsoft.com...
    > trying to make an equation for conditional format...?? where same cell

    has
    > x2, need to test for Exact/ small "x",
    >
    > is this close, can't quite get: (where bj6 is 20)
    > =T9=(EXACT(LEFT(T9,1),"x")&$BJ$6/10) comes up false for "x2", should be
    > true.
    >
    > =EXACT(LEFT(T9,1),"x")&$BJ$6/10 gets a true2
    >
    > thanks.
    >




  5. #5
    nastech
    Guest

    Re: Formula for cell=exact(left...&2)

    Hi, thanks, will give that a try, was wondering what the double negative / 2
    minus signs do? thanks

    "Bob Phillips" wrote:

    > Try this
    >
    > =AND(EXACT(LEFT(T9),"x"),--RIGHT(T9,LEN(T9)-1)=$BJ$6/10)
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "nastech" <nastech@discussions.microsoft.com> wrote in message
    > news:BFE5ECF6-4E1B-4849-BD0F-04DC5F99298B@microsoft.com...
    > > trying to make an equation for conditional format...?? where same cell

    > has
    > > x2, need to test for Exact/ small "x",
    > >
    > > is this close, can't quite get: (where bj6 is 20)
    > > =T9=(EXACT(LEFT(T9,1),"x")&$BJ$6/10) comes up false for "x2", should be
    > > true.
    > >
    > > =EXACT(LEFT(T9,1),"x")&$BJ$6/10 gets a true2
    > >
    > > thanks.
    > >

    >
    >
    >


  6. #6
    Bob Phillips
    Guest

    Re: Formula for cell=exact(left...&2)

    That is a double unary. The RIGHT function will return a string, so a single
    unary will convert it to a number, but a negative number, so the second
    reverts it back to positive.

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "nastech" <nastech@discussions.microsoft.com> wrote in message
    news:B44A1627-55A3-4E34-A03A-0B8ECF8468CA@microsoft.com...
    > Hi, thanks, will give that a try, was wondering what the double negative /

    2
    > minus signs do? thanks
    >
    > "Bob Phillips" wrote:
    >
    > > Try this
    > >
    > > =AND(EXACT(LEFT(T9),"x"),--RIGHT(T9,LEN(T9)-1)=$BJ$6/10)
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (replace somewhere in email address with gmail if mailing direct)
    > >
    > > "nastech" <nastech@discussions.microsoft.com> wrote in message
    > > news:BFE5ECF6-4E1B-4849-BD0F-04DC5F99298B@microsoft.com...
    > > > trying to make an equation for conditional format...?? where same

    cell
    > > has
    > > > x2, need to test for Exact/ small "x",
    > > >
    > > > is this close, can't quite get: (where bj6 is 20)
    > > > =T9=(EXACT(LEFT(T9,1),"x")&$BJ$6/10) comes up false for "x2",

    should be
    > > > true.
    > > >
    > > > =EXACT(LEFT(T9,1),"x")&$BJ$6/10 gets a true2
    > > >
    > > > thanks.
    > > >

    > >
    > >
    > >




  7. #7
    nastech
    Guest

    Re: Formula for cell=exact(left...&2)

    sorry, didn't realize same replier... thanks

    "Bob Phillips" wrote:

    > That is a double unary. The RIGHT function will return a string, so a single
    > unary will convert it to a number, but a negative number, so the second
    > reverts it back to positive.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "nastech" <nastech@discussions.microsoft.com> wrote in message
    > news:B44A1627-55A3-4E34-A03A-0B8ECF8468CA@microsoft.com...
    > > Hi, thanks, will give that a try, was wondering what the double negative /

    > 2
    > > minus signs do? thanks
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Try this
    > > >
    > > > =AND(EXACT(LEFT(T9),"x"),--RIGHT(T9,LEN(T9)-1)=$BJ$6/10)
    > > >
    > > > --
    > > > HTH
    > > >
    > > > Bob Phillips
    > > >
    > > > (replace somewhere in email address with gmail if mailing direct)
    > > >
    > > > "nastech" <nastech@discussions.microsoft.com> wrote in message
    > > > news:BFE5ECF6-4E1B-4849-BD0F-04DC5F99298B@microsoft.com...
    > > > > trying to make an equation for conditional format...?? where same

    > cell
    > > > has
    > > > > x2, need to test for Exact/ small "x",
    > > > >
    > > > > is this close, can't quite get: (where bj6 is 20)
    > > > > =T9=(EXACT(LEFT(T9,1),"x")&$BJ$6/10) comes up false for "x2",

    > should be
    > > > > true.
    > > > >
    > > > > =EXACT(LEFT(T9,1),"x")&$BJ$6/10 gets a true2
    > > > >
    > > > > thanks.
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >


+ 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