Conditional If Statement With Percent 


Conditional If Statement With Percent  Excel 
View Answers 
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:
A1325
B1??
C11000
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:
A1325
B1??
C11000
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 ...
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 ...
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
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 ...
How to make Excel automatically add row shading to every other row when you add data to the spreadsheet. This does ...
AND Function  Check if All Arguments are True
The AND function in Excel checks if every argument you put in it evaluates to TRUE. If everything evaluates to TRU ...
The AND function in Excel checks if every argument you put in it evaluates to TRUE. If everything evaluates to TRU ...
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
 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 Popup Windows in Excel
 This is a very simple Message Box, popup window, macro for Excel that illustrates how to put new lines, the same thi
 This is a very simple Message Box, popup 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
 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
 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
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Similar Topics
Dear all,
I would like to achieve the following:
M= minimum Stock
Res= Quantity in Stock after sales
In= Quantity to reorder
IF Res= to between 0 25% of M than In= M 0
IF Res= to between 25 50% of M than In= M15% of M
IF Res= to between 50 75% of M than In= M30% of M
IF Res= to between 75%100% of M than In= M60% of M
Example:
M= 30
=IF(D4<???,"30",IF(D4????,"45", IF(D4<????,"41",IF(D4<????,"42"))))
Thanks in advance for you help. Don't hesitate asking further details.
Best regards.
Hi
I'm still very new here & struggling abit with Excel, I know it can do lots of things, maybe because its late, my brain isn't helping me!!
I have created a Inventory Spreadsheet (I have attached it, to assist):
1) I want each piece of equipment to have its own unique number, eg. first 3 characters from "Manufacturer" column, first 5 characters from "Model Description" column & first 5 characters from "Part Number" column, and a number 1 going up from 1 from there.
& if there is nothing in these fields, the corresponding cell in "Column A" remains blank;
2) I would also like to create a traffic Light (red, orange, green) reorder system to monitor stock control. I have attempted to use "Conditional Formatting", but while it worked for the first line, it didn't work for the rest of the cells.
eg. If the "Quantity In Stock" column is higher than the "Desired Level" column then the corresponding "J" cell is Green
If the "Quantity In Stock" column is less than the "Desired Level" column then the corresponding "J" cell is Red
If the "Reorder Level" column is the same as the "Desired Level" column then the corresponding "J" cell is Orange
and
3) What is the formula to use if the "Unit Price" & "Inventory Price" columns have nothing in the cells, & I want them to remain blank until data is typed in.
I would appreciate any help anyone can offer.
Thanks
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,")","")
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.
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 (a1b1 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!!
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 (a1b1 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 A1 = 10 (Quantity in stock)
Cell A2 = 15 (Quantity needed)
Cell A3 = Order amount
I want cell A3 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 A3 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,A2A1)
Thanks for the help
exp:
Cell A1 = 10 (Quantity in stock)
Cell A2 = 15 (Quantity needed)
Cell A3 = Order amount
I want cell A3 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 A3 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,A2A1)
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 dropdown 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 freeform 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 correctlyformatted version of that statement, or point me to where I need to look to figure it out myself?
Many thanks,
Rich
I have a nested IF function I'm having issues with. My workbook is laid out as follows:
Sheet1 contains two dropdown 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 freeform 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 correctlyformatted 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/excelwork...stcolumn.html
(D.O)
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/excelwork...stcolumn.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!
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,
=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 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...
=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 getting multiple errors trying to get this formula into a macro:
The first being an expected end of statement on the fourth comma of the
third line (at the end of the first If(OR statement)
Basically, I'm just trying to get the formula typed in to the cell as
part of a macro and may be missing an easier way to type it in. The
formula itself works fine if I manually type it into a cell. The macro
recorder says it cannot record it. Please help.
Range("AG2").Select
ActiveCell.Formula = "=AI2&IF(OR(AI2="",AJ2=""),"",", ")&AJ2&
IF(OR(AI2&AJ2="",AK2=""),"",", ")&AK2 & IF(OR(AI2 & AJ2 &
AK2="",AL2=""),"",", ")
& AL2 & IF(OR(AI2 & AJ2 & AK2 & AL2="",AM2=""),"",", ")
&AM2&IF(OR(AI2&AJ2&AK2&AL2&AM2="",AN2=""),"",", ") & AN2
& IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2="",AO2=""),"",", ")
& AO2 & IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2 &
AO2="",AP2=""),"",", ")
& AP2"
Thanks!
The first being an expected end of statement on the fourth comma of the
third line (at the end of the first If(OR statement)
Basically, I'm just trying to get the formula typed in to the cell as
part of a macro and may be missing an easier way to type it in. The
formula itself works fine if I manually type it into a cell. The macro
recorder says it cannot record it. Please help.
Range("AG2").Select
ActiveCell.Formula = "=AI2&IF(OR(AI2="",AJ2=""),"",", ")&AJ2&
IF(OR(AI2&AJ2="",AK2=""),"",", ")&AK2 & IF(OR(AI2 & AJ2 &
AK2="",AL2=""),"",", ")
& AL2 & IF(OR(AI2 & AJ2 & AK2 & AL2="",AM2=""),"",", ")
&AM2&IF(OR(AI2&AJ2&AK2&AL2&AM2="",AN2=""),"",", ") & AN2
& IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2="",AO2=""),"",", ")
& AO2 & IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2 &
AO2="",AP2=""),"",", ")
& AP2"
Thanks!
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 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
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.
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.
Hey guys, if working on a small project right now, and i'm a bit lost.
here is what i need to do.
Column A= Item
Column B= Quantity InStock
Column C= Quantity Subtracted
i need a macro that would take "column C" subtract it from "column B", and then clear "column C". if the macro could do all the rows simultaneously, as apposed to having a macro for each row that would be great.
thanx for all the help in advance.
here is what i need to do.
Column A= Item
Column B= Quantity InStock
Column C= Quantity Subtracted
i need a macro that would take "column C" subtract it from "column B", and then clear "column C". if the macro could do all the rows simultaneously, as apposed to having a macro for each row that would be great.
thanx for all the help 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 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 have a column of values that are based on a formula e.g.
if(A1=10,"",A1*5)
What I am trying to do is place a conditional format on cell A1
1. if cell is =>50 make cell RED,
2. if cell is between 40 to 49 make cell Green
3. if cell is "" then make cell Yellow
The first two work okay but are having trouble with the third condition.
Have tried Cell equals "" and Formula Is =A1="" but without success.
Can anyone help me here please.
if(A1=10,"",A1*5)
What I am trying to do is place a conditional format on cell A1
1. if cell is =>50 make cell RED,
2. if cell is between 40 to 49 make cell Green
3. if cell is "" then make cell Yellow
The first two work okay but are having trouble with the third condition.
Have tried Cell equals "" and Formula Is =A1="" but without success.
Can anyone help me here please.
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:
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 609501") _ 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 606011") _ Or InStr(1, Range("M" & i).Value, "UL 60601A1") Or InStr(1, Range("M" & i).Value, "UL 60601B1") _ Or InStr(1, Range("M" & i).Value, "UL 60601C1") 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
I've a Database Query with Command Text that looks like this (Command Type SQL);
SELECT
ILE."Item No_",
ILE."Posting Date",
ILE."Entry Type",
ILE."Location Code",
ILE.Quantity
FROM
"Production".dbo."Item Ledger Entry" ILE
WHERE
ILE."Item No_" In (?)
I can then set to "Get the value from the following cell" in Parameters for the above question mark in the WHERE statement. That works well if I only type a single "Item No" in my cell (for example my parameter cell is cell I2, and in that cell is FE02C). However, I'd like to have a list of acceptable values in cell I2, such as FE02C, FE02D, FE0ZZ, etc.,.
Suggestions?
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
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 LocationA 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
For example
Store LocationA 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
Respectable Greeting to all
I stuck in this type of IF Condition, can plz anyone help
"Subtract Cell ""G4"" from Cell ""I4"", IF Cell ""J4"" become equal to Cell ""G4"", then
Subtract Cell ""G5"" from Cell ""I4"", IF Cell ""J5"" become equal to Cell ""G5"", then
Subtract Cell ""G6"" from Cell ""I4""."
For e.g
SUM(IF((G4(H4+I4)="G4" Then Shift to Cell (G5(H5+I4)="G5") Then Shift to Cell (G6(H6+I4)))
please find the attachment
Leave Balance From Half Day.xlsx
best Regards
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.5E55)*1000+1000,IF(G6="V",(8.3E55)*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.5E55)*1000+1000,IF(G6="V",(8.3E55)*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 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.5E55)*1000+1000,IF(G6="V",(8.3E55)*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.5E55)*1000+1000,IF(G6="V",(8.3E55)*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
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.