Why doesn't this formula work?

Thanks:  0
Likes:  0

# Thread: Why doesn't this formula work?

1. ## 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. ## 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. ## 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. ## 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. ## Re: Why doesn't this formula work?

Originally Posted by janko
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

6. ## 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. ## 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. ## Re: Why doesn't this formula work?

Originally Posted by janko
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

 D E 1 L Result 2 1 3 3 3 7 4 7 4.1 5 4.1 9 6 9 19 7 19 7.1 8 7.1 15 9 15 31 10 31 11.1 11 11.1 23 12 23 47

Sheet1

 G H 1 W Result 2 1 1 3 3 1 4 7 1 5 9 4.1 6 19 4.1 7 15 7.1 8 31 7.1 9 23 11.1 10 47 11.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!

9. ## Re: Why doesn't this formula work?

Originally Posted by Firefly2012
Just looking at that formula makes my mind wander on to other things . . .
FOCUS Firefly2012 ! You can do it !

10. ## Re: Why doesn't this formula work?

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

## User Tag List

#### Posting Permissions

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