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)))))))))))))))))))))))
---------------------------------------
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)))))))))))))))))))))))
---------------------------------------