IF Statement Using Multiple Variables To Give Multiple Outcomes
Feb 6, 2009
I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:
A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)
D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
View 2 Replies
ADVERTISEMENT
Dec 7, 2009
I want to say, if B1 = A then the value is 10, if B1 = B then the value is 20, if C then the value is 30 and D the value is 40.
If there are two conditions then I know it would be =if(b1=A,10,0) but this is obviously a bit more complex.
View 6 Replies
View Related
Aug 3, 2012
I keep a formula to many arguments error.
=if(C2="","X","", ), IF(h2="","X","", )
What I am trying to say is if cell c2 is populated enter a "x" and if h2 is populated enter a "x".
View 4 Replies
View Related
Jun 9, 2009
I have in cell c40 a data validation list with source equal Royal (=Royal) has been created.
Royal is a namebox that has a defined list of 7 options (6 actual options plus one that says "select via drop down") :
FA4 = Select via Drop Down
FA5 = option 1
FA6 = option 2
FA7 = option 3
FA8 = option 4
FA9 = option 5
FA10 = option 6..............
The concept is that FA5 is associated with a value defined in FB5, FA6 is associated with FB6, so on
What I want to happen is when I select a value via the drop down selection in C40 eg. "Option 1" that in cell D40 the value associated with Option 1 (found in FA5) returns the value found in FB5 ($10).
How do I do this? I have tried a number of "if" statements but no luck.
View 2 Replies
View Related
Oct 7, 2006
Sheet 2 is the problem.
I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.
Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.
I have included a sample.
View 9 Replies
View Related
Sep 28, 2009
I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).
Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?
View 5 Replies
View Related
Dec 8, 2009
I have a web query that I would like to loop to a specified range. The catch is there are two possible outcomes. There is an outcome (data) and an outcome that is useless (no such page is found, etc). I'm kind of stuck trying to get this thing to loop. I would like to reference a range (contains links for input), and input all the data from the range, and based on the outcome, input the useful webpage data into the same spreadsheet.
Here is where I'm starting out.
View 9 Replies
View Related
Oct 18, 2013
I have an If statement I am trying to get to work but not sure of the flow. Each part works seperately but not together. What I need is if column E is blank leave blank, if cell DI2 is Yes then use columns DJ to DP for the lookup if DI2 is No than use columns O to U for the lookup. The first 2 lines are the individual formulas that work independently. The 3rd was my last attempt at combining them.
=IF($E21="","",VLOOKUP($E21,$O$5:$U$476,2,FALSE))
=IF($DI$2="YES",VLOOKUP($E21,$DJ$5:$DP$366,2,FALSE))
[Code]....
View 2 Replies
View Related
Feb 14, 2009
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.
I find writing out what I want helps some times so let me try it here.
So my variables are:
Product (there are 22 products)
Supply Less than (inset number)
These are the two criteria I want to use to produce a result.
The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.
What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.
The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
View 9 Replies
View Related
May 25, 2014
I'm making a Form with multiple pages and on every page there are atleast 36 Textboxes.
[Amount] [Description] [price]
I want to run a small sub updating the price on changing the amount. My code now looks like this:
Code:
Private Sub TextBox1_Change()
Call UpdatePrice
End Sub
Private Sub TextBox2_Change()
Call UpdatePrice
End Sub
Private Sub TextBox3_Change()
Call UpdatePrice
End Sub
I have to do this on 8 pages, 24 to 36 times, which makes the code extremely long. Is there a better way to do this?
Something like:
Code:
Private Sub TextBox1_Change(), TextBox2_Change() etc...
Call UpdatePrice
End sub
View 2 Replies
View Related
Jan 28, 2014
I'm looking to find a formula to calculate the red cell (actually all the cells in the "type" fields) by determining:
1) If the Product in the table matches the one in row 3
2) If the dates in table columns H, J, L, N and P took place in the month before the billing date
3) If those two conditions are met, sum the matching columns in I, K, M, O and Q.
For example for the red cell would currently equal $11,380.02 as the only revenue collection dates in range would be cells I1 through I3.
View 7 Replies
View Related
Sep 7, 2012
how to shift data in a column down multiple rows while in an IF statement. I am assuming you cannot just simply repeat the insert cell formula.
Here is my code - it's ugly but it was working when I just needed it to move down one cell:
lastrow = Range("A2").End(xlDown).Row
For i = 2 To lastrow
Range("B" & i).Select
If Range("B" & i).Value = Range("B" & i).Offset(-1, 1).Value Then
[Code].....
View 1 Replies
View Related
Aug 11, 2014
I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.
This is as far as I have got but even this won't work:
=IF(I15="BUTT CUT",=VLOOKUP(J15,V13:Z22,2,FALSE))
View 7 Replies
View Related
Oct 29, 2012
long time reader, 1st time poster:I have a spreadsheet which uses =F to give set responses i.e.
Cell H35= =IF((G7-G35)=0,"No Issue",IF((G7-G35)>=1,"System/Paperwork do not Match",IF((G7-G35)
View 3 Replies
View Related
Jan 13, 2014
Is there a lookup formula that will give me multiple items? so in my example attachment i want to look up risk from section A to give me the item in section B, but for risk 7 for example it has multiple items (a,b,c), how would i get all them? even better would be if its possible to have in seperate cells
View 4 Replies
View Related
Feb 4, 2012
columns consist of 1,2
I selected only 1 value of cells
I want to add comments to all the 1 value cells by selecting all 1 by shift + ctl +down
I can put same comments to all the cells?
If you want to have the same comment applied on many cells you could apply it to a single cell, then use the copy and paste special tool bar commands (under Edit) In Paste Special select "Comments" but value is going of that cell.
View 5 Replies
View Related
Jul 27, 2006
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
View 2 Replies
View Related
Feb 6, 2014
I have been struggling with this formula for ages and have finally given up. What I would like to do is to do a lookup on the concatenated values of Province, Department and Initiative No, which has mutiple values in the status of milestone column, and then give the answer based on a condition.
The condition for this example should be, that if the returned status / or statuses of the initiative is all 1, then the value should be 1, if it's all 2, then the value should be 2, and so on.....but if the values returned from the status of the initiative is a combination of 1,2 or 3, then it should give me the answer 2. If 4 is part of the comination of values returned, then the value should be 4.
Zero - 0 should be excluded from the formula as it is part of planned values and not actual.
View 5 Replies
View Related
Feb 5, 2010
Can I put two variables into a SumIF? forexample I want to sum Column C if Column A is equal to Apples and Column B is equal to Oranges, then sum Column C. Is there a quick formula?
View 3 Replies
View Related
Jun 26, 2014
I am looking for a syntax to ask a question with three variables
As an example
If
Blade size = 114 (only 3 blade size choices)
and
the Panel Size is between 500 and 700 ( This will also be dependant on the blade size)
and
the tilt bar is clearview (Three choices)
then
the result will be x = 182
The answers will depend on the above variables: 154, 159, 179, 177, 182 and 202
Screenshot 2014-06-26 14.21.41.png
Screenshot 2014-06-26 14.23.53.png
Screenshot 2014-06-26 14.26.11.png
View 3 Replies
View Related
Sep 30, 2009
I need to count up the scores from a questionnaire I have developed, which I am having trouble coming up with a formula for.
I have 5 different types of sales channels in column A. In columns C, E and G there is the call method they use (inbound, outbound or both) with a 'Y' / 'N' option next to it. Basically, I need a formula that if a sales channel has a Y next to Inbound, then the value 'Inbound' to be returned to the destination sale and if it has a Y next to Outbound then the value 'Outbound' returned to the destination cell and so on.
I have attached a spreadsheet to make my problem easier to understand.
View 7 Replies
View Related
Oct 28, 2009
I'm trying to use two variable to calculate a count.
In my attachment I have Name, Balance, and Disabled for the data.
I would like to find out how many names has a negative balance and have a "Yes" for Disabled
In the Summary part, I can find out the count of who is disabled, but can incorporate the negative balance.
View 4 Replies
View Related
Feb 18, 2010
This code is functional but very redundant and I figured that has to be an easier way to go about this.
View 5 Replies
View Related
Jan 23, 2013
I'm trying to count a range but I have multiple criteria which I would like to use, I have attempted to use countif/sumproduct with no avail. The data is spread over two columns, one contains the rank of various employees and the other is the number of hours they have worked. I am trying to count how many employees fall in to set hour ranges.
View 2 Replies
View Related
Nov 25, 2013
Writing a formula for the info below. Here is what I need basically. If B2>2, then I need it to count all central and field, STK and NSTK in column D.
location
days
stock type
Central
3
nstk
field
1
stk
[Code] ...........
View 9 Replies
View Related
Aug 24, 2009
I trying to figure out how to calculate a field based off multiple variables that are dependent on another cell range.
I'm looking to count everything in the C8:C49 cell range that contains either "BETA" or "FINAL" in the cell but ONLY if the F8:F49 cell range contains "In Test")
View 9 Replies
View Related
Oct 30, 2006
I am trying to create a column chart in which i want to incorporate 2 variables. However I don't want to use the line for the second variable. What I would like to do is to add the information of the number of deals a company has done on top of the column. So you see a column which gives you the average size of a deal, and the you should see a number at that column to see the number of deals done with that company.
To make this more clear I have attached an example. There are 2 tables of which the information I would like to see in one chart.
View 9 Replies
View Related
Feb 16, 2007
It's possible to declare multiple variables on a single line as such:
Dim x, y, z As Integer
Is it possible to assign these variables on a single line instead of:
x = 1
y = 2
z = 3
View 2 Replies
View Related
Mar 5, 2009
I am trying to put together an IF statement within excel which will give me certain output depending on the particular value within ONE cell.
For example:
If cell L5 has a value between 0 and 1.00 output value 0
If cell L5 has a value between 1.01 and 2.00 output value 20
If cell L5 has a value between 2.01 and 3.00 output value 40
If cell L5 has a value between 3.01 and 4.00 output value 60
If cell L5 has a value between 4.01 and 5.00 output value 80
If cell L5 has a value between 5.01 and 6.00 output value 100
View 4 Replies
View Related
Nov 26, 2006
How can I get this to read each, (if (and statement) and give the correct answer. And not give me the 1st underlined statement answer in cell D10. If that also equals true.
But if any other cells G11 thru G14 equals OUT. It still will give the 1st statement.
I need the answer to be the only one of them correctly that’s equal true.
I know that the 1st underlined statement = true. Because G15= OUT .....
View 9 Replies
View Related
