Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Why doesn't this formula work?

This is a discussion on Why doesn't this formula work? within the Excel Questions forums, part of the Question Forums category; Hi, I am trying to use this formula but it doen't work. When I put "w" next to a cell ...

  1. #1
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Default Why doesn't this formula work?

    Hi,

    I am trying to use this formula but it doen't work. When I put "w" next to a cell that contains the nubmer 4.1, the formula does nothing and stops working. It is supposed to show me 4.1 again!

    thanks for the help

    ---------------------------------------------------
    =IF(AND(B1="l",A1=1),3,IF(AND(B1="l",A1=3),7,IF(AND(B1="l",A1=7),4.1,IF(AND(B1="l",A1=4.1),9,IF(AND(B1="l",A1=9),19,IF(AND(B1="l",A1=19),7.1,IF(AND(B1="l",A1=7.1),15,IF(AND(B1="l",A1=15),31,IF(AND(B1="l",A1=31),11.1,IF(AND(B1="l",A1=11.1),23,IF(AND(B1="l",A1=23),47,IF(AND(B1="w",A1=1),1,IF(AND(B1="w",A1=3),1,IF(AND(B1="w",A1=7),1,IF(AND(B1="w",A1=4.1,countif(a1:a20,"4.1")<5),4.1,IF(AND(B1="w",A1=9),4.1,IF(AND(B1="w",A1=19),4.1,IF(AND(B1="w",A1=7.1,countif(a1:a20,"7.1")<9),7.1,IF(AND(B1="w",A1=15),7.1,IF(AND(B1="w",A1=31),7.1,IF(AND(B1="w",A1=11.1,countif(a1:a20,"11.1")<11),11.1,IF(AND(B1="w",A1=23),11.1,IF(AND(B1="w",A1=47),11.1,1)))))))))))))))))))))))
    ---------------------------------------

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: Why doesn't this formula work?

    What version of xl are you using? What does the formula actually test for and return? Just looking at that formula makes my mind wander on to other things, so assuming I'm not alone in that regard, I would do your best to explain in words what it is meant to be doing.

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    5,030

    Default Why doesn't this formula work?

    Not sure for versions of Excel 2007 or later, but for 2003 and earlier, you can only have a maximum of 7 nested IFs.

    I'd suggested you'd be better off using a VLOOKUP function than all those nested IFs.

    Also, that formula *looks* like it can be simplified, i.e. the first part of it could be changed to:
    Code:
    IF(B1="I",LOOKUP(A1,{1,3,7},{3,7,4.1}),"B1 does not equal I")
    Too much there for me to go through right now and suggest ways to improve though, perhaps another board user can suggest


  4. #4
    Board Regular
    Join Date
    Dec 2011
    Posts
    536

    Default Re: Why doesn't this formula work?

    Hi janko, I would suggest you make a separate table with the different criteria and the desired output for each set of choices. Then use the index() function to get the value you want. It would a much less tedious way to solve your dilemma

  5. #5
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Why doesn't this formula work?

    Quote Originally Posted by janko View Post
    Hi,

    I am trying to use this formula but it doen't work. When I put "w" next to a cell that contains the nubmer 4.1, the formula does nothing and stops working. It is supposed to show me 4.1 again!

    thanks for the help

    ---------------------------------------------------
    =IF(AND(B1="l",A1=1),3,IF(AND(B1="l",A1=3),7,IF(AND(B1="l",A1=7),4.1,IF(AND(B1="l",A1=4.1),9,IF(AND(B1="l",A1=9),19,IF(AND(B1="l",A1=19),7.1,IF(AND(B1="l",A1=7.1),15,IF(AND(B1="l",A1=15),31,IF(AND(B1="l",A1=31),11.1,IF(AND(B1="l",A1=11.1),23,IF(AND(B1="l",A1=23),47,IF(AND(B1="w",A1=1),1,IF(AND(B1="w",A1=3),1,IF(AND(B1="w",A1=7),1,IF(AND(B1="w",A1=4.1,countif(a1:a20,"4.1")<5),4.1,IF(AND(B1="w",A1=9),4.1,IF(AND(B1="w",A1=19),4.1,IF(AND(B1="w",A1=7.1,countif(a1:a20,"7.1")<9),7.1,IF(AND(B1="w",A1=15),7.1,IF(AND(B1="w",A1=31),7.1,IF(AND(B1="w",A1=11.1,countif(a1:a20,"11.1")<11),11.1,IF(AND(B1="w",A1=23),11.1,IF(AND(B1="w",A1=47),11.1,1)))))))))))))))))))))))
    ---------------------------------------
    Create a lookup table and then use a lookup formula.

    See if this helps:

    http://contextures.com/xlFunctions02.html
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  6. #6
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Default Re: Why doesn't this formula work?

    Hi all,

    thanks for the quick replies.

    the formula is meant to give the numbers of 3,7,9,4,1.. and so on dependind on the letter i have written in the other box. My excel version is 2010!

    It works perfectly without the counif function. I need this function to count how many times, for instance, 4.1 appears in the range. If it is more than 5 times, then 4.1 won't be coming as the next number?



    thanks guys

  7. #7
    New Member
    Join Date
    Nov 2011
    Posts
    14

    Default Re: Why doesn't this formula work?

    hi again,

    this is what i want to do

    A1 B1 then
    if 1 l 3
    if 1 w 1
    if 3 w 1
    if 3 l 7
    if 7 w 1
    if 7 l 4.1
    if 4.1 l 9
    if 4.1 w 4.1 *(if there are less than 5 4.1s, if more than 5, then = 1)
    if 9 l 19
    if 9 w 4.1*
    if 19 l 7.1
    if 19 w 4.1*
    if 7.1 w 7.1 **(if there are less than 9 7.1s, if more than 9, then=1)
    if 7.1 l 15
    if 15 l 31
    if 15 w 7.1**
    if 31 w 7.1**
    if 31 l 11.1
    if 11.1 l 23
    if 11.1 w 11.1 ***(if there are less than 11 11.1s, if more than 11, then=1)
    if 23 l 47
    if 23 w 11.1 ***
    if 47 w 11.1 ***
    if 47 l 100

    it looks unclear :|

    thanks

  8. #8
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Why doesn't this formula work?

    Quote Originally Posted by janko View Post
    hi again,


    this is what i want to do

    A1 B1 then
    if 1 l 3
    if 1 w 1
    if 3 w 1
    if 3 l 7
    if 7 w 1
    if 7 l 4.1
    if 4.1 l 9
    if 4.1 w 4.1 *(if there are less than 5 4.1s, if more than 5, then = 1)
    if 9 l 19
    if 9 w 4.1*
    if 19 l 7.1
    if 19 w 4.1*
    if 7.1 w 7.1 **(if there are less than 9 7.1s, if more than 9, then=1)
    if 7.1 l 15
    if 15 l 31
    if 15 w 7.1**
    if 31 w 7.1**
    if 31 l 11.1
    if 11.1 l 23
    if 11.1 w 11.1 ***(if there are less than 11 11.1s, if more than 11, then=1)
    if 23 l 47
    if 23 w 11.1 ***
    if 47 w 11.1 ***
    if 47 l 100

    it looks unclear :|

    thanks
    Try this...

    Create these 2 tables:

    Sheet1

     DE
    1LResult
    213
    337
    474.1
    54.19
    6919
    7197.1
    87.115
    91531
    103111.1
    1111.123
    122347



    Sheet1

     GH
    1WResult
    211
    331
    471
    594.1
    6194.1
    7157.1
    8317.1
    92311.1
    104711.1



    Then, the formula becomes:

    =IF(AND(B1="w",A1=4.1,COUNTIF(A1:A20,4.1)<5),4.1,IF(AND(B1="w",A1=7.1,COUNTIF(A1:A20,7.1)<9),7.1,IF(AND(B1="w",A1=11.1,COUNTIF(A1:A20,11.1)<11),11.1,IF(AND(B1="w",COUNTIF(G2:G10,A1)),VLOOKUP(A1,G2:H10,2,0),IF(AND(B1="L",COUNTIF(D2:D12,A1)),VLOOKUP(A1,D2:E12,2,0),1)))))

    Still a monster!
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  9. #9
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,150

    Default Re: Why doesn't this formula work?

    Quote Originally Posted by Firefly2012 View Post
    Just looking at that formula makes my mind wander on to other things . . .
    FOCUS Firefly2012 ! You can do it !
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  10. #10
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: Why doesn't this formula work?

    Quote Originally Posted by Gerald Higgins View Post
    FOCUS Firefly2012 ! You can do it !
    I'm still failing miserably. i think i need beer

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com