Return A Value In A Table Using An IF Statement

Dec 10, 2008

I am attempting to return a value in a table using an IF statement, but without referring to other values in the table.

My first column contains price break quantities;
5
10
20
50
70
100

The top row contains selling quantities;
1410192541517699105

The IF statement should return a "Y" in the cell if the value in the top row is greater than the value in the first column, but only for the relevant price break, i.e. the column with 51 in the first row should only have a "Y" in the row with 50. This would be much easier if I could work out how to post the workbook!

The formula cannot refer to other row values (otherwise it would be easy), and cannot use VB.

So far, I have come up with;

=IF(AND($C6

View 9 Replies


ADVERTISEMENT

IF Statement, MAX Statement, And Return A Value

Apr 25, 2007

Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C.

i.e.
Column A --- Column B ---- Column C
AB ------------ 0:02:22 ----- 04/14/2007
CW ----------- 0:03:13 ----- 04/16/2007
CW ----------- 0:01:42 ----- 04/13/2007

Thus, the value that should be returned is "04/16/2007".

View 9 Replies View Related

Return Null Value IF Statement

Mar 5, 2014

I have 2 sets of criteria, column B and D, both are ranked in column C and E respectively.

Cell h2 and h3 have the minimum requirements so I essentially want to add onto the RANK formula I have so if a person does not meet the minimum rank cells will return a null value.

View 1 Replies View Related

If Statement Return “W” Or “L” Result

Oct 14, 2008

creating a formula that will give me a result of either "W" or "L" depending on the amount in corresponding cell.

View 9 Replies View Related

If Statement To Return 3 Values

Aug 25, 2013

I want to use an IF statement that returns 3 values. I can do it to return 2.

Example: I am measuring performance of individuals. If they deliver below 50% I can return the value "Needs Improvement". If they deliver Over 60%, I need to show "Excellent" and finally if they deliver between 50% & 60% I need to return the value "Good".

I am not sure how to show 3 values.

View 3 Replies View Related

If / Or Statement To Return Header

Feb 15, 2012

I am working with data that has numeric values in columns S, T and U. In column M I want to return the header of the column that has the largest value (S,T or U).

Here is the formula I am trying to use:

=IF(OR(S2>T2,S2>U2),$S$1,IF(OR(T2>S2,T2>U2),$T$1,IF(OR(U2>S2,U2>T2),$U$1)))

The problem is that it only sometimes works properly. I have found instances where the value in column T is greater than S and yet column S's header is still returned. What am I doing wrong?

View 9 Replies View Related

IF Statement To Return A List

Feb 17, 2012

IF the first letter (i.e. left most letter) in any of the cells in range F7:F17 is "R", return its content to cell L3 and put a line break between each (i.e. create a list within a single cell).

I've read line breaks can be put in by using &CHAR(10)& but can't get a full formula to work.

View 5 Replies View Related

If Statement: Return 'V,' If Cell='&'

Jan 15, 2007

When I tried using if & or statements I got an error - so I tried this:

=IF(K7="&","V,",""),IF(K7="1 Space + &"," V,","")

I want to return 'V,' if cell='&' or if cell='(space)&' I want to return '(space)V,' What is wrong with this statement..?

View 5 Replies View Related

If Statement That Return A String Of Text?

Jan 9, 2009

not sure if an "IF" staement is appropriate?

I have 3 columns containing data:

column A has text in it,
example: A2="red", A3="green", A4="blue"

column B has 3 letter month in it and they could have repeats
example: B2="Jan", B3="Feb", B4="Jan"

C2="Jan"

in D2 list anything column A that matches C2
example: D2="red, blue"

View 11 Replies View Related

If Statement Return Conditionally Value In Other Column

Oct 26, 2009

In column D I have an Expiry Date and in column E I put formula “=IF(D2<=TODAY()+90,"we have less than 90 days!!!","we have more than 90 days")” the formula works fine but my problem is I need formula if item have expired in column D its have to say in column E “ ITEM EXPIRED “ or something. I highlighted line in yellow below attached sheet.

View 4 Replies View Related

If Statement To Return A Blank Cell?

Apr 17, 2014

I can't seem to get this If statement to return an empty cell It returns 0

HTML Code: 

=IF(+B7+C7="","", +$C$3-B7+C7)

The formula will go in D7
If either B7 or C7 is empty, leave D7 empty
If either B7 or C7 has a value, add or subtract from $C$3

View 6 Replies View Related

Return Column Header From If Statement

Jan 6, 2012

I'm working on a spreadsheet where I need to return the column header where the value in that row matches the criteria from an if statement.

The column headers are departments, the row headers are customers numbers, the values show the % of items sold in that department as a % of the total purchases by that customer.

I need to make a formula for each row so that if the % in any of the current row's values is 75%-100% then get the column header for the column that value is in.

I know it's an IF statement, but I don't know how to return the column header for the value when the if statement is true.

View 9 Replies View Related

If Statement- To Return A 2 If The Cell Value Was Greater Than

Feb 23, 2010

I know that if true the following formula would return a 1

=IF(I7= 500000 and if I wanted to return a 2 if the cell value was greater than 500000 but less than or equal to 1000000

View 9 Replies View Related

Setup IF Statement - If Date In Column A It Will Return Q3?

May 2, 2014

I wan to set up an IF statement so that if the date in Column A is between 4/28/14 and 7/17/14, it will return "Q3" and blank if no date.

View 1 Replies View Related

If Statement To Return Blank If Cell D1 Is Empty...

Nov 11, 2008

I have a problem with finding the right function. My goal is to have the following function...

The cell D1 has the following function: [=IF(B1<=C1;"YES";"NO")] But I would like to have the cell D1 to be left blank, if the cell A1 is blank.

View 8 Replies View Related

Search Range Of Data To Return A Value (IF Statement)

Oct 11, 2011

I have data that has months in it that are spelled wrong and in different formats etc. I need to be able to search the range of data I have created in a separate table in order to return the correct abbreviation into my spreadsheet. See below:

Need to be able to search this range (A1:E12) and return the corresponding horizontal value in the last column (Jan, Feb, Mar)

JanuaryJanuraryJan-11JanJan
FebruaryFebruraryFeb-11FebFeb
MarchMarchMar-11MarMar
AprilAprilApr-11AprApr
MayMayMay-11MayMay
JuneJuneJun-11JunJun
JulyJulyJul-11JulJul
AugustAugustAug-11AugAug
SeptemberSeptemberSep-11SepSep
OctoberOctoberOct-11OctOct
NovemberNovemberNov-11NovNov
DecemberDecmberDec-11DecDec

I am currently trying this, but its not working! =IF(F2="","",VLOOKUP(F2,Dates!$A$1:$E$12,5,TRUE))

View 9 Replies View Related

If Statement (return A Text Value If Numbers Are Present)

May 13, 2009

Column A consists of cells with a variety of five digit numbers. My formula needs to return a text value if numbers are present in column A, this is what I tried:
=if(A1="","Chassis") but it returns "False" instead of "Chassis".

View 9 Replies View Related

VLOOKUP To Return Image Based On If Statement

Nov 19, 2009

OK i have put the membership cards on the same sheet as my raw data so to make the formulas easier. On the membership card i have under membership level i have the formula : =VLOOKUP($J$3,A:E,4,FALSE)

there is 3 types of level bronze, silver and gold

what i would like is if the level is gold after then an image to be placed in the cell rather than the word gold and a different image for silver and a different image for bronze.

View 9 Replies View Related

If Statement That Will Return 0 If Difference Between Two Cell Ranges Is Negative?

Apr 26, 2014

How would i wrte this formula correctly?

=IF(SUM(I5:J26))-(SUM(D5:E26))>=0,0,(SUM(I5:J26))-(SUM(D5:E26)

It needs to say if the sum of the two cell ranges is less than or equal to zero then display as zero, otherwise display the answer

View 3 Replies View Related

IF Statement: Look At Different Cells And Return Different Values If Ture Or False

Nov 6, 2009

I have an IF statement that I need to look at different cells and return different values if ture or false, depending on what is selected in cell J2

Cell D2
if J2 = "FMB/FPI" then retrun the value of cell Y7
if J2 = "FPI/FPI" then N/A
if J2 = "FMB/OTHER" then return the value of cell Y7

Cell E2
if J2 = "FMB/FPI" then retrun the value of cell AC7
if J2 = "FPI/FPI" then then retrun the value of cell AC7
if J2 = "FMB/OTHER" then N/A

Cell F2
if J2 = "FMB/FPI" then N/A
if J2 = "FPI/FPI" then N/A
if J2 = "FMB/OTHER" then then retrun the value of cell AC7

View 4 Replies View Related

Return A Value Via An IF Statement Depending On The Font Style Of A Cell

Jan 16, 2007

Is it possible to return a value, via an IF statement, depending on the font style of a cell?

What I want to do is this...Check to see if a the font in the cell has a strikethrough, if it does, return 0, else return the value in the cell.

View 9 Replies View Related

IF Statement Based On Table

Jul 30, 2012

Im trying to put a IF statement together to use the in between function.

I have a table (days) from 1 to 5 ie 21

I have a column of aged purchase orders and im trying to do a IF statement based on the table.

View 5 Replies View Related

Nested If Statement For My Table

Apr 17, 2008

I am trying to write a nested if statement for my excel table. I calculated the betas of the stock portfolio. I would like to create an if statement that would do something like this:

if beta is less than 1 -- Below Average Risk
if beta is greater than 1 but less than 1.25 -- Average Risk
if beta is greater than 1.25 but less than 1.75 --Medium Risk
if beta is greater than 1.75 -- High Risk...

View 9 Replies View Related

Search Formula (return A Statement If A Certain Month Is Contained In The Text Within Another Cell)

Dec 21, 2008

I am trying to write a formula that will return a statement if a certain month is contained in the text within another cell. Formula is =IF(ISERROR(SEARCH("Dec",Assumptions!B2)),"Ensure Journal is Non Reversing","")

Cell B2 contains a date in the format of Dec 08, so if this date contains Dec, then return "Ensure Journal is Non Reversing", if it doesn't then leave the cell blank.

At the moment it is putting in the first test for every month I select and not changing to blank.

View 2 Replies View Related

Pivot Table Chaos: Blank Cells Return 0, Empty Cells Return Nothing

Jun 25, 2009

Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....

View 9 Replies View Related

Excel 2010 :: Pivot Table For Financial Statement With Months As Columns

Jul 4, 2012

Excel 2010. I set up a trial balance as following:

1/31/2000 2/28/2000 3/31/2000 ------- 12/31/2012
Acct # 1
Acct # 2
Acct # 3
Acct # 4

I was able to use pivot table to set up the balance sheet and income statement.

1. now each month is a field which I can choose to add to the report, but the field list is getting too long (and growing). Is there a way I can insert 1 field (for the user to input the exact date) or 2 fields (to input year and month) and the pivot table can pick up the input and refresh either automatically or manually.

2. I looked into the "group" function in pivot table, but how do I group the above data in quarterly and yearly fashion so the user can get those information quickly?

View 1 Replies View Related

Return Row Heading With Max Value In Table

Mar 23, 2014

I uploaded a simplified version of the workbook I'm manipulating for reference. I'm looking for a formula where I can get the ROW heading of a table to be returned based on the max value of a certain column. My column headings in the table are months. I have separate place in the workbook where I have the Months listed in a Column (A9:A......) and in Cells (B9:B....) I want to return the row heading name from the table. The tricky part is that the column headings don't always stay in same place, so Jan isn't always in A1, sometimes it is in B1 or C1 of the table.

Essentially from my months listed is separate part of the workbook. In the cell next to each month I want to match that month with column in the table. In that column find the max value and return the row heading.

View 6 Replies View Related

Table Value Return For Each Worksheet

Mar 20, 2014

I am trying to create a formula to return values based on the Total of a Table in each worksheet.

Example:

Column A has a list of worksheet names
Column B I want to return the value of the Total from a table on the worksheet named in Column A.

Each Table might be different widths (i.e., not the same number of columns).

Problem, I want the formula to use the worksheet name to goto the correct worksheet, and retrieve the data.

Each worksheet will only have one table on it.

View 1 Replies View Related

Return Value From A Table Using Two Way Lookup Method

Apr 25, 2014

My aim is to return a value from a table (Price of models) using a two way lookup method.

E.g. The code is CA45-AI, I'm looking for a formula to match the first 2 letters 'CA' to the TYPE RANGE (cell range) so excel knows to return a value from the CA row.. then, match the number '45' (from the CA45-AI (cell b3), so in the end.. excel returns the value (price) '90'.

Another example, Cell B5 is RA34-AI, so excel should return a value of 80 from the Price Of Models (cell range).

View 6 Replies View Related

Return A Value From A Table Based On Several Other Cells

Mar 26, 2014

So I've been trying to set up a table.

The table is made of 4 columns, with several times the same values. Each column has a specific range of value. No possible combination can exist twice. Meaning I won't have two rows that are identical. It looks like :

A1@X
A1@Y
A1&X
A2&X
B3#K
B3#O
B3ÂŁK
B4ÂŁK
C5$P
C5$M
C5*P
C6*P

The user tells me the value he wants in the 3 first columns, and I want to tell him what's in the 4th one.

View 3 Replies View Related







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