|
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
More than 7 Nested IF functions
- See what to do if you must have more than 7 nested IF functions: 1) Use the Ampersand, Use different cells, buy Excel 2007, use VLOOKUP, or Use a Bool ...
Nested IF & Short Term Loans
- Nested IF example. See how to use the TEXT function, concatenation using the Ampersand (&) symbol and the IF function to create labels and loan ca ...
Helpful Excel Macros
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
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
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 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")
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 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.
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.
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])))))))))"
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
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
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,
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
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!
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,
I want to copy over text from various fields into a single field.
Here is the only solution (my limited time and my limited programing savy) I
have found so far. Is there a way to minimise or shorten this
equation/formula:
=A3&" "&A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10&" "&A11&" "&A12&"
"&A13&" "&A14&" "&A15&" "&A16&" "&A17&" "&A18&" "&A19&" "&A20&" "&A21&"
"&A22&" "&A23&" "&A24&" "&A25&" "&A26&" "&A27
In essense, I want to tell a cell to capture the info in a3 through a27 with
a space inbetween data/value returned.
Thanks
I am unable understand the following code. Please explain me about this chain formula and explain about the purpose these many quatation marks.
str = Sheets("Sample1").Range("B" & i).Value
Sheets("Sample3").Range("C" & i).Value = "=IF(ISERROR(FIND(" & """-""" & "," & """" & _
str & """" & ",FIND(" & """-""" & "," & """" & str & """" & ",FIND(" & """-""" & "," & """" & _
str & """" & ",1)+1)+1)) = TRUE,"""", MID(" & """" & str & """" & ",(FIND(" & """-""" & "," & """" & _
str & """" & " ,FIND(" & """-""" & "," & """" & str & """" & ",1)+1))+1,1))"
Thanks&Regards
Ramesh
I have the following function that I am using to replace letter grades with
grade points. Excel will not allow me to enter more than seven nested
functions. Is there another way?
=IF(D3="a","4",IF(D3="b+","3.5",
IF(D3="b","3",IF(D3="c+","2.5",IF(D3="C","2",IF(D3="D+","1.5",IF(D3="D","1",IF(D3="F","0"))))))))
I have tracker I'm using for weapons qualification but I know ther has to be a smater way to do it. I have a formula that is giving "too many arguments for this function" error. Basically the formula is supposed to determine the type of weapon in "D2" from a pull down menu it then reads the score in "F2" and compares it to the criteria for a "rating".
=IF(OR(D2="M4T",D2="M16T"),IF(F2>35,"EXP",IF(F2>29,"SS",IF(F2>22,"MM",IF(F2>=1,"UNQUAL","")))),IF(D2 ="M4P",D2="M16P"),IF(F2>37,"EXP",IF(F2>32,"SS",IF(F2>25,"MM",IF(F2>=1,"UNQUAL","")))),IF(D2="M249",I F(F2>182,"EXP",IF(F2>158,"1st",IF(F2>133,"2nd",IF(F2>=1,"UNQUAL","")))),IF(D2="M240B",IF(F2>206,"EXP ",IF(F2>180,"1st",IF(F2>158,"2ND",IF(F2>=1,"UNQUAL","")))),IF(D2="M203",IF(F2>80,"EXP",IF(F2>70,"1st ",IF(F2>60,"2ND",IF(F2>=1,"UNQUAL","")))),IF(D2="M9",IF(F2>159,"EXP",IF(F2>119,"SS",IF(F2>79,"MM",IF (F2>=1,"UNQUAL","")))),IF(D2="M2 .50 CAL",IF(F2>195,"EXP",IF(F2>173,"1ST",IF(F2>152,"2ND",IF(F2>=1,"UNQUAL","")))),"")))))
I would like to be able to do the same thing with either more simple formula or a type of lookup wich is not my strong suit I want to also add more weapons type..
|
|
