+ Reply to Thread
Results 1 to 20 of 20

IF( OR IF)

  1. #1
    Max
    Guest

    Re: IF( OR IF)

    Try in M1:
    =IF(K1="WINNER","WINNER",IF(L1="BOOBY","BOOBY",""))

    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "Silvabod" <[email protected]> wrote in message
    news:[email protected]...
    > Cells in Col K contain EITHER text OR null, as the result of a function
    > test.
    > Cells in Col L contain EITHER text OR null, as the result of a different
    > function (which is triggered only when the function in Col K cells yields

    a
    > text value). Since 2nd function is dependant on the first, both cannot

    exist
    > in the same cell (circular reference).
    >
    > Need - Cells in Col M to contain EITHER the textvalue in K (if positive)

    OR
    > the textvalue in L (if positive) else null
    >
    > I can't get my head round the correct function nesting layout - help?
    >
    > =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor

    can
    > I find a google example).
    >
    > In practical terms - Cells K1:K60 function tests the adjacent row of 6
    > cells, if ALL 6 are positive, displays WINNER.
    > If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row

    of
    > 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,

    cells
    > K and L are left blank. These functions work.
    >
    > Problem is that Winner and Booby are displayed in adjacent columns - what

    I
    > want is just a ONE column print display, SO - hide cols K & L, copy text

    in
    > K or L into M (and there cannot be text in BOTH K and L in same row, it
    > can't be both a winner and booby!).
    >
    >
    >
    >




  2. #2
    Silvabod
    Guest

    IF( OR IF)

    Cells in Col K contain EITHER text OR null, as the result of a function
    test.
    Cells in Col L contain EITHER text OR null, as the result of a different
    function (which is triggered only when the function in Col K cells yields a
    text value). Since 2nd function is dependant on the first, both cannot exist
    in the same cell (circular reference).

    Need - Cells in Col M to contain EITHER the textvalue in K (if positive) OR
    the textvalue in L (if positive) else null

    I can't get my head round the correct function nesting layout - help?

    =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor can
    I find a google example).

    In practical terms - Cells K1:K60 function tests the adjacent row of 6
    cells, if ALL 6 are positive, displays WINNER.
    If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row of
    6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail, cells
    K and L are left blank. These functions work.

    Problem is that Winner and Booby are displayed in adjacent columns - what I
    want is just a ONE column print display, SO - hide cols K & L, copy text in
    K or L into M (and there cannot be text in BOTH K and L in same row, it
    can't be both a winner and booby!).





  3. #3
    Silvabod
    Guest

    Re: IF( OR IF)

    Max, logic looks good, but unfortunately doesn't work.
    I have deliberately created a "Winner", scenario to test, which demonstrates
    that the functions of Cols I and J do work - your suggested solution is in
    K1:K6 with no output (and no error).
    (the OP had wrong col ID's - these are corrected here)

    Is it because Col I and J contents are functions, and what I need is the
    RESULT of the formulae? (absolute value?)
    How do I do that?

    (Tried posting the small spreadsheet, as attachment but it didn't post).

    "Max" <[email protected]> wrote in message
    news:[email protected]...
    > Try in M1:
    > =IF(K1="WINNER","WINNER",IF(L1="BOOBY","BOOBY",""))
    >
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > Singapore, GMT+8
    > xdemechanik
    > http://savefile.com/projects/236895
    > --
    > "Silvabod" <[email protected]> wrote in message
    > news:[email protected]...
    >> Cells in Col K contain EITHER text OR null, as the result of a function
    >> test.
    >> Cells in Col L contain EITHER text OR null, as the result of a different
    >> function (which is triggered only when the function in Col K cells yields

    > a
    >> text value). Since 2nd function is dependant on the first, both cannot

    > exist
    >> in the same cell (circular reference).
    >>
    >> Need - Cells in Col M to contain EITHER the textvalue in K (if positive)

    > OR
    >> the textvalue in L (if positive) else null
    >>
    >> I can't get my head round the correct function nesting layout - help?
    >>
    >> =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor

    > can
    >> I find a google example).
    >>
    >> In practical terms - Cells K1:K60 function tests the adjacent row of 6
    >> cells, if ALL 6 are positive, displays WINNER.
    >> If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row

    > of
    >> 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,

    > cells
    >> K and L are left blank. These functions work.
    >>
    >> Problem is that Winner and Booby are displayed in adjacent columns -
    >> what

    > I
    >> want is just a ONE column print display, SO - hide cols K & L, copy text

    > in
    >> K or L into M (and there cannot be text in BOTH K and L in same row, it
    >> can't be both a winner and booby!).
    >>
    >>
    >>
    >>

    >
    >




  4. #4
    Max
    Guest

    Re: IF( OR IF)

    "Silvabod" wrote:
    ....
    > (Tried posting the small spreadsheet, as attachment but it didn't post).


    Luckily it didn't <g>. Please do not post any attachments to the newsgroups.

    You could use a free filehost, say*: http://flypicture.com/
    to upload your sample file, and then post just the *link* to d/l the file

    *Another alternative: http://cjoint.com/index.php

    Try the above, and post the link in your response
    (Think I need to take a closer look at your file/set-up)
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  5. #5
    Arvi Laanemets
    Guest

    Re: IF( OR IF)

    Hi

    What do you mean with 'null' as function result? Logically it must be or 0
    or "", as you can't set the cell value to Null in Excel, is it?

    =IF(K1<>"",K1,IF(L1<>"",L1,""))
    or
    =IF(K1<>0,K1,IF(L1<>0,L1,""))


    --
    Arvi Laanemets
    ( My real mail address: arvil<at>tarkon.ee )



    "Silvabod" <[email protected]> wrote in message
    news:[email protected]...
    > Cells in Col K contain EITHER text OR null, as the result of a function
    > test.
    > Cells in Col L contain EITHER text OR null, as the result of a different
    > function (which is triggered only when the function in Col K cells yields
    > a text value). Since 2nd function is dependant on the first, both cannot
    > exist in the same cell (circular reference).
    >
    > Need - Cells in Col M to contain EITHER the textvalue in K (if positive)
    > OR the textvalue in L (if positive) else null
    >
    > I can't get my head round the correct function nesting layout - help?
    >
    > =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor
    > can I find a google example).
    >
    > In practical terms - Cells K1:K60 function tests the adjacent row of 6
    > cells, if ALL 6 are positive, displays WINNER.
    > If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row
    > of 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,
    > cells K and L are left blank. These functions work.
    >
    > Problem is that Winner and Booby are displayed in adjacent columns - what
    > I want is just a ONE column print display, SO - hide cols K & L, copy text
    > in K or L into M (and there cannot be text in BOTH K and L in same row, it
    > can't be both a winner and booby!).
    >
    >
    >
    >




  6. #6
    Silvabod
    Guest

    Re: IF( OR IF)

    I'm a novice - for "null" read "" (sorry, thought they were the same
    thing).
    Have tried your solution, the result is a blank cell when it should be
    "WINNER", so regrettably, it doesn't work.

    =IF(K1<>"",K1,IF(L1<>"",L1,""))

    Cell K1 contains a complex multiple function, the result of which is
    "WINNER" only if all the functions return a positive (exact reverse for L1,
    different text).
    If the function conditions are not fully met, cells K1 and L1 remain as "".

    What I need is
    IF K1'a actual display is "WINNER", M1 = "WINNER" ? I think I'm in the
    murky realms of Absolute values ?

    Silvabod


    ..

    "Arvi Laanemets" <[email protected]> wrote in message
    news:[email protected]...
    > Hi
    >
    > What do you mean with 'null' as function result? Logically it must be or 0
    > or "", as you can't set the cell value to Null in Excel, is it?
    >
    > =IF(K1<>"",K1,IF(L1<>"",L1,""))
    > or
    > =IF(K1<>0,K1,IF(L1<>0,L1,""))
    >
    >
    > --
    > Arvi Laanemets
    > ( My real mail address: arvil<at>tarkon.ee )
    >
    >
    >
    > "Silvabod" <[email protected]> wrote in message
    > news:[email protected]...
    >> Cells in Col K contain EITHER text OR null, as the result of a function
    >> test.
    >> Cells in Col L contain EITHER text OR null, as the result of a different
    >> function (which is triggered only when the function in Col K cells yields
    >> a text value). Since 2nd function is dependant on the first, both cannot
    >> exist in the same cell (circular reference).
    >>
    >> Need - Cells in Col M to contain EITHER the textvalue in K (if positive)
    >> OR the textvalue in L (if positive) else null
    >>
    >> I can't get my head round the correct function nesting layout - help?
    >>
    >> =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor
    >> can I find a google example).
    >>
    >> In practical terms - Cells K1:K60 function tests the adjacent row of 6
    >> cells, if ALL 6 are positive, displays WINNER.
    >> If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row
    >> of 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,
    >> cells K and L are left blank. These functions work.
    >>
    >> Problem is that Winner and Booby are displayed in adjacent columns -
    >> what I want is just a ONE column print display, SO - hide cols K & L,
    >> copy text in K or L into M (and there cannot be text in BOTH K and L in
    >> same row, it can't be both a winner and booby!).
    >>
    >>
    >>
    >>

    >
    >




  7. #7
    Silvabod
    Guest

    Re: IF( OR IF) SOLVED

    In cell M1
    =IF(K1<>"",K1,IF(L1<>"",L1,"")) - works.
    Thanks, everyone, for your help.

    If anyone's downloaded the worksheet, you will note that the col. ID's are
    different to those posted - the "real" subject columns are I, J and K. (that
    was not the cause of earlier "not working" problem).

    Thanks again, Silvabod



    "Silvabod" <[email protected]> wrote in message
    news:[email protected]...
    > Cells in Col K contain EITHER text OR null, as the result of a function
    > test.
    > Cells in Col L contain EITHER text OR null, as the result of a different
    > function (which is triggered only when the function in Col K cells yields
    > a text value). Since 2nd function is dependant on the first, both cannot
    > exist in the same cell (circular reference).
    >
    > Need - Cells in Col M to contain EITHER the textvalue in K (if positive)
    > OR the textvalue in L (if positive) else null
    >
    > I can't get my head round the correct function nesting layout - help?
    >
    > =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor
    > can I find a google example).
    >
    > In practical terms - Cells K1:K60 function tests the adjacent row of 6
    > cells, if ALL 6 are positive, displays WINNER.
    > If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row
    > of 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,
    > cells K and L are left blank. These functions work.
    >
    > Problem is that Winner and Booby are displayed in adjacent columns - what
    > I want is just a ONE column print display, SO - hide cols K & L, copy text
    > in K or L into M (and there cannot be text in BOTH K and L in same row, it
    > can't be both a winner and booby!).
    >
    >
    >
    >




  8. #8
    Biff
    Guest

    Re: IF( OR IF)

    Hi!

    Check your inbox!

    Biff

    "Silvabod" <[email protected]> wrote in message
    news:[email protected]...
    > Cells in Col K contain EITHER text OR null, as the result of a function
    > test.
    > Cells in Col L contain EITHER text OR null, as the result of a different
    > function (which is triggered only when the function in Col K cells yields
    > a text value). Since 2nd function is dependant on the first, both cannot
    > exist in the same cell (circular reference).
    >
    > Need - Cells in Col M to contain EITHER the textvalue in K (if positive)
    > OR the textvalue in L (if positive) else null
    >
    > I can't get my head round the correct function nesting layout - help?
    >
    > =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor
    > can I find a google example).
    >
    > In practical terms - Cells K1:K60 function tests the adjacent row of 6
    > cells, if ALL 6 are positive, displays WINNER.
    > If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row
    > of 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,
    > cells K and L are left blank. These functions work.
    >
    > Problem is that Winner and Booby are displayed in adjacent columns - what
    > I want is just a ONE column print display, SO - hide cols K & L, copy text
    > in K or L into M (and there cannot be text in BOTH K and L in same row, it
    > can't be both a winner and booby!).
    >
    >
    >
    >




  9. #9
    Silvabod
    Guest

    Re: IF( OR IF)

    Already did, and replied - now have the best of all worlds, a choice! Have
    combined the best of both worksheets.
    Thanks, Biff (publicly, in addition to teh private ones).
    Silvabod

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > Hi!
    >
    > Check your inbox!
    >
    > Biff
    >
    > "Silvabod" <[email protected]> wrote in message
    > news:[email protected]...
    >> Cells in Col K contain EITHER text OR null, as the result of a function
    >> test.
    >> Cells in Col L contain EITHER text OR null, as the result of a different
    >> function (which is triggered only when the function in Col K cells yields
    >> a text value). Since 2nd function is dependant on the first, both cannot
    >> exist in the same cell (circular reference).
    >>
    >> Need - Cells in Col M to contain EITHER the textvalue in K (if positive)
    >> OR the textvalue in L (if positive) else null
    >>
    >> I can't get my head round the correct function nesting layout - help?
    >>
    >> =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor
    >> can I find a google example).
    >>
    >> In practical terms - Cells K1:K60 function tests the adjacent row of 6
    >> cells, if ALL 6 are positive, displays WINNER.
    >> If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row
    >> of 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,
    >> cells K and L are left blank. These functions work.
    >>
    >> Problem is that Winner and Booby are displayed in adjacent columns -
    >> what I want is just a ONE column print display, SO - hide cols K & L,
    >> copy text in K or L into M (and there cannot be text in BOTH K and L in
    >> same row, it can't be both a winner and booby!).
    >>
    >>
    >>
    >>

    >
    >




  10. #10
    Biff
    Guest

    Re: IF( OR IF)

    You're welcome. Thanks for the feedback!

    Biff

    "Silvabod" <[email protected]> wrote in message
    news:[email protected]...
    > Already did, and replied - now have the best of all worlds, a choice!
    > Have combined the best of both worksheets.
    > Thanks, Biff (publicly, in addition to teh private ones).
    > Silvabod
    >
    > "Biff" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi!
    >>
    >> Check your inbox!
    >>
    >> Biff
    >>
    >> "Silvabod" <[email protected]> wrote in message
    >> news:[email protected]...
    >>> Cells in Col K contain EITHER text OR null, as the result of a function
    >>> test.
    >>> Cells in Col L contain EITHER text OR null, as the result of a different
    >>> function (which is triggered only when the function in Col K cells
    >>> yields a text value). Since 2nd function is dependant on the first, both
    >>> cannot exist in the same cell (circular reference).
    >>>
    >>> Need - Cells in Col M to contain EITHER the textvalue in K (if
    >>> positive) OR the textvalue in L (if positive) else null
    >>>
    >>> I can't get my head round the correct function nesting layout - help?
    >>>
    >>> =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor
    >>> can I find a google example).
    >>>
    >>> In practical terms - Cells K1:K60 function tests the adjacent row of 6
    >>> cells, if ALL 6 are positive, displays WINNER.
    >>> If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row
    >>> of 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,
    >>> cells K and L are left blank. These functions work.
    >>>
    >>> Problem is that Winner and Booby are displayed in adjacent columns -
    >>> what I want is just a ONE column print display, SO - hide cols K & L,
    >>> copy text in K or L into M (and there cannot be text in BOTH K and L in
    >>> same row, it can't be both a winner and booby!).
    >>>
    >>>
    >>>
    >>>

    >>
    >>

    >
    >




  11. #11
    Max
    Guest

    Re: IF( OR IF)

    Just thought you might also be interested in this file ..
    http://savefile.com/files/7565212
    Randomization_Lotto_program.xls

    It's a breeze to use/set-up, and great for generating random picks
    for whatever game you're into ..
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  12. #12
    Silvabod
    Guest

    Re: IF( OR IF)

    Alas! link gives "page cannot be displayed" so cannot check.

    But - my spreadsheet is not a game. It's specifically designed as a weekly
    print report for a noticeboard.

    Once populated with players/players numbers, all user has to do is enter the
    6 "lottery draw" numbers each week, and print the thing.
    Entering the 6 weekly numbers automatically flags the "matches" and when
    there's a winner (6 matches in one row), flags both "winner" and "booby
    prize" (for NO matches) - replacing the weekly hour or more spent manually
    checking and highlighting, and avoiding dyslexic error ...

    The "random pick" you mention is I believe more appropriate to an
    individual. Mine is a "group" service report benefitting the publican (who
    runs it for free, all money collected goes out in prizes) and the 60
    punters, who come in to check their progress.

    If anyone wants it, post a request with e_mail ID. It's only 40kbs, and
    free.
    If not for the lottery, it could be useful for the code - esp how to
    visually highlight "matched" cells WITHOUT colour! (i.e for black-and-white
    reports - it's done via cute conditional formatting).

    Silvabod





    "Max" <[email protected]> wrote in message
    news:[email protected]...
    > Just thought you might also be interested in this file ..
    > http://savefile.com/files/7565212
    > Randomization_Lotto_program.xls
    >
    > It's a breeze to use/set-up, and great for generating random picks
    > for whatever game you're into ..
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > Singapore, GMT+8
    > xdemechanik
    > http://savefile.com/projects/236895
    > --
    >
    >




  13. #13
    Max
    Guest

    Re: IF( OR IF)

    "Silvabod" wrote:
    > ... The "random pick" you mention is I believe
    > more appropriate to an individual ..


    Not really, albeit that would be the obvious.
    It could be also be put to a number of creative uses,
    including the running of private "self-organized" lotto/lucky draws,
    independent of national lotteries' results <g>.
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  14. #14
    Silvabod
    Guest

    Re: IF( OR IF)

    Depending on your age - "Housey-Housey" (Victorian kids game, still popular
    even now, or, commercially, Bingo (same thing) ? See what you mean!

    Mine's not a competition/game, just a utility (though it wouldn't be
    difficult to incorporate a random number generator to make it so).
    Silvabod
    "Max" <[email protected]> wrote in message
    news:[email protected]...
    > "Silvabod" wrote:
    >> ... The "random pick" you mention is I believe
    >> more appropriate to an individual ..

    >
    > Not really, albeit that would be the obvious.
    > It could be also be put to a number of creative uses,
    > including the running of private "self-organized" lotto/lucky draws,
    > independent of national lotteries' results <g>.
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > Singapore, GMT+8
    > xdemechanik
    > http://savefile.com/projects/236895
    > --
    >
    >




  15. #15
    Curt
    Guest

    Re: IF( OR IF)

    =IF(AND(OR(E6>=0,J6>=0,O6>=0)),(E6+J6+O6)," ") why must there be a entry
    into 2 of cells in formula for it to produce a total. want it to show any
    entry or total if more than one. Also is there a way to check for blank cell?

    "Max" wrote:

    > Try in M1:
    > =IF(K1="WINNER","WINNER",IF(L1="BOOBY","BOOBY",""))
    >
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > Singapore, GMT+8
    > xdemechanik
    > http://savefile.com/projects/236895
    > --
    > "Silvabod" <[email protected]> wrote in message
    > news:[email protected]...
    > > Cells in Col K contain EITHER text OR null, as the result of a function
    > > test.
    > > Cells in Col L contain EITHER text OR null, as the result of a different
    > > function (which is triggered only when the function in Col K cells yields

    > a
    > > text value). Since 2nd function is dependant on the first, both cannot

    > exist
    > > in the same cell (circular reference).
    > >
    > > Need - Cells in Col M to contain EITHER the textvalue in K (if positive)

    > OR
    > > the textvalue in L (if positive) else null
    > >
    > > I can't get my head round the correct function nesting layout - help?
    > >
    > > =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor

    > can
    > > I find a google example).
    > >
    > > In practical terms - Cells K1:K60 function tests the adjacent row of 6
    > > cells, if ALL 6 are positive, displays WINNER.
    > > If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row

    > of
    > > 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,

    > cells
    > > K and L are left blank. These functions work.
    > >
    > > Problem is that Winner and Booby are displayed in adjacent columns - what

    > I
    > > want is just a ONE column print display, SO - hide cols K & L, copy text

    > in
    > > K or L into M (and there cannot be text in BOTH K and L in same row, it
    > > can't be both a winner and booby!).
    > >
    > >
    > >
    > >

    >
    >
    >


  16. #16
    Bob Phillips
    Guest

    Re: IF( OR IF)

    Do you mean

    =IF(OR(AND(E6>=0,J6>=0),AND(E6>=,O6>=0),AND(J6>=0,O6>=0)),E6+J6+O6," ")


    test for blank with

    =ISBLANK(A1)
    or
    A1=""
    or
    LEN(A1)=0

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Curt" <[email protected]> wrote in message
    news:[email protected]...
    > =IF(AND(OR(E6>=0,J6>=0,O6>=0)),(E6+J6+O6)," ") why must there be a entry
    > into 2 of cells in formula for it to produce a total. want it to show any
    > entry or total if more than one. Also is there a way to check for blank

    cell?
    >
    > "Max" wrote:
    >
    > > Try in M1:
    > > =IF(K1="WINNER","WINNER",IF(L1="BOOBY","BOOBY",""))
    > >
    > > --
    > > Rgds
    > > Max
    > > xl 97
    > > ---
    > > Singapore, GMT+8
    > > xdemechanik
    > > http://savefile.com/projects/236895
    > > --
    > > "Silvabod" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Cells in Col K contain EITHER text OR null, as the result of a

    function
    > > > test.
    > > > Cells in Col L contain EITHER text OR null, as the result of a

    different
    > > > function (which is triggered only when the function in Col K cells

    yields
    > > a
    > > > text value). Since 2nd function is dependant on the first, both cannot

    > > exist
    > > > in the same cell (circular reference).
    > > >
    > > > Need - Cells in Col M to contain EITHER the textvalue in K (if

    positive)
    > > OR
    > > > the textvalue in L (if positive) else null
    > > >
    > > > I can't get my head round the correct function nesting layout - help?
    > > >
    > > > =IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work!

    (nor
    > > can
    > > > I find a google example).
    > > >
    > > > In practical terms - Cells K1:K60 function tests the adjacent row of 6
    > > > cells, if ALL 6 are positive, displays WINNER.
    > > > If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same

    row
    > > of
    > > > 6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail,

    > > cells
    > > > K and L are left blank. These functions work.
    > > >
    > > > Problem is that Winner and Booby are displayed in adjacent columns -

    what
    > > I
    > > > want is just a ONE column print display, SO - hide cols K & L, copy

    text
    > > in
    > > > K or L into M (and there cannot be text in BOTH K and L in same row,

    it
    > > > can't be both a winner and booby!).
    > > >
    > > >
    > > >
    > > >

    > >
    > >
    > >




  17. #17
    Max
    Guest

    Re: IF( OR IF)

    "Curt" wrote
    > =IF(AND(OR(E6>=0,J6>=0,O6>=0)),(E6+J6+O6)," ")


    Perhaps try instead: =IF(SUM(E6,J6,O6)=0,"",SUM(E6,J6,O6))

    > Also is there a way to check for blank cell?


    One way, e.g. in R6: =ISBLANK(E6)
    which returns TRUE if E6 is empty
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  18. #18
    Registered User
    Join Date
    11-13-2005
    Location
    Moscow
    Posts
    41

    Subtracting dates

    Could Someone please help me.
    A1 = 01-Jan-05 = The first day of the year.
    B1 = 16-June 05 = Their starting date.
    C1 = 10-Oct 05 = Their finishing date.

    example I have a program for my staff when they resign to count how many days they have worked here this year and to make sure they have not take to many days holiday and in cell A1 I have 01-Jan-05 and in cell B1 I have their starting date for example 16-June-05 and in cell C1 I have their finishing date for example 10-Oct-05. Now I am trying to work out a formula in D1 to tell me how many days the employee has worked here this year, so what I want is a formula to count the days between cell C1 (their finishing date) and A1 but if B1 is greater then A1 I want the formula to count the number of days between C1 and B1. I have been trying to work this out now for sometime.

    And many thanks in advance if you can help me.

  19. #19
    Registered User
    Join Date
    11-13-2005
    Location
    Moscow
    Posts
    41

    Subtracting dates

    A1 = 01-Jan-05 = The first day of the year.
    B1 = 16-June 05 = Their starting date.
    C1 = 10-Oct 05 = Their finishing date.

    example I have a program for my staff when they resign to count how many days they have worked here this year and to make sure they have not take too many days holiday and in cell A1 I have 01-Jan-05 and in cell B1 I have their starting date for example 16-June-05 and in cell C1 I have their finishing date for example 10-Oct-05. Now I am trying to work out a formula in D1 to tell me how many days the employee has worked here this year, so what I want is a formula to count the days between cell C1 (their finishing date) and A1 but if B1 is greater then A1 I want the formula to count the number of days between C1 and B1. I have been trying to work this out now for sometime.

    And many thanks in advance if you can help me.

  20. #20
    Bob Phillips
    Guest

    Re: IF( OR IF)

    =C1-MAX(A1,B1)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "paulrm906" <[email protected]> wrote
    in message news:[email protected]...
    >
    > COULD SOMEONE PLEASE HELP ME.
    > A1 = 01-Jan-05 = The first day of the year.
    > B1 = 16-June 05 = Their starting date.
    > C1 = 10-Oct 05 = Their finishing date.
    >
    > example I have a program for my staff when they resign to count how
    > many days they have worked here this year and to make sure they have
    > not take to many days holiday and in cell A1 I have 01-Jan-05 and in
    > cell B1 I have their starting date for example 16-June-05 and in cell
    > C1 I have their finishing date for example 10-Oct-05. Now I am trying
    > to work out a formula in D1 to tell me how many days the employee has
    > worked here this year, so what I want is a formula to count the days
    > between cell C1 (their finishing date) and A1 but if B1 is greater then
    > A1 I want the formula to count the number of days between C1 and B1. I
    > have been trying to work this out now for sometime.
    >
    > And many thanks in advance if you can help me.
    >
    >
    > --
    > paulrm906
    > ------------------------------------------------------------------------
    > paulrm906's Profile:

    http://www.excelforum.com/member.php...o&userid=28776
    > View this thread: http://www.excelforum.com/showthread...hreadid=474927
    >




+ 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