How To Use An IF Statement With Multiple Outcomes

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


ADVERTISEMENT

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 View Related

Nested If Statement With 2 Possible Outcomes And 2 Vlookups?

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

Multiple Outcomes From A Logical Formula

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

Web Query Looping Multiple Outcomes

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

Formula With 6 Outcomes To Return 4

Apr 5, 2009

if h5 = "Supply Only" or "Supply Only With IWA" then I5 should be "S/O" however if h5 = "Supply Only With Survey" or "Supply Only With Survey With IWA" then I5 should be "S/o & S"

h5 could also be "Supply & Fit" or "Supply & Fit With Iwa" which should return "S/F"

but if h5 = "Supply & Fit With Survey" or "Supply & Fit With Survey & IWA" then I5 should be "S/F & S"

View 3 Replies View Related

Referencing 2 Outcomes From One Cell

Apr 20, 2007

Formula that if the cell (C1) looks up C5, and the range in C5 is 1-10000, I want C1 to show 10, or if C5 has less than 1, then I want C5 to show 0.

View 9 Replies View Related

Inserting Multiple Cells And Shifting Down Column Multiple Rows In IF Statement

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

Conditional Formatting To Reflect 2 Outcomes?

Mar 3, 2014

I have a master worksheet with due dates for various forms to be completed. These forms are on separate worksheets within the workbook.

I'd like to highlight the master date entry in red if the form hasn't been completed by the due date, but also highlight in green when it has been done.

View 1 Replies View Related

Formula To Predict Trends / Outcomes?

May 2, 2014

I used three different dates. In which the outcomes were available. The first three sheets.

Is there a possiblity to use excel to calculate a future outcome of the next month(Sheet 4). The figures are in a way linear, because the previous months are in it.

So it's possible for the future outcome to go up or stay the same. It can't be less then previous months.

See attachments.

It's about a holiday (combinations) of arrangements. So it'll be influenced by the date (because of holliday plannings), finance etc etc. But the last factors/variables we will use and test in SPSS and other programs.

The question is more: Is there a program/formula in excel which will predict the outcome in the last sheet with maybe a factor of outliners (this coincendence of a better salary in some parts/or the date that people will book a holiday). And how the formula would be formulated in the last sheet?

View 2 Replies View Related

Simpler Way To Record The Outcomes Of Matches

Feb 9, 2009

I recently posted work of my attempts to create a league table, i feel i have probaly chosen the worst way to record the actual results of matches, causing the need for complicated formula which donkyoite has been very helpful in doing and i thank him, but i dont understand whats going on, was wondering if there was a simplier way?

View 10 Replies View Related

Negative And Positive Outcomes In Worksheet

Apr 4, 2014

In my sheet i got some negative and positive numbers -210(Negative) and 210(positive).

So when on F there is a Negative number on K must come (Yes).

And when on F there is a positive number on K must come (NO)

It must go on for all on F

Is there function for that in Excel what i got now.

View 5 Replies View Related

Compare The Number Value Of 3 Cells To 64 Possible Outcomes

Apr 16, 2007

I need to compare the number value of 3 cells to 64 possible outcomes, of the three cells they can only have one of four possible values, How do I write the formula to do this. if cell a1 is 1,a2 is 1, a3 is 1 then thave it display the number 64, but I also need it to able to look at the same three cells, 64 different combinations, like 0,0,1 displays 21 etc.

View 9 Replies View Related

Create Multiple IF Statement And Multiple VLOOKUP Within Same Formula

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

Creating Outcomes For Normative Values Using Time Hh:mm:ss

Aug 6, 2014

I seem to struggle with excel formulas when time is involved. I am trying to create a spreadsheet that reviews outcome measures (walking tests in this case) to outline firstly if someone has improved or not (Outcome in the blue columns) which seems to be working alright. This takes into account the 2 aspects of time taken to complete the test and the walking aid required.

What I would now like to be able to do is compare each individuals 'After' score with a normal range for that test. I have put a list below the table of the ranges I am looking for. I have tried to put time formats into a long IF formula to try and get the ranges covered for the TUAG test, but doesn't seem to be working.

The 10 metre walk test is a little trickier as this is looking at change in velocity between the before and after scores. I think I need to be able to work out the velocity for each test and then do a comparison, but again bit stuck with the time formatting.

View 2 Replies View Related

Generating Random Outcomes Based On Probability

Feb 10, 2009

Can anyone help, I'm trying to create some test data, eg 1000 rows in excel. In each cell a formula (?) returns some text such as Dog, Cat or Rabbit based on the probability Dog=0.5, Cat=0.3, Rabbit=0.2

I've come up with a messy solution of generating a random number between 1-100 and then using a lookup table where 1-50 = dog etc... it works but is long winded and difficult for other people to follow.

View 6 Replies View Related

Count And Select Outcomes, Skip Duplicates

Oct 27, 2009

This code counts all cells with a value from column B1:B:10, C1:C10, D1:D10, E1:E10, F1:F10, G1:G10 and H1:H10. And then give the cells of the 3 lowest outcomes a blue backcolor. The problem is that the code also colors any duplicate outcomes. How can I let the code skip any following duplicate outcome(s)?

View 2 Replies View Related

Use Excel To Create A Formula To Predict Future Outcomes?

Jan 17, 2014

So I have some stats I have been tracking for NHL hockey games. I want to find a formula that is predictive of future outcomes, based on whether or not a team has a statistical advantage. Obviously, there are so many other factors, and there is no golden formula to accurately predict the winner of ever game. I want to find maybe a 70% success rate here. I want to be able to plug in all of the stats, and essentially have it tell me who statistically should win the game.

I have 11 independent variables (Stat A, B, C, D, E, etc.). I have a running total of how often the winning team had the stat advantage for each of these, and I think that should factor into the formula. My dependent variable is whether the team wins or not. how to do that with multiple independent variables.

View 3 Replies View Related

Multiple If Statement ..

Dec 13, 2009

I have a spreadsheet that I need some help with coming up with a multiple if statement(or maybe sumifs?) I don't know. Here is what I am wanting to do(In somewhat plain old english)

if c3:c43 = A9 add g4:43 than does the total = h3:43 if so display true else false

I have attached a copy of the spreadsheet.

View 14 Replies View Related

Multiple Outcome If And Statement

Mar 7, 2014

I have the following columns:

Status (column C), New Status (Column D) and Date Sent (column M)
Status column (C) contains multiple stages of progress status (Being Reviewed, Filed Not, etc)
New Status (D) column is blank (this is where I will enter the formula)
Date Sent (M) has mostly blank cells but some with dates in them

I would like a formula in column D (New Status) which evaluates C (Status).

If C = "Filed Not" AND M is blank, then output "Filed Not - Not Sent"
If C = "Filed Not" AND M has a date in it, then output "Filed Not - Sent"
If C <>"Filed Not" then output whatever data exists in C

I can get each of these to work individually, however, I can't, for the life of me, get them to work into one equation.

View 7 Replies View Related

Multiple Conditions Within An IF-THEN Statement

Jan 13, 2009

I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.

View 2 Replies View Related

IF Statement On Multiple Columns

Jul 28, 2009

I have a formula that looks at 5 columns. If any one of these 5 columns meets the specified criteria, it will "Fail" the row. What I want is, when the row is failed, to display the column that was failed. Well I have that part under control using the following formula.

View 2 Replies View Related

If Statement With Multiple Criteria

Nov 11, 2009

My concern is in row 4 and 5 of the attached spreadsheet. The problem is that not all postings for the allocations will take place on or before the date the allocation starts. Therefore, i need a logical formula that builds on what already exists and determines if the "entered" date is > the "start date". and then catches up how far it was behind. Example:

contract 12,000
Contract starts January 1st and ends December 31 (12 Months)
Entered Feb 1

In the January column there chould be zero in feb there should be 2,000 and the rest of the months should be 1,000

View 4 Replies View Related

IF Statement With Multiple Values

May 24, 2006

I would like to be able to search a group of cells for particular text,
placing the true of false value in another cell, using this formula;

=IF(ISNUMBER(SEARCH("ACP",A2)),"ACP","XXX")

My reason for this is that I have a large amount of data that I need to
break down. I have a column that is listed similar to below;

OR/ACP
OM/ACT
OR/MTS
O/O

The part before the / is 'system code'
The part after the / is 'module code'

I want to be able to have one additional column for system code and one
additional column for module code, without having to manually go through all
the data and make any amendments to it.

So as a result, (without the correct syntax) with 'S' being the system
Column and 'M' being the module column, what I would like to achieve is;

If cell contains "O/" then S1 = Office but if cell contains "OR/" then S1 =
Office Range but if cell contains "OM/" then S1 = Office Medium

I hope that makes sense.

Obviously repeated for the module column;

If cell contains "/ACP" then M1 = ACP but if cell contains "/ACT" then M1 =
ACT but if cell contains "/O" then M1 = O

View 9 Replies View Related

If Statement & Multiple Variables

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

MAX And IF Statement On Multiple Columns

Jun 30, 2014

I was wondering if there is a more beautiful way of this formula

Code:
{=MAX(IF(IF(B14=A1:A12, B1:B12)>IF(B14=A1:A12, C1:C12),IF(B14=A1:A12, B1:B12),IF(B14=A1:A12, C1:C12)))}

A
B
C

1
A
10
10

[Code] ...

Highest B is 80

View 7 Replies View Related

If Statement With Multiple Conditions

May 2, 2006

Dim b As Long
Dim last_rowB As Long
last_rowB = Range("K65536").End(xlUp).Row
For b = last_rowB To 2 Step -1
If Sheets("Main").Cells(b, 11).Value = Sheets("Main").Cells(b + 1, 11).Value & Sheets("Main").Cells(b, 11).Value <> "" Then
Sheets("Main").Cells(b + 1, 11).EntireRow.Delete
End If
Next b

If 2 cells are the same i want to delete the row of the 2nd cell, this works. But i dont want 2 delete cells that are blank, this doesnt work. For some reason it deletes everything in the table.

View 3 Replies View Related

Multiple If Statement Subject To Max Val

Jun 13, 2006

I've been asked to write a multiple if statement for someone here at work but I've been trying all day with no success. Basically, the OS liabilty in column C cannot exceed £2500. Here is an example inc the expected results............

So in row 1 the expected result is 500, as even though there is a 1000 reserve, 2000 of it has already been paid In row 2 nothing has been paid in column A so the OS liabilty in column C is the reserve up to a max of 2500 In row 3, there is 5000 in reserve, but 100 has been paid so the OS liability in col C must be 2000
In row 4 nothing has been paid in column A so the OS liability is the reserve in column B In row 5 150 has been paid, 150 is in reserve, so 150 is the OS liability
In row 6, 2500 has been paid, but even though there is an amount of 300 in reserve (col B), nothing is expected in OS liability as the 2500 max liability amount has been reached.

View 5 Replies View Related

If Statement: Multiple Criteria

Oct 31, 2006

I want to expand this calculation to include

IF(J5>100,VLOOKUP(A5,INDIRECT("'"&E5&"'!$A$4:$CY$31"),100,FALSE)

=IF(B5=0,0,IF(N5=0,0,IF(B5=1,IF(E5="INSERT",VLOOKUP(A5,INDIRECT("'"&E5&"'!$A$4:$CY$31"),J5+2,FALSE),IF(B5=1,IF(E5="ADVERT",VLOOKUP(A5,INDIRECT("'"&E5&"'!$A$4:$CY$31"),J5+2,FALSE),0),0)))))

View 9 Replies View Related

IF Statement To Evaluate Multiple Cells

Jun 9, 2014

I have a ss that has item descriptions, quantities and pricing.

Item descriptions are identified by a letter (a, b, c, etc) and in cells C20:c32. These are selected by drop down box. Item quantities are in cells E20:E32.

I want to evaluate cells C20:C32 and determine what letter is chosen. If A is selected in any cell C20:c32 I want to count the quantities for A in cells E20:E32. I can't quite figure out how to do this.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved