Email:      Pass:    Pass?


Advertisements


Free Excel Forum

More Than 7 Ifs Nested?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi All,

I have a formula with 7 IFs Nested within an IF formula. I also have some AND functions nested within it. I need to add more IFs, is this possible? Here is a copy of the current working formula:

=IF(AND(GST="",K28<>""),"Please enter",IF(AND(GST<>"",K28<>"",F28=""),"<----- Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28=""),"<----- Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28<>"",E28="Enter Date Above"),"<----- Enter ",IF(K28="SP",220,IF(K28="TPR",109.09,IF(K28="TD",136.36,IF(K28="TA",100,""))))))))

I just want to add another IF at the end like this:

=IF(AND(GST="",K28<>""),"Please enter",IF(AND(GST<>"",K28<>"",F28=""),"<----- Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28=""),"<----- Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28<>"",E28="Enter Date Above"),"<----- Enter ",IF(K28="SP",220,IF(K28="TPR",109.09,IF(K28="TD",136.36,IF(K28="TA",100,IF(K28="TA",100,""))))) ))))

But this comes up with an error, I assume that there must be a maximum number of items to nest?

Is there away around this?

This formula is what I have created inside an invoice that I get my staff to use. The idea is that it is Idiot proof, ie if they forget to enter whether or not they are registered for GST, then it will tell them. If one of the items on the form are missing, it will tell them, but then the last couple are for their pay.

Make any sense? think I have just confused myself... lol

Any help would be appreciated.

Clint


Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie

Similar Topics







I have this huge formula that I am using to create a field with text in it defining ingredients in food items. Is there anyway I can break it up?

Here's the formula:
Quote:

=C2&IF(SUM(D2:E2)>0," -","")
&IF(D2>0,"Vegetarian","")
&IF(D2=3," on request","")
&IF(SUM(D2:E2)=2," or ","")
&IF(E2>0,"Vegan","")
&IF(E2=3," on request","")
&IF(SUM(D2:E2)=2," on request","")
&IF(SUM(F2:O2)>0," contains (","")
&IF(P2=1," May Contain","")
&IF(F2>0,"Alcohol","")
&IF(AND(G2=1,H2=0,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=0,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(G2=1,H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(Q2=1," May Contain","")
&IF(G2>0," Dairy","")
&IF(AND(H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=0,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(H2=1,I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(R2=1," May Contain","")
&IF(H2>0," Eggs","")
&IF(AND(I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=0,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(I2=1,J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(S2=1," May Contain","")
&IF(I2>0," Gluten","")
&IF(AND(J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=0,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(J2=1,K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(T2=1," May Contain","")
&IF(J2>0," Soy","")
&IF(AND(K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=0,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(K2=1,L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(U2=1," May Contain","")
&IF(K2>0," Peanuts","")
&IF(AND(L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(L2=1,M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=0,N2=1,O2=0,SUM(F2:O2)>1),",","")
&IF(AND(L2=1,M2=1,N2=0,O2=0,SUM(F2:O2)>1),",","")
&IF(V2=1," May Contain","")
&IF(L2>0," Nuts","")
&IF(AND(M2=1,N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(M2=1,N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(AND(M2=1,N2=0,O2=1,SUM(F2:O2)>1),",","")
&IF(W2=1," May Contain","")
&IF(M2>0," Seeds","")
&IF(AND(N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(AND(N2=1,O2=1,SUM(F2:O2)>1),",","")
&IF(X2=1," May Contain","")
&IF(N2>0," Fish","")
&IF(AND(O2=1,SUM(F2:O2)>1)," &","")
&IF(Y2=1," May Contain","")
&IF(O2>0," Shell Fish","")
&IF(SUM(F2:O2)>0,")","")




Hi Excel gurus, I have a formula that says too long, heres my formula...
=LOOKUP(B141,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32 ,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65, 66,67,68,70,71,72,73,74,75,76,77,78,79,80,81,494,495,496,497,498,499},{"$11.00","$11.00","$9.50","$8 .00","$8.50","$15.00","$12.00","$21.50","$14.50","$21.50","$22.50","$18.00","$21.50","$26.50","$14.0 0","$14.50","$14.50","$19.50","$14.50","$14.50","$19.50","$9.50","$14.50","$28.50","$42.00","$40.00" ,"$14.50","$24.00","$18.00","$24.00","$9.00","$7.50","$42.00","$30.00","$33.50","$45.00","$25.00","$ 23.50","$21.00","$14.50","$7.00","$21.50","$23.50","$33.50","$9.00","$8.50","$21.50","$36.00","$36.0 0","$36.00","$8.00","$33.50","$30.00","$50.00","$58.00","$72.00","$12.00","$6.00","$21.00","$7.00"," $72.00","$30.00","$7.00","$15.00","$10.00","$35.00","$36.00","$40.00","$7.00","$3.00","$8.00","$25.0 0","$3.00","$4.50","$1.50","$6.00","$12.00","18","20","16","$25.00","$28.00","$25.00","$35.00","$25. 00","$18.00"})

Enter PLU here qty entered
81 16
79 18
39 21

the problem is I will have a lot more PLU numbers as time goes. No wI am stuck, please help thanx in advance...


I'm looking to create a mailto link. The issue i'm having is with the 255char limit i believe. I'd like to enter quite a few email addresses so that they'll automatically be entered into outlook when I click on the link. Is there a way to do this? all the addresses come from a different row of a column. I've concatenated them all with a comma delimiter so that formula can be used to enter after the mailto.

What I have:

=hyperlink("mailto:" & =$K$12&IF($K$13<>"",","&$K$13,"")&IF($K$14<>"",","&$K$14,"")&IF($K$15<>"",","&$K$15,"")&IF($K$16<>"" ,","&$K$16,"")&IF($K$17<>"",","&$K$17,"")&IF($K$18<>"",","&$K$18,"")&IF($K$19<>"",","&$K$19,"")&IF($ K$20<>"",","&$K$20,"")&IF($K$21<>"",","&$K$21,"")&IF($K$22<>"",","&$K$22,"")&IF($K$23<>"",","&$K$23, "")&IF($K$35<>"",","&$K$35,"")&IF($K$36<>"",","&$K$36,"")&IF($K$37<>"",","&$K$37,"")&IF($K$38<>"",", "&$K$38,"")&IF($K$39<>"",","&$K$39,"")&IF($K$40<>"",","&$K$40,"")&IF($K$41<>"",","&$K$41,"")&IF($K$4 2<>"",","&$K$42,"")&IF($K$43<>"",","&$K$43,"")&IF($K$44<>"",","&$K$44,"")&IF($K$45<>"",","&$K$45,"") &IF($K$46<>"",","&$K$46,"")&IF($K$47<>"",","&$K$47,"")&IF($K$48<>"",","&$K$48,"")&IF($K$49<>"",","&$ K$49,"")&IF($K$50<>"",","&$K$50,"")&IF($K$51<>"",","&$K$51,"")&IF($K$52<>"",","&$K$52,"")&IF($K$53<> "",","&$K$53,"")&IF($K$54<>"",","&$K$54,"")&IF($K$55<>"",","&$K$55,"")&IF($K$56<>"",","&$K$56,"")&IF ($K$57<>"",","&$K$57,"")&IF($K$58<>"",","&$K$58,"")&IF($K$59<>"",","&$K$59,"")&IF($K$60<>"",","&$K$6 0,""))

I've also tried to do things like display all the emails so that they can be copied to the clipboard after the excel file has been saved and then viewed as a webpage. This runs into the 1024char limit when leaving the concatenated addresses in a cell or even a text box. any fancy idea on a way to accomplish any of this?

Tom


Help!

I have a stupid long forumla that needs to be longer! There must be an easier way of doing and I'm hoping one of you guys can sort it.

=IF((AND(AF4="w",AG4="w")),"0")*(IF((AND(AF4="w",AG4="1c")),"1"),(IF((AND(AF4="w",AG4="1b")),"2",(IF ((AND(AF4="w",AG4="1a")),"3",(IF((AND(AF4="w",AG4="2c")),"w",(IF((AND(AF4="w",AG4="2c")),"4",(IF((AN D(AF4="1c",AG4="w")),"-1",(IF((AND(AF4="1c",AG4="1c")),"0",(IF((AND(AF4="1c",AG4="1a")),"1"),(IF((AND(AF4="1c",AG4="1a"))," 2"),(IF((AND(AF4="1c",AG4="2c")),"3"),(IF((AND(AF4="1c",AG4="2b")),"4"),(IF((AND(AF4="1c",AG4="2a")) ,"5"),(IF((AND(AF4="1b",AG4="w")),"-2"),(IF((AND(AF4="1b",AG4="1c")),"-1"),(IF((AND(AF4="1b",AG4="1b")),"0"),(IF((AND(AF4="1b",AG4="1a")),"1"),(IF((AND(AF4="1b",AG4="2c")) ,"2"),(IF((AND(AF4="1b",AG4="2b")),"3"),(IF((AND(AF4="1b",AG4="2a")),"4"),(IF((AND(AF4="1b",AG4="3c" )),"5"),(IF((AND(AF4="1a",AG4="w")),"-3"),(IF((AND(AF4="1a",AG4="1c")),"-1"),(IF((AND(AF4="1b",AG4="2b")),"3"),(IF((AND(AF4="1b",AG4="2a")),"4"),(IF((AND(AF4="1b",AG4="3c")) ,"5"),(IF((AND(AF4="1a",AG4="w")),"-3"),(IF((AND(AF4="1a",AG4="1c")),"-1"))))))))))))))))))))))))))))))))))

Any ideas?


I've researched a few options but havent been able to make anything work.
Here is the formula I know would would if the limit were not 7....

=IF(C5="AT",K5*9.5%,IF(C5="Cr",K5*9.8%,IF(C5 ="Du",K5*9.8%,IF(C5="OH",K5*9.5%,IF(C5="PWC",K5 *9.5%,IF(C5="Sc",K5*9.8%,IF(C5="Sp",K5*9.8 %,IF(C5="To",K5*9.8%,IF(C5="Tr",K5*9.8%, IF(C5="UTV",K5*9.8%))))))))))

Well I've added a few more values now totaling 14...

I tried this and I get a "false" AND then the correct answer, obviously because the correct value is in the second IF statement.

=IF(C5="ATV S",J5*9.5%,IF(C5="ATV U",J5*9.5%,IF(C5="Cr",J5*9.8%,IF(C5="Cr T",J5*9.8%,IF(C5="DS",J5*9.8%,IF(C5="MX",J5*9.5%,IF(C5="Off", J5*9.5%)))))))&IF(C5="PWC",J5*9.5%,IF(C5="Scr" ,J5*9.8%,IF(C5="Sta",J5*9.8%,IF(C5="Sp",J5 *9.8%,IF(C5="Sp T",J5*9.8%,IF(C5="Tr",J5*9.5%,IF(C5="UTV",J5*9.5%)))))))

The I tried this, but I get "formula contains an error"

=LOOKUP(C5,{"ATV S","ATV U","Cruiser","Cr T","DS","MX","Off","PWC","Scr","St"," Sp","Sp T","Tr","UTV"},{J5*9.5%,J5*9.5%,J5*9.8% ,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%,J5*9.5%,J5*9.8%,J 5*9.8%,J5*9.8%,J5*9.8%,J5*9.5%,J5*9.5%})

I'd REALLY appreciate any help you can give...I'm stumped.

-T


The formula i want to use is the following:

=SUM('KCR 1'!G24="Yes",'KCR 1'!G25="Yes",'KCR 1'!G26="Yes",'KCR 1'!G27="Yes",'KCR 1'!G28="Yes",'KCR 1'!G29="Yes",'KCR 2'!G24="Yes",'KCR 2'!G25="Yes",'KCR 2'!G26="Yes",'KCR 2'!G27="Yes",'KCR 2'!G28="Yes",'KCR 2'!G29="Yes",'KCR 3'!G24="Yes",'KCR 3'!G25="Yes",'KCR 3'!G26="Yes",'KCR 3'!G27="Yes",'KCR 3'!G28="Yes",'KCR 3'!G29="Yes",'KCR 4'!G24="Yes",'KCR 4'!G25="Yes",'KCR 4'!G26="Yes",'KCR 4'!G27="Yes",'KCR 4'!G28="Yes",'KCR 4'!G29="Yes",'KCR 5'!G24="Yes",'KCR 5'!G25="Yes",'KCR 5'!G26="Yes",'KCR 5'!G27="Yes",'KCR 5'!G28="Yes",'KCR 5'!G29="Yes",'KCR 6'!G24="Yes",'KCR 6'!G25="Yes",'KCR 6'!G26="Yes",'KCR 6'!G27="Yes",'KCR 6'!G28="Yes",'KCR 6'!G29="Yes",'KCR 7'!G24="Yes",'KCR 7'!G25="Yes",'KCR 7'!G26="Yes",'KCR 7'!G27="Yes",'KCR 7'!G28="Yes",'KCR 7'!G29="Yes",'KCR 8'!G24="Yes",'KCR 8'!G25="Yes",'KCR 8'!G26="Yes",'KCR 8'!G27="Yes",'KCR 8'!G28="Yes",'KCR 8'!G29="Yes",'KCR 9'!G24="Yes",'KCR 9'!G25="Yes",'KCR 9'!G26="Yes",'KCR 9'!G27="Yes",'KCR 9'!G28="Yes",'KCR 9'!G29="Yes",'KCR 10'!G24="Yes",'KCR 10'!G25="Yes",'KCR 10'!G26="Yes",'KCR 10'!G27="Yes",'KCR 10'!G28="Yes",'KCR 10'!G29="Yes")/60

Obviously very longwinded, so i guess im asking if theres a much shorter way of doing this calculation, or if anyone can recommend a completly different way altogether?!?

Many thanks!!!


Hi,

I am having problems using IF in a formula.

This one works:

=
IF(C5="01","A",
IF(C5="02","B",
IF(C5="03","C",
IF(C5="04","D",
IF(C5="05","E",
IF(C5="06","F",
IF(C5="08","G",
IF(C5="09","H"
))))))))


This one does not work. I get an error message.

=
IF(C5="01","A",
IF(C5="02","B",
IF(C5="03","C",
IF(C5="04","D",
IF(C5="05","E",
IF(C5="06","F",
IF(C5="08","G",
IF(C5="09","H"
IF(C5="11","I",
IF(C5="12","J",
IF(C5="13","K",
IF(C5="14","L",
IF(C5="15","M",
IF(C5="16","N",
))))))))))))))


As you can see, I am just giving more values. Is there a limit for IF to be
used ?

Thanks in advance for your help



Here is a clip of my table"
"a" "b" "c"
"100-104" "004" "304"
"105-108" "005" "305"
"109-113" "004" "304"
"114-115" "005" "305"
"116" "004" "304"
"117-119" "005" "305"
"120-123" "004" "304"
"124-126" "005" "305"

I would like to have an index function search the first column to find what row it corresponds to. I would like to be able to enter "106" and have it reference the row "105-108".

How could I do that?

Bonus help: These are actually the first three numbers of ZIP codes. I would idealls like to have a function where you could input the whole 5 digit zip and it would determine the row. ie. enter "10633" and have it reference the "105-108" row.

Thanks for your help!


I have the following code for a dde input everyhting is working fine except that, it is happening every second as the incoming DDE data is everrchanging and so the calculations always kick start off, every second.
This is making the program take lot of memory and computer resources is there any way to simplify this and yet make the calculations as precise as now? pls help


Code:

Private Sub Worksheet_Calculate()

'This is the code that decides wether to initiate the main program, _
by depending on the value typed in C30 in sheet Prima. _
Make sure that the value in C30 is not directly input but rather referenced to another cell this is in order _
to kick start the CALCULATE event in this case it is referenced to D30
If (Range("C30").Value = 1) Then

If (Range("C2").Value > Range("G2").Value) Then
Range("E2").Value = Range("C2").Value
Range("G2").Value = Range("C2").Value
Else
Range("E2").Value = Range("G2").Value
End If

If (Range("C2").Value < Range("H2").Value) Then
Range("F2").Value = Range("C2").Value
Range("H2").Value = Range("F2").Value
Else
Range("F2").Value = Range("H2").Value
End If

If (Range("C3").Value > Range("G3").Value) Then
Range("E3").Value = Range("C3").Value
Range("G3").Value = Range("C3").Value
Else
Range("E3").Value = Range("G3").Value
End If

If (Range("C3").Value < Range("H3").Value) Then
Range("F3").Value = Range("C3").Value
Range("H3").Value = Range("F3").Value
Else
Range("F3").Value = Range("H3").Value
End If

If (Range("C4").Value > Range("G4").Value) Then
Range("E4").Value = Range("C4").Value
Range("G4").Value = Range("C4").Value
Else
Range("E4").Value = Range("G4").Value
End If

If (Range("C4").Value < Range("H4").Value) Then
Range("F4").Value = Range("C4").Value
Range("H4").Value = Range("F4").Value
Else
Range("F4").Value = Range("H4").Value
End If

If (Range("C5").Value > Range("G5").Value) Then
Range("E5").Value = Range("C5").Value
Range("G5").Value = Range("C5").Value
Else
Range("E5").Value = Range("G5").Value
End If

If (Range("C5").Value < Range("H5").Value) Then
Range("F5").Value = Range("C5").Value
Range("H5").Value = Range("F5").Value
Else
Range("F5").Value = Range("H5").Value
End If

If (Range("C6").Value > Range("G6").Value) Then
Range("E6").Value = Range("C6").Value
Range("G6").Value = Range("C6").Value
Else
Range("E6").Value = Range("G6").Value
End If

If (Range("C6").Value < Range("H6").Value) Then
Range("F6").Value = Range("C6").Value
Range("H6").Value = Range("F6").Value
Else
Range("F6").Value = Range("H6").Value
End If

If (Range("C7").Value > Range("G7").Value) Then
Range("E7").Value = Range("C7").Value
Range("G7").Value = Range("C7").Value
Else
Range("E7").Value = Range("G7").Value
End If

If (Range("C7").Value < Range("H7").Value) Then
Range("F7").Value = Range("C7").Value
Range("H7").Value = Range("F7").Value
Else
Range("F7").Value = Range("H7").Value
End If

If (Range("C8").Value > Range("G8").Value) Then
Range("E8").Value = Range("C8").Value
Range("G8").Value = Range("C8").Value
Else
Range("E8").Value = Range("G8").Value
End If

If (Range("C8").Value < Range("H8").Value) Then
Range("F8").Value = Range("C8").Value
Range("H8").Value = Range("F8").Value
Else
Range("F8").Value = Range("H8").Value
End If

If (Range("C9").Value > Range("G9").Value) Then
Range("E9").Value = Range("C9").Value
Range("G9").Value = Range("C9").Value
Else
Range("E9").Value = Range("G9").Value
End If

If (Range("C9").Value < Range("H9").Value) Then
Range("F9").Value = Range("C9").Value
Range("H9").Value = Range("F9").Value
Else
Range("F9").Value = Range("H9").Value
End If

If (Range("C10").Value > Range("G10").Value) Then
Range("E10").Value = Range("C10").Value
Range("G10").Value = Range("C10").Value
Else
Range("E10").Value = Range("G10").Value
End If

If (Range("C10").Value < Range("H10").Value) Then
Range("F10").Value = Range("C10").Value
Range("H10").Value = Range("F10").Value
Else
Range("F10").Value = Range("H10").Value
End If

If (Range("C11").Value > Range("G11").Value) Then
Range("E11").Value = Range("C11").Value
Range("G11").Value = Range("C11").Value
Else
Range("E11").Value = Range("G11").Value
End If

If (Range("C11").Value < Range("H11").Value) Then
Range("F11").Value = Range("C11").Value
Range("H11").Value = Range("F11").Value
Else
Range("F11").Value = Range("H11").Value
End If

If (Range("C12").Value > Range("G12").Value) Then
Range("E12").Value = Range("C12").Value
Range("G12").Value = Range("C12").Value
Else
Range("E12").Value = Range("G12").Value
End If

If (Range("C12").Value < Range("H12").Value) Then
Range("F12").Value = Range("C12").Value
Range("H12").Value = Range("F12").Value
Else
Range("F12").Value = Range("H12").Value
End If

If (Range("C13").Value > Range("G13").Value) Then
Range("E13").Value = Range("C13").Value
Range("G13").Value = Range("C13").Value
Else
Range("E13").Value = Range("G13").Value
End If

If (Range("C13").Value < Range("H13").Value) Then
Range("F13").Value = Range("C13").Value
Range("H13").Value = Range("F13").Value
Else
Range("F13").Value = Range("H13").Value
End If

If (Range("C14").Value > Range("G14").Value) Then
Range("E14").Value = Range("C14").Value
Range("G14").Value = Range("C14").Value
Else
Range("E14").Value = Range("G14").Value
End If

If (Range("C14").Value < Range("H14").Value) Then
Range("F14").Value = Range("C14").Value
Range("H14").Value = Range("F14").Value
Else
Range("F14").Value = Range("H14").Value
End If


End If

End Sub





I am trying to calculate averages of Key Stage 2 SAT results that have the following outcomes:

"5a","5b","5c","4a","4b","4c","3a","3b","3c","2a","2b","2c","1a","1b","1c"

Each student has three values like:

4c 4b 5b

I am after a formula that will give me an average of all three or more. I have got as far as:

(D4:F4,{"5a","5b","5c","4a","4b","4c","3a","3b","3c","2a","2b","2c","1a","1b","1c"},{15,14,13,12,10, 9,8,7,6,5,4,3,2,1})

This seems to be in the right area, but I cant quite get enough information to get the thing working. I know how to do it with VLOOKUP, but that requires a separate table and is then independent on that, this way it will be easy to share the formula with colleagues.

Help greatly appreciated on this one.


Just curious if I can Say Fill this Combobox with letters from A to AZ, so I do not need to do this:

Code:

Me.cobColumn.List = Array("A", "B", "C", "D", "E", "F", "G", "H", _
        "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", _
        "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", _
        "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", _
        "AY", "AZ")







Example Excel: Example.xlsx.
Below is the function I am using that is not working properly.{ =IF(A11=A10,"",IF(A11=A12=A13=A14=A15,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&";"&C13&"-"&D13&"-"&E13&"("&F13&"-"&G13&")"&";"&C14&"-"&D14&"-"&E14&"("&F14&"-"&G14&")"&" "&C15&"-"&D15&"-"&E15&"("&F15&"-"&G15&")",IF(A11=A12=A13=A14,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&")"&";"&C13&"-"&D13&"-"&E13&"("&F13&"-"&G13&")"&" "&C14&"-"&D14&"-"&E14&"("&F14&"-"&G14&")",IF(A11=A12=A13,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&";"&C13&"-"&D13&"-"&E13&"("&F13&"-"&G13&")"&,IF(A11=A12,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&,C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"))))) }

The function works in most ways except for one, it does not combine more than a few pieces of information. I tried using the =CONCATENATE() function instead of using [&] but had the same result. If I pull out the function that combines data that works properly:
{ C11&"-"&D11&"-"&E11&"("&F11&"-"&G11&")"&";"&C12&"-"&D12&"-"&E12&"("&F12&"-"&G12&")"&";"&C13&"-"&D13&"-"&E13&"("&F13&"-"&G13&")"&";"&C14&"-"&D14&"-"&E14&"("&F14&"-"&G14&")"&" "&C15&"-"&D15&"-"&E15&"("&F15&"-"&G15&")}
Is there an alternative method to solve this problem? Is there someone to change a setting so this actually works?
I have a set of data with an identification code, I need to reduce the data down so there is only one identification code per the rest of the corresponding data. My function checks if there are multiple entries with the same identification code and combines the corresponding data into one cell.

I believe excel is incapable of handling the function.

Simplifying multiple IF-Then while maintaining one macro.

How can i simplify this? This is only 2 of 5, idealy i want this to do exactly what it's doing, checking to see what value is in the cells B5:B9

There has to be an easier way.

Code:

Sub Players()
If Range("B5") = "1" Then
    Range("G16").Value = Range("G16").Value + 1
Else
   If Range("B5") = "2" Then
    Range("G17").Value = Range("G17").Value + 1
    Else
        If Range("B5") = "3" Then
            Range("G18").Value = Range("G18").Value + 1
        Else
            If Range("B5") = "4" Then
            Range("G19").Value = Range("G19").Value + 1
            Else
                If Range("B5") = "5" Then
                Range("G20").Value = Range("G20").Value + 1
                Else
                    If Range("B5") = "6" Then
                    Range("G21").Value = Range("G21").Value + 1
                    Else
                        If Range("B5") = "7" Then
                        Range("G22").Value = Range("G22").Value + 1
                        Else
                            If Range("B5") = "8" Then
                            Range("G23").Value = Range("G23").Value + 1
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End If
If Range("B6") = "1" Then
    Range("G16").Value = Range("G16").Value + 1
Else
   If Range("B6") = "2" Then
    Range("G17").Value = Range("G17").Value + 1
    Else
        If Range("B6") = "3" Then
            Range("G18").Value = Range("G18").Value + 1
        Else
            If Range("B6") = "4" Then
            Range("G19").Value = Range("G19").Value + 1
            Else
                If Range("B6") = "5" Then
                Range("G20").Value = Range("G20").Value + 1
                Else
                    If Range("B6") = "6" Then
                    Range("G21").Value = Range("G21").Value + 1
                    Else
                        If Range("B6") = "7" Then
                        Range("G22").Value = Range("G22").Value + 1
                        Else
                            If Range("B6") = "8" Then
                            Range("G23").Value = Range("G23").Value + 1
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End If
End Sub





Hi,

I need to create a function that can cope with more than 7 if ands, where it
can read what's in cell A1 and what's in cell B1 then spit out a defined
value in C1. For example,

If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on for
combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and ee.
My formula below works but only gets me up to bc.

=IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b"),"2",IF(AND(A1="a",B1="c"),"3",IF(AND(A1="a",B1="d") ,"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b"),"2",IF(AND(A1="b",B1="c"),"3",)))))))

I've found some tricks to get around the 7-limit IF functions but can't seem
to make them work for my nest IF(AND) function.

I hope this makes sense. Any help would be most appreciated.

thanks.

Rohan.




Hi Everyone

I was wondering if any could please tell me what I am doing wrong with the following, its giving me an error.

=IF(AND(R8="",P8="",N8="",L8="",I8=""),"",IF(AND(R8="",P8="",N8="",L8="",I8<>""),"Internal Review",IF(AND(R8="",P8="",N8="",L8<>"",I8<>""),"CfH Review",IF(AND(R8="",P8="",N8<>"",L8<>"",I8<>""),"CSC Update",IF(AND(R8="",P8<>"",N8<>"",L8<>"",I8<>""),"CfH Approval",IF(AND(R8<>"",P8<>"",N8<>"",L8<>"",I8<>""),"Complete",IF(AND(R8<>"",P8<>"",N8<>"",L8<>"",I 8=""),"Missing Date for Internal Review",IF(AND(R8<>"",P8<>"",N8<>"",L8="",I8<>""),"Missing Date for CfH Review Submission",IF(AND(R8<>"",P8<>"",N8="",L8<>"",I8<>""),"Missing Date for CfH Feedback",IF(AND(R8<>"",P8="",N8<>"",L8<>"",I8<>""),"Missing Date for Re-Issue to CfH",IF(AND(R8<>"",P8="",N8="",L8="",I8=""),"Missing Some Actual Dates",IF(AND(R8<>"",P8<>"",N8="",L8="",I8=""),"Missing Some Actual Dates"))))

I would be grateful if you could help.

(Ignore spaces in formula)

Many Thanks.




)="Robert","Green",IF((B$10)="Nerida","Pink",IF((B$10)="Jack","Black",IF((B$10)="Max","Red",IF((B$10)="Jim","Blue",IF((B$10)="Chris","Orange",IF((B$10)="Sarah","Yellow",IF((B$10)="Rose","Purple",""))))))))

I need to add: IF((B$10)="Wade","Aqua" and IF((B$10)="Steve","White" yet.

How can I do this.



I am using the following formual but the results will not populate in currency format is there a quick solution to correct this? What am I missing?

=IF(OR(Y4="6to12"),"$170.00",IF(OR(Y4="12to18"),"$210.00",IF(OR(Y4="18to24"),"$315.00",IF(OR(Y4="24to30"),"$315.00",IF(OR(Y4="30to36"),"$690.00",IF(OR(Y4="36to42"),"$790.00",IF(OR(Y4="42to48"),"$950.00",IF(OR(Y4="48to54"),"$1200.00",IF(OR(Y4="grtr54"),"$1500.00",IF(OR(Y4="6to1248HR"),"$225.00",IF(OR(Y4="12to1848HR"),"$375.00",IF(OR(Y4="18to2448HR"),"$585.00",IF(OR(Y4="24to3048HR"),"$700.00",IF(OR(Y4="30to3648HR"),"$950.00",IF(OR(Y4="36to4248HR"),"$1250.00",IF(OR(Y4="42to4848HR"),"$1500.00",IF(OR(Y4="48to5448HR"),"$1900.00",IF(OR(Y4="grtr5448HR"),"$2500.00"))))))))))))))))))

Hello all -

I'm trying to write a formula in a macro that will look at the value in J2 and based on that value, write out a new value in K2. Here's what I've got. It works until I add Camper/Trailer to the mix.

Anyone have any idea why this is erroring out on me?

Code:

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]=""AUTOP"",""AUTO"",IF(RC[-1]=""Watercraft"",""BOAT"",IF(RC[-1]=""PHOME"",""HOME"",IF(RC[-1]=""DFIRE"",""DWF"",IF(RC[-1]=""EQ"",""EQU"",IF(RC[-1]=""PUMBR"",""UMB"",IF(RC[-1]=""Camper/Trailer"",""TRAV"",IF(RC[-1]=""Motorcycle"",""CYCL"",IF(RC[-1]=""INMRP"",""INLM"",RC[-1])))))))))"







Dear all,

1) the code is starting from Range("A1").
i want it should run it on any cell of the sheet.

2) the value is also set for sr. no 1 to 20.
i want it should ask me like "how many items in table?"

3) the same code runs only one time in sheet.
i want it should runs for n times, means it should ask me like "how many tables?"

thanks in advance.

following is the code:

Sub Evaluation()
'
Range("A3").FormulaR1C1 = "Sr"
Range("A4").FormulaR1C1 = "No"
Range("A5").FormulaR1C1 = "1"
Range("A6").FormulaR1C1 = "2"
Range("A7").FormulaR1C1 = "3"
Range("A8").FormulaR1C1 = "4"
Range("A9").FormulaR1C1 = "5"
Range("A10").FormulaR1C1 = "6"
Range("A11").FormulaR1C1 = "7"
Range("A12").FormulaR1C1 = "8"
Range("A13").FormulaR1C1 = "9"
Range("A14").FormulaR1C1 = "10"
Range("A15").FormulaR1C1 = "11"
Range("A16").FormulaR1C1 = "12"
Range("A17").FormulaR1C1 = "13"
Range("A18").FormulaR1C1 = "14"
Range("A19").FormulaR1C1 = "15"
Range("A20").FormulaR1C1 = "16"
Range("A21").FormulaR1C1 = "17"
Range("A22").FormulaR1C1 = "18"
Range("A23").FormulaR1C1 = "19"
Range("A24").FormulaR1C1 = "20"
Range("B1").FormulaR1C1 = "Value1 "
Range("B3").FormulaR1C1 = "Name"
Range("B28").FormulaR1C1 = "Value3"
Range("C28").FormulaR1C1 = "=IF(ISERROR(R[-3]C/RC[8]*100),"""",R[-3]C/RC[8]*100)"
Range("D5:D24").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D28").FormulaR1C1 = "Value8"
Range("E4").FormulaR1C1 = "Value9"
Range("G4").FormulaR1C1 = "Value13"
Range("G5:G24").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("H4").FormulaR1C1 = "Value14"
Range("H5:H24").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("I3").FormulaR1C1 = "Value15"
Range("I4").FormulaR1C1 = "Value16"
Range("I5:I24").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I28").FormulaR1C1 = "Value17"
Range("J4").FormulaR1C1 = "Value18"
Range("J5:J24").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("K3").FormulaR1C1 = "Value19"
Range("K4").FormulaR1C1 = "Value20"
Range("K5").FormulaR1C1 = _
"=IF(ISERROR(RC[-8]/R[20]C[-8]),"""",RC[-8]/R[20]C[-8])"
Range("K6").FormulaR1C1 = _
"=IF(ISERROR(R[-1]C*RC[-2]/R[-1]C[-8]),"""",R[-1]C*RC[-2]/R[-1]C[-8])"
Range("K7").FormulaR1C1 = _
"=IF(ISERROR(R[-2]C*RC[-2]/R[-2]C[-8]),"""",R[-2]C*RC[-2]/R[-2]C[-8])"
Range("K8").FormulaR1C1 = _
"=IF(ISERROR(R[-3]C*RC[-2]/R[-3]C[-8]),"""",R[-3]C*RC[-2]/R[-3]C[-8])"
Range("K9").FormulaR1C1 = _
"=IF(ISERROR(R[-4]C*RC[-2]/R[-4]C[-8]),"""",R[-4]C*RC[-2]/R[-4]C[-8])"
Range("K10").FormulaR1C1 = _
"=IF(ISERROR(R[-5]C*RC[-2]/R[-5]C[-8]),"""",R[-5]C*RC[-2]/R[-5]C[-8])"
Range("K11").FormulaR1C1 = _
"=IF(ISERROR(R[-6]C*RC[-2]/R[-6]C[-8]),"""",R[-6]C*RC[-2]/R[-6]C[-8])"
Range("K12").FormulaR1C1 = _
"=IF(ISERROR(R[-7]C*RC[-2]/R[-7]C[-8]),"""",R[-7]C*RC[-2]/R[-7]C[-8])"
Range("K13").FormulaR1C1 = _
"=IF(ISERROR(R[-8]C*RC[-2]/R[-8]C[-8]),"""",R[-8]C*RC[-2]/R[-8]C[-8])"
Range("K14").FormulaR1C1 = _
"=IF(ISERROR(R[-9]C*RC[-2]/R[-9]C[-8]),"""",R[-9]C*RC[-2]/R[-9]C[-8])"
Range("K15").FormulaR1C1 = _
"=IF(ISERROR(R[-10]C*RC[-2]/R[-10]C[-8]),"""",R[-10]C*RC[-2]/R[-10]C[-8])"
Range("K16").FormulaR1C1 = _
"=IF(ISERROR(R[-11]C*RC[-2]/R[-11]C[-8]),"""",R[-11]C*RC[-2]/R[-11]C[-8])"
Range("K17").FormulaR1C1 = _
"=IF(ISERROR(R[-12]C*RC[-2]/R[-12]C[-8]),"""",R[-12]C*RC[-2]/R[-12]C[-8])"
Range("K18").FormulaR1C1 = _
"=IF(ISERROR(R[-13]C*RC[-2]/R[-13]C[-8]),"""",R[-13]C*RC[-2]/R[-13]C[-8])"
Range("K19").FormulaR1C1 = _
"=IF(ISERROR(R[-14]C*RC[-2]/R[-14]C[-8]),"""",R[-14]C*RC[-2]/R[-14]C[-8])"
Range("K20").FormulaR1C1 = _
"=IF(ISERROR(R[-15]C*RC[-2]/R[-15]C[-8]),"""",R[-15]C*RC[-2]/R[-15]C[-8])"
Range("K21").FormulaR1C1 = _
"=IF(ISERROR(R[-16]C*RC[-2]/R[-16]C[-8]),"""",R[-16]C*RC[-2]/R[-16]C[-8])"
Range("K22").FormulaR1C1 = _
"=IF(ISERROR(R[-17]C*RC[-2]/R[-17]C[-8]),"""",R[-17]C*RC[-2]/R[-17]C[-8])"
Range("K23").FormulaR1C1 = _
"=IF(ISERROR(R[-18]C*RC[-2]/R[-18]C[-8]),"""",R[-18]C*RC[-2]/R[-18]C[-8])"
Range("K24").FormulaR1C1 = _
"=IF(ISERROR(R[-19]C*RC[-2]/R[-19]C[-8]),"""",R[-19]C*RC[-2]/R[-19]C[-8])"
Range("K25").FormulaR1C1 = _
"=IF(ISERROR(RC[-8]*R[-20]C/R[-20]C[-8]),"""",RC[-8]*R[-20]C/R[-20]C[-8])"
Range("K28").FormulaR1C1 = _
"=IF(ISERROR(RC[-3]*R[-23]C[-8]/RC[-6]),"""",RC[-3]*R[-23]C[-8]/RC[-6])"
Range("L3").FormulaR1C1 = "Value21"
Range("L4").FormulaR1C1 = "Value28"
Range("L5").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[-1]),"""",RC[-1]/RC[-1])"
Range("L6").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-1]C[-1]),"""",RC[-1]/R[-1]C[-1])"
Range("L7").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-2]C[-1]),"""",RC[-1]/R[-2]C[-1])"
Range("L8").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-3]C[-1]),"""",RC[-1]/R[-3]C[-1])"
Range("L9").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-4]C[-1]),"""",RC[-1]/R[-4]C[-1])"
Range("L10").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-5]C[-1]),"""",RC[-1]/R[-5]C[-1])"
Range("L11").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-6]C[-1]),"""",RC[-1]/R[-6]C[-1])"
Range("L12").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-7]C[-1]),"""",RC[-1]/R[-7]C[-1])"
Range("L13").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-8]C[-1]),"""",RC[-1]/R[-8]C[-1])"
Range("L14").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-9]C[-1]),"""",RC[-1]/R[-9]C[-1])"
Range("L15").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-10]C[-1]),"""",RC[-1]/R[-10]C[-1])"
Range("L16").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-11]C[-1]),"""",RC[-1]/R[-11]C[-1])"
Range("L17").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-12]C[-1]),"""",RC[-1]/R[-12]C[-1])"
Range("L18").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-13]C[-1]),"""",RC[-1]/R[-13]C[-1])"
Range("L19").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-14]C[-1]),"""",RC[-1]/R[-14]C[-1])"
Range("L20").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-15]C[-1]),"""",RC[-1]/R[-15]C[-1])"
Range("L21").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-16]C[-1]),"""",RC[-1]/R[-16]C[-1])"
Range("L22").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-17]C[-1]),"""",RC[-1]/R[-17]C[-1])"
Range("L23").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-18]C[-1]),"""",RC[-1]/R[-18]C[-1])"
Range("L24").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-19]C[-1]),"""",RC[-1]/R[-19]C[-1])"
Range("M3").FormulaR1C1 = "Value22"
Range("N4").FormulaR1C1 = "Value27"
Range("N5:N24").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N25").FormulaR1C1 = _
"=IF(ISERROR(SUM(R[-20]C:R[-1]C)),"""",SUM(R[-20]C:R[-1]C))"
Range("N28").FormulaR1C1 = _
"=IF(ISERROR(R[-3]C[-11]/R[-23]C[-11]),"""",R[-3]C[-11]/R[-23]C[-11])"
End Sub

Hey folks,
I am kind of a newb to Excel. I have come up with this formula that works (I was really supprised when I hit enter and it actually worked) but I was wondering if theres any way to simplify it..

Code:

=
IF(AND(E3<>"",E4="",E5="",E6="",E7="",E8="",E9="",E10="",E11="",E12="",E13="",E14="",E15="",E16="",E17="",E18="",E19="",E20="",E21="",E22="",E23="",E24="",E25="",E26="",E27=""),D3,"NOTHING FOLLOWS")


Basically it checks E3 to make sure something is there AND that E4 through E27 are blank... if those two conditions are met then D3 becomes the output... otherwise and error (nothing follows) is given.

Is there som way to shorten this for the blank cells? something like ..

Code:

=IF(AND(E3<>"",E4:E27=""),D3,"NOTHING FOLLOWS")


I have tried doing that but it just says NAME? in the cell when I hit enter... is this the right format or is there an easier way to do what I want?

Thanks,
-LF


I need help condensing this repeating code. Thanks.

Code:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/1/2007 by Janet
Sheets("H").Select
Range("c3").Select
ActiveCell.Resize(, 100) = Array("=data!f4", "=data!f5", "=data!f6", "=data!f7", "=data!f8", "=data!f9", "=data!f10", "=data!f11", "=data!f12", "=data!f13", _
                                "=data!f15", "=data!f16", "=data!f17", "=data!f18", "=data!f19", "=data!f20", "=data!f21", "=data!f22", "=data!f23", "=data!f24", _
                                "=data!f25", "=data!f26", "=data!f27", "=data!f28", "=data!f29", "=data!f30", "=data!f31", "=data!f32", "=data!f33", "=data!f34", _
                                "=data!f35", "=data!f36", "=data!f37", "=data!f38", "=data!f39", "=data!f40", "=data!f41", "=data!f42", "=data!f43", "=data!f44", _
                                "=data!f45", "=data!f46", "=data!f47", "=data!f48", "=data!f49", "=data!f50", "=data!f51", "=data!f52", "=data!f53", "=data!f54", _
                                "=data!f55", "=data!f56", "=data!f57", "=data!f58", "=data!f59", "=data!f60", "=data!f61", "=data!f62", "=data!f63", "=data!f64", _
                                "=data!f65", "=data!f66", "=data!f67", "=data!f68", "=data!f69", "=data!f70", "=data!f71", "=data!f72", "=data!f73", "=data!f74", _
                                "=data!f75", "=data!f76", "=data!f77", "=data!f78", "=data!f79", "=data!f80", "=data!f81", "=data!f82", "=data!f83", "=data!f84", _
                                "=data!f85", "=data!f86", "=data!f87", "=data!f88", "=data!f89", "=data!f90", "=data!f91", "=data!f92", "=data!f93", "=data!f94", _
                                "=data!f95", "=data!f96", "=data!f97", "=data!f98", "=data!f99", "=data!f100", "=data!f101", "=data!f102", "=data!f103", "=data!f104")
Sheets("L").Select
Range("c3").Select
ActiveCell.Resize(, 100) = Array("=data!g4", "=data!g5", "=data!g6", "=data!g7", "=data!g8", "=data!g9", "=data!g10", "=data!g11", "=data!g12", "=data!g13", _
                                "=data!g15", "=data!g16", "=data!g17", "=data!g18", "=data!g19", "=data!g20", "=data!g21", "=data!g22", "=data!g23", "=data!g24", _
                                "=data!g25", "=data!g26", "=data!g27", "=data!g28", "=data!g29", "=data!g30", "=data!g31", "=data!g32", "=data!g33", "=data!g34", _
                                "=data!g35", "=data!g36", "=data!g37", "=data!g38", "=data!g39", "=data!g40", "=data!g41", "=data!g42", "=data!g43", "=data!g44", _
                                "=data!g45", "=data!g46", "=data!g47", "=data!g48", "=data!g49", "=data!g50", "=data!g51", "=data!g52", "=data!g53", "=data!g54", _
                                "=data!g55", "=data!g56", "=data!g57", "=data!g58", "=data!g59", "=data!g60", "=data!g61", "=data!g62", "=data!g63", "=data!g64", _
                                "=data!g65", "=data!g66", "=data!g67", "=data!g68", "=data!g69", "=data!g70", "=data!g71", "=data!g72", "=data!g73", "=data!g74", _
                                "=data!g75", "=data!g76", "=data!g77", "=data!g78", "=data!g79", "=data!g80", "=data!g81", "=data!g82", "=data!g83", "=data!g84", _
                                "=data!g85", "=data!g86", "=data!g87", "=data!g88", "=data!g89", "=data!g90", "=data!g91", "=data!g92", "=data!g93", "=data!g94", _
                                "=data!g95", "=data!g96", "=data!g97", "=data!g98", "=data!g99", "=data!g100", "=data!g101", "=data!g102", "=data!g103", "=data!g104")
Sheets("C").Select
Range("c3").Select
ActiveCell.Resize(, 100) = Array("=data!d4", "=data!d5", "=data!d6", "=data!d7", "=data!d8", "=data!d9", "=data!d10", "=data!d11", "=data!d12", "=data!d13", _
                                "=data!d15", "=data!d16", "=data!d17", "=data!d18", "=data!d19", "=data!d20", "=data!d21", "=data!d22", "=data!d23", "=data!d24", _
                                "=data!d25", "=data!d26", "=data!d27", "=data!d28", "=data!d29", "=data!d30", "=data!d31", "=data!d32", "=data!d33", "=data!d34", _
                                "=data!d35", "=data!d36", "=data!d37", "=data!d38", "=data!d39", "=data!d40", "=data!d41", "=data!d42", "=data!d43", "=data!d44", _
                                "=data!d45", "=data!d46", "=data!d47", "=data!d48", "=data!d49", "=data!d50", "=data!d51", "=data!d52", "=data!d53", "=data!d54", _
                                "=data!d55", "=data!d56", "=data!d57", "=data!d58", "=data!d59", "=data!d60", "=data!d61", "=data!d62", "=data!d63", "=data!d64", _
                                "=data!d65", "=data!d66", "=data!d67", "=data!d68", "=data!d69", "=data!d70", "=data!d71", "=data!d72", "=data!d73", "=data!d74", _
                                "=data!d75", "=data!d76", "=data!d77", "=data!d78", "=data!d79", "=data!d80", "=data!d81", "=data!d82", "=data!d83", "=data!d84", _
                                "=data!d85", "=data!d86", "=data!d87", "=data!d88", "=data!d89", "=data!d90", "=data!d91", "=data!d92", "=data!d93", "=data!d94", _
                                "=data!d95", "=data!d96", "=data!d97", "=data!d98", "=data!d99", "=data!d100", "=data!d101", "=data!d102", "=data!d103", "=data!d104")
End Sub





Please, help me, guys.

I'm trying to introduce this formula:

=LOOKUP(B10,{20,20.25,20.5,20.75,21,21.25,21.5,21.75,22,22.25,22.5,22.75,23,23.25,23.5,23.75,24,24.2 5,24.5,24.75,25,25.25,25.5,25.75,26,26.25,26.5,26.75,27,27.25,27.5,27.75,28,28.25,28.5,28.75,29,29.2 5,29.5,29.75,30,30.25,30.5,30.75,31,31.25,31.5,31.75,32,32.25,32.5,32.75,33,33.25,33.5,33.75,34,34.2 5,34.5,34.75,35,35.25,35.5,35.75,36,36.25,36.5,36.75,37,37.25,37.5,37.75,38,38.25,38.5,38.75,39,39.2 5,39.5,39.75,40,40.25,40.5,40.75,41,41.25,41.5,41.75,42,42.25,42.5,42.75,43},{"8 5/8","8 3/4","8 7/8","9","9 1/8","9 1/4","9 3/8","9 1/2","9 5/8","9 3/4","9 7/8","10","10 1/8","10 1/4","10 3/8","10 1/2","10 5/8","10 3/4","10 7/8","11","11 1/8","11 1/4","11 3/8","11 1/2","11 5/8","11 3/4","11 7/8","12","12 1/8","12 1/4","12 3/8","12 1/2","12 5/8","12 3/4","12 7/8","13","13 1/8","13 1/4","13 3/8","13 1/2","13 5/8","13 3/4","13 7/8","14","14 1/8","14 1/4","14 3/8","14 1/2","14 5/8","14 3/4","14 7/8","15","15 1/8","15 1/4","15 3/8","15 1/2","15 5/8","15 3/4","15 7/8","16","16 1/8","16 1/4","16 3/8","16 1/2","16 5/8"}) etc etc

All I need is to insert more data in this formula. But... it's too long. Any sugestion?


Hi,

Using Excel 2002, SP3 on a Windows XP Machine.

I am trying to do a fairly simple thing that required a huge formula.

I have "labels" in column C3, on another worksheet (called 'codes') I have a list of new 'codes' for the things in C3. Each value in C3 can be one of 50. All 50 are represented in the 'codes'. There are approximately 1000 cells in C3. I would rather not do this by hand since the codes are things like "000000000000000000000000000100000000000"

This is my current formula:

=IF((C3="CC-A01"),codes!B1,IF((C3="CC-A02"),codes!B2,IF((C3="CC-A03"),codes!B3,IF((C3="CC-A04"),codes!B4,IF((C3="CC-A05"),codes!B5,IF((C3="CC-A06"),codes!B6,IF((C3="CC-A07"),codes!B7,IF((C3="CC-A08"),codes!B8,IF((C3="CC-A09"),codes!B9,IF((C3="CC-A10"),codes!B10,IF((C3="CC-A11"),codes!B11,IF((C3="CC-A12"),codes!B12,IF((C3="CC-A13"),codes!B13,IF((C3="CC-A14"),codes!B14,IF((C3="CC-A15"),codes!B15,IF((C3="CC-A16"),codes!B16,IF((C3="CC-A17"),codes!B17,IF((C3="CC-A18"),codes!B18,IF((C3="CC-A19"),codes!B19,IF((C3="CC-A20"),codes!B20,IF((C3="CC-A21"),codes!B21,IF((C3="CC-A22"),codes!B22,IF((C3="CC-A23"),codes!B23,IF((C3="CC-A24"),codes!B24,IF((C3="CC-A25"),codes!B25,IF((C3="CC-A26"),codes!B26,IF((C3="CC-A27"),codes!B27,IF((C3="CC-A28"),codes!B28,IF((C3="CC-A29"),codes!B29,IF((C3="CC-A30"),codes!B30,IF((C3="CC-A31"),codes!B31,IF((C3="CC-A32"),codes!B32,IF((C3="CC-A33"),codes!B33,IF((C3="CC-A34"),codes!B34,IF((C3="CC-A35"),codes!B35,IF((C3="CC-A36"),codes!B36,IF((C3="CC-A37"),codes!B37,IF((C3="CC-A38"),codes!B38,IF((C3="CC-A39"),codes!B39,IF((C3="CC-A40"),codes!B40,IF((C3="CC-A41"),codes!B41,IF((C3="CC-A42"),codes!B42,IF((C3="CC-A43"),codes!B43,IF((C3="CC-A44"),codes!B44,IF((C3="CC-A45"),codes!B45,IF((C3="CC-A46"),codes!B46,IF((C3="CC-A47"),codes!B46,IF((C3="CC-A48"),codes!B48,IF((C3="CC-A49"),codes!B49,IF((C3="CC-A50"),codes!B50,""))))))))))))))))))))))))))))))))))))))))))))))))))

Any ideas would be appreciated!


hi,

i want to associate a lookup function to a specific cell through command button submit like this

.sheets("Data").Range("H4").Formula = AQL

where

AQL = "=LOOKUP(G4"+ " ;{""A"";""B"";""C"";""D"";""E"";""F"";""G"";""H"";""J"";""K"";""L"";""M"";""N"";""P"";""Q""};{0;0;0; 0;0;0;0;1;1;2;3;5;7;10;14})"


but an error occur when i run the macro

Rui Azenha


The numbers in B3=0, C3=2 and D3=0 the formula I'm using is:

=IF(B3="0",1,IF(B3="5",1,IF(B3="1",2,IF(B3="6",2,IF(B3="2",3,IF(B3="7",3,IF(B3="3",4,IF(B3="8",4,IF( B3="4",5,IF(B3="9",5))))))))))

For cell "B3" the formula works

Now for both the next two cells C3 & D3 the formula is:

=IF(C3="0",1,IF(C3="5",1,IF(C3="1",2,IF(C3="6",2,IF(C3="2",3,IF(C3="7",3,IF(C3="3",4,IF(C3="8",4,IF( C3="4",5,IF(C3="9",5))))))))))

=IF(D3="0",1,IF(D3="5",1,IF(D3="1",2,IF(D3="6",2,IF(D3="2",3,IF(D3="7",3,IF(D3="3",4,IF(D3="8",4,IF( D3="4",5,IF(D3="9",5))))))))))

Doesn't work and displays "FALSE" ?????

I don't the luxury of wasting time, the formula should work but I don't understand why it isn't, could anyone please help me. (Probably just stressed and tried.)

Thanks ahead of time,