Why doesn't this formula work?

janko

New Member
Joined
Nov 16, 2011
Messages
14
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)))))))))))))))))))))))
---------------------------------------
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:

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

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

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!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top