Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Conditional If Statement With Percent

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

Hi everyone-

I'm trying to figure out the formula that will place the word "yes" if cell A is less than 25% of cell C.

For example, "Yes" if cell A1 (Quantity in stock) is less than 25% of C1 (Quantity in reorder), or "No" if this condition is no met.

Values:

A1-325
B1-??
C1-1000

View Answers     

Similar Excel Tutorials

Increment a Value Every X Number of Rows in Excel
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...
Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
Break out of or Exit Different Types of Loops in VBA Macros in Excel
How to Exit, End, or Break out of loops in Excel. This tutorial includes breaking out of While, Do, and For loops. ...
Automatically Shade Every Other Row When You Add Data in Excel
How to make Excel automatically add row shading to every other row when you add data to the spreadsheet. This does ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
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
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

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




I'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.

What I want to do is have three columns that interact with eachother.

1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status

What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".

So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".

Any help would be appreciated.


Hello All,

I have a inventory question that I need help with.

Lets say cell:
A1=Quantity in stock (person enters)
B1=Quantity used per day (person enters)
C1=Quantity used per week (person enters)
D1=Reorder Level (Person enters)
E1=Status (Condition formula)

What I need formulas for are
1. Subtract quantity used per day(b1) or quantity used per week (C1) from quantity in stock (a1). Then I want the total of this (a1-b1 or c1) to replace the quantity in A1 as the new quantity in stock.

After the calculation is completed I am looking for a formula that then clears the cells (B1 or C1) for the next entry (the next day/week).

2. A status formula that will state when A1 (quantity stock) is above the reorder level (d1) the cell (e1) will be green and say In Stock, otherwise if a1 is less than or equal to d1 then state Need to order, and turn cell e1 yellow.

If anyone can help I would really appreciate it!!! I would even pay for your time invested if I need to!!

Thanks!!


Hi everyone, I have a rather large excel work book I set up for inventory control/ordering purposes, I have over 10,000 formulas in this work book. I have spent hours trying to get this particular IF statement to work with no luck. Any ideas?

exp:
Cell A-1 = 10 (Quantity in stock)
Cell A-2 = 15 (Quantity needed)
Cell A-3 = Order amount

I want cell A-3 to calculate the amount for me to order. Some times I have more in stock then the quantity needed. If there is more in stock then needed I want cell A-3 to = "0" not a negative number, if there is less in stock I need it to calculate the amount needed.

Here is what I have so far: =IF(A1>A2,0,A2-A1)

Thanks for the help


Hi everyone,
I have a nested IF function I'm having issues with. My workbook is laid out as follows:

Sheet1 contains two drop-down lists, in Cells E2 and F2, and a number field, G2.

The list in E2 may have the following values: "Condition 1" or "Condition 2". The list in F2 may have the following values: "Condition 3" or "Condition 4". Both are text fields.

I need to evaluate for the appearance of the following combinations: ("Condition 1" AND "Condition 3") OR ("Condition 1 AND "Condition 4"), ("Condition 2" AND "Condition 3") OR ("Condition 2 AND "Condition 4").

The results of the conditions use the value of the free-form number field, G2, and manipulate it referencing various values on Sheet3. I'm confident the results portion (the manipulations of G2) are correct, it's the formatting of the IF statement I'm having problems with.

I created the following statement to try to accomplish this:

=IF(AND(E2="Condition 1", F2="Condition 3"), G2*Sheet3!C1, IF(AND(E2="Condition 1", F2="Condition 4"),(G2*Sheet3!C1)*Sheet3!F4, IF(AND(E2="Condition 2", F2="Condition 3")(G2*Sheet3!C1)*Sheet3!F2 IF(AND(E2="Condition 2", F2="Condition 4") (((G2*Sheet3!C1)*Sheet3!F2)*Sheet3!F4), 0))))

However, it doesn't work, Excel tells me "The formula you typed contains an error." I've been searching this site but can't seem to find a readily available answer.

I realize I could be way off, or it could be an easy fix -- I'm new to more complex logic in Excel, so I'll thank you in advance for your patience with the newb.

Can anyone give me a correctly-formatted version of that statement, or point me to where I need to look to figure it out myself?

Many thanks,

Rich


in first cell (A1) i have a code: 553282300000
in second cell (B1) quantity: 50
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 160.

Here is the example:

Code........................Quantity

553282300000............... 50........................55
553290240040..............160........................58
553480480020..............420........................7E
553492300040............... 20
583182300060SMA.......100
585281100060SMA........ 80
7E1382300000................15
7E1682300000................38
7E3684000000................73
7E3684000002................57


What i have to do is to sum the quantity of the products that begin with 55 in "C1", quantity of the products that begin with 58 in "C2" and the quantity of products that begin with 7E in "C3".

NOTE: OP requires VBA solution - WF solution provided previously he related thread: http://www.excelforum.com/excel-work...st-column.html
(D.O)


This is killing me. I've read and read and read. I can't figure this one out. I konw it is super simple.

I have 2 columns. "RE ORDER LEVEL" and "IN STOCK" I want the re order level to turn red when the in stock goes below the re order level. I can make the formula for 1 cel but I can't seem to specify a range. Every time I try to specify the range it says I can't and I should use something like =SUM I don't want a sum I want it to compare the in stock quantity with the reorder level and if instock is less than reorder turn re order red but I need to apply the same formula for 300 rows quickly. Can someone tell me where I'm going wrong here??? I'm sure this is a simple formula.


THANKS SOOO MUCH!


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,


Hi.

I am trying to put both item and quantity in the same column.
For example:

A1 has "5a"
A2 has "3b"
A3 has "4c"
A4 has "3a"
A5 has "2a"

I would like to make a formula in another cell to contain the sum of all "A"s.
So for the example above I would like the formula to return a value of 8.

Is that possible?


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


Hi, i hope someone has the answer to my question.
what im trying to do is:
make a formula that will display a word in its cell & another word in a
different cell.
meaning: if i am typing in cell C1 and i use the if statement to compare two
values: IF(A1>B1) if this is true then display the word "OFF" in its own
cell: this part is easy
i would also need it to display the word "ON" in cell D1
if the statement is false, i dont need anything displayed in any cells.
my formula is: IF((A1>B1),("OFF"),("") --> this will display the word "OFF"
in cell C1 if the statement is true and nothing in its cell if the statement
is false. but i dont know how to display the word "ON" in cell D1 at the same
its displaying the word "OFF" in cell C1.


Thank you in advance.



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 would like this code to check if there is already something in the cell, and if so, add a comma and space, then output the next appropriate condition of the if statement. The way I have the code written currently, it checks one column to see if a word exists, then if so, it outputs a word associated with this condition. Under this method, the code does not take account for columns that have more than one matching word. I've included a small portion of the code, there are more elseif statements, but they are redundant for this purpose. Thanks in advance for your help!!!

Code:

Sub Industry()

'Change this to reflect your true starting row.
vStartRow = 8

For i = vStartRow To Cells(Rows.Count, "M").End(xlUp).Row

'ITE
    If InStr(1, Range("M" & i).Value, "UL 60950") Or InStr(1, Range("M" & i).Value, "UL 60950-1") _
        Or InStr(1, Range("M" & i).Value, "UL 1459") Or InStr(1, Range("M" & i).Value, "UL 497") _
        Or InStr(1, Range("M" & i).Value, "UL 497A") Or InStr(1, Range("M" & i).Value, "UL 497B") _
        Or InStr(1, Range("M" & i).Value, "UL 497C") Or InStr(1, Range("M" & i).Value, "UL 1863") _
        Or InStr(1, Range("M" & i).Value, "UL 1310") Or InStr(1, Range("M" & i).Value, "UL 1012") _
        Or InStr(1, Range("M" & i).Value, "UL 61965") Or InStr(1, Range("M" & i).Value, "UL 60601-1") _
        Or InStr(1, Range("M" & i).Value, "UL 60601A-1") Or InStr(1, Range("M" & i).Value, "UL 60601B-1") _
        Or InStr(1, Range("M" & i).Value, "UL 60601C-1") Or InStr(1, Range("M" & i).Value, "UL 1863") Then
            Range("N" & i).Value = "ITE"

'Insulating Materials
    ElseIf InStr(1, Range("M" & i).Value, "UL 94") Or InStr(1, Range("M" & i).Value, "UL 840") _
        Or InStr(1, Range("M" & i).Value, "UL 1446") Or InStr(1, Range("M" & i).Value, "UL 2353") _
        Or InStr(1, Range("M" & i).Value, "UL 1692") Or InStr(1, Range("M" & i).Value, "UL 224") _
        Or InStr(1, Range("M" & i).Value, "UL 1441") Or InStr(1, Range("M" & i).Value, "UL 1696") _
        Or InStr(1, Range("M" & i).Value, "UL 969") Then
            Range("N" & i).Value = "Insulating Materials"

End If
Next i

End Sub





Good day: I am using the following formula to make choices for conditional
formatting in other cells. As it is, the "else" would be the result of an
entry of a US state abbreviation and would result in a 2 being placed in the
cell. The problem is that if the source cell is blank a 2 is returned. Is
there another way of expressing this so that a Canadian province abb =1, a US
state = 2 and a blank cell ="" null.
=IF(OR(C6={"NB","NS","NF","NL","PE","PQ","QE","ON","MB","SK","AB","BC"}),1,2)
--
Thanks Kevin



I have a pivot table that shows the quantity of an item on hand for various locatons. I want to count the number of occurences where the quantity is less than the reorder point. I use a vlookup function to get the reorder point for each item.

For example

Store Location---A B C D E F Reorder Count
part #123------- 5 2 1 6 1 3 4 4

That is, the values in locations B, C, E and F are below the reoder point.

I can construct a countif function =countif(B2:G2,"<4") however the reorder point for the next item might be 10.

Is there a way in which I can say countif the value is less than the value stored in cell H2?

That is =countif(B2:G2,<h2)

Thanks in advance for your assistance.

Jim Palmer


Hi Guys,

I will really appreciate you help. I have been breaking my head for an hour or so to figure this out. But no luck!

Here is what i am trying to do

=IF(G6="",0,IF(G6="FFV",-1000,-(IF(G6="C",(6.5-E55)*1000+1000,IF(G6="V",(8.3-E55)*1000+1000)))))

In G6 we mannualy input depending on the following category.

I have 3 categories Cars (C), Vans (V) and FFV i want to perform the above calculation and if the result is greater than $2000 then the function should return max $2000 as the output.

I tried using the max function as follows
=IF(G6="",0,IF(G6="FFV",-1000,IF(MAX(-2000),-2000,-(IF(G6="C",(6.5-E55)*1000+1000,IF(G6="V",(8.3-E55)*1000+1000))))))

But i keep getting -2000 as output even when i change values in cell E55.

Let me know if further clarification is required.

Thanks in advance.

- Aamil


I am working on a very long IF Statement which I am sure can be condensed.

Part of the statement is:

Code:

=IF(OR(K4<0,L4<0,M4<0),"NAV CHECK",IF(E4="Dead","DISCO",IF(ISBLANK(E4),"TYPE1?",IF(ISBLANK(F4),"TYPE2?",IF(AND(K4=0,L4=0,M4=0),"ZERO",IF(AND(E4="Stock",F4="On"),IF(AND(K4=0,L4>0,M4=0),"NO SO",IF(AND(K4=0,L4=0,M4>0),"NO PO",IF(AND(M4>=0,K4+L4>M4),"OVER",IF(K4+L4<=M4,"UNDER")))),IF(OR(F4="Shipper",F4="Promo"),IF(AND(K4=0,L4>0,M4=0),"NO SO",IF(AND(K4=0,L4=0,M4>0),"NO PO",IF(AND(M4>0,K4+L4>M4),"ERROR1",IF(K4+L4<=M4,"ERROR2",IF(AND(K4=M4,L4=0),"ACTIVE",IF(AND(L4=M4,K4=0),"ACTIVE",IF(AND(K4>0,L4=0,M4=0),"ANSWER1?"))))))),IF(F4="Off",IF(AND(K4=0,L4>0,M4=0),"ANSWER1",IF(AND(K4=0,L4=0,M4>0),"ERROR3",IF(AND(M4>0,K4+L4>M4),"ANSWER1",IF(K4+L4<=M4,"ERROR3",IF(AND(K4=M4,L4=0),"ERROR3",IF(AND(L4=M4,K4=0),"ERROR3",IF(AND(K4>0,L4=0,M4=0),"ANSWER1")))))))))))))))


The above is currently working and i"ll be adding to it so if its possible to shrink I'd be happy to see it.

If needed I can post a sample but will take a little time.


Dear Friends,

I am wanting to correct a conditional statement that I created that I want to use to determine if the value found in a cell is "YES." The value comes as a result of the following IF statement (=IF(D5>C5,"YES",""). Can someone help me to make the following conditional statement work?

If cell.Value = "YES" Then

Remember, the "YES" comes from a formula (see above) and I think that maybe that is the problem?

Thank you,
Mike


I want to apply a conditional format to a cell on my workbook which will make the cell blue in the following circumstances a) the cell is displaying no data and b) a certain cell elsewhere on my worksheet does not contain the text "VC" or "NV".

The problem is that the cell I want to apply this conditional formatting to also contains a formula which automatically populates the cell if a certain cell elsewhere on my worksheet contains the text "VC".

When I set up my formula within the conditional formatting window it does not work because I think it detects the formula within the active cell and therefore does not regard the cell as not displaying any data.

The conditional formulas I have been trying to use are;

Condition 1

=IF(OR(I18="VC",I18="NV"),"TRUE","FALSE") and

Condition 2

=IF(J18<1,"True","False")

The default cell colour is currently blue and I have set the conditional formatting so that when the conditions of the formula above is true it changes to white.

Is it possible to do what I am trying to do whilst the formula to prepopulate the active cell is in place.

Apologies if this is not clear but this is my first post to a forum of this type. If you need any clarification please get in touch.

Thank you.


I am trying to do this.

I am putting an "O" in cell C28.
In cell A34 I put this formula =IF(C28=BE48,"A-1","")

It put text A-1 in cell C28. Works great for what I need.
Now this is where I am unsure. When the user puts an "O" in C29 I want excel to look at A34 and realize there is already text there and from the prior IF statement above and put "A-2" into cell C29.

Kinda like this from excels point-of-view:
-The user has entered an "O" here
-I need to place the location where this "O" is found here.
-Oh, there is already a reference to an "O" already here, the next cell is open so I will place the location here instead.

Hope this makes sense, its an odd thing I know but its engineering related.


I have two worksheets in two different workbooks - the worksheets are
named "EARNED" and "Pay App Qty's". Each of these worksheets are
fairly complex and pull values automatically from OTHER worksheets
contained within their respective workbooks.

Column A in both worksheets contains specific Item Numbers - what I
need is code that will compare column A of both worksheets and when a
match is found copy a quantity from the appropriate cell in Column Z of
"Pay App Qty's" and paste it into the appropriate cell
in Column C of "EARNED".

"Pay App Qty's" contains multiple hidden rows - ONLY the visible rows
need to be included in the comparison.

Column Z of "Pay App Qty's" pulls figures from other worksheets within
it's workbook.

Item Number data starts in cell A7 of "Pay App Qty's".
Quantity data starts in Cell Z7 of "Pay App Qty's".

Item Number data starts in cell A2 of "EARNED".
Quantity data starts in cell C2 of "EARNED".

Data in "Pay App Qty's" currently goes to row 446.
Data in "EARNED" currently goes to row 271.

I am using Excel 2002 SP-2 and running Windows XP Professional.

Any and all help is sincerely appreciated.


Thanks!

- Kobi




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!


Here is my scenario:

I have two workbooks in one xls file. Workbook 1 takes values from
Workbook 2 and formats them as a receipt. I have 10 products on
Workbook 2, each with a different name and a different price.

What I need for Workbook 1, is a way to:

1. detect when I have a value other than "0" in the quantity cell on
Workbook 2
2. If the quantity is >0, enter the quantity into a cell in Workbook 1
3. multiply the cell value that is the quantity by a designated price;
the price of the product.


How do I accomplish this?

Thanks.




I need to set up something like an IF formula that will populate 1 cell based on which of 11 possible values will be represented in another cell. I can do this up to 7 with no problems using the following IF statement:

= IF(G23="1","a", IF(G23="2","b", IF(G23="3","c", IF(G23="4","d", IF(G23="5","e", IF(G23="6","f", IF(G23="6","g",IF(G23="7","h", "OTHER"))))))))

But if I try to put more than 7 conditions in there it tells me I've entered too many arguments. Is there a way I can either use more than 7 arguments or some other formula I should be using to accomplish this?


Hello,

I am currently try to have cells change color according to their value. I was wondering if there was a way to do this by using an "if" statement. I am aware of conditional formatting, but I cannot copy and paste this formula so I would prefer to use an if statement.

For example, I have a value in "A2", and if "A2" is >= "B2" then it is green, if it is >="C2" and <="B2" then it is yellow and if it is <="C2" it is red.

The values of C2 and B2 change depending on demand. Is there a way to use an if statement to do this?

Thank you!