# 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
ADVERTISEMENT
Apr 22, 2009

I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."

(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:

=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")

(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.

View 5 Replies
View Related
Oct 9, 2008

In the attached workbook - the stock Inventory is increased, every second day, by the value shown in cells of column A. Column B displays the date of the update. I'm looking for a Formula (might be an Array Formula) that will return the latest date before the stock turns to be greater than the value in cell D3. I managed to solve it, in cell F3, but with the help column C.

View 2 Replies
View Related
Nov 26, 2002

Is it possible to have the condition IF(H9>E9>G9,E9-G9,0) so that the calculation is only done if E9 is between H9 and G9?

View 5 Replies
View Related
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
Aug 24, 2009

If A7 is equal to and greater than 95% than B7 is equal to "A". If A7 is equal to 91% but less than 94.99% than B7 is equal to "B"...

View 4 Replies
View Related
Jun 30, 2009

I have a value in E12, and i need a formula that looks at the value and if it is equal to or greater than 5, then the output should be E12 x 500 +1000, but if the value in E12 is greater than 5, then the output needs to add the original 5 x $500 and now include all greater than 5 to be x by $250 + 1000. I got this far, but if the value is greater than 5, i don't get the original 5 * 500 that i also need.

=IF(E12<=5,(1000+500*E12),IF(E12>5,(1000+250*E12)))

View 2 Replies
View Related
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
Jan 31, 2014

Is there a way to tell Excel to sum only up to a certain point? Like if A1 through AF equal 50, but I only want it to sum up to 40, to stop at 40?

Is there also a way to tell Excel using IF that if a statement evaluating a number is true or false, to return the value up until a certain number. For instance, if the value is 8 or less, then it should return that number, but if it exceeds beyond 8, say for instance 12, then if the value is 12, only return 8.

View 1 Replies
View Related
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
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
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
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
Aug 25, 2009

I have to make a table that shows that a if someone purchases

less than 5 items they receive no discount

5-10 items they receive 2% discount

11-20 items they receive 5% discount

21-50 items they receive 8% discount

over 50 items they receive 10%

and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.

View 4 Replies
View Related
May 11, 2012

With four numbers, I am attempting to "return" a number that is greater than zero, EVEN IF zero is in the array. Meaning, I need the number greater zero, knowing zero is within the array. This will be a part of a much longer concatenate formula.

010 5 0

I would expect the answer to be "5".

I tried: =abs(min(A1:D1))

This works great if there is a negative number (e.g. a -5, rather than 5 shown above), but when the numbers are all positive, the result is "0", I want 5 or the next greater number above zero.

View 9 Replies
View Related
Jul 21, 2014

NumberABC D Value to be returned

1 -10050 0 C

2 156.6700-31.34A

3 104.5800-20.92A

4 -6.2000 #NA

5 0 -3500 #NA

6 132.800-33.2 A

7 0 1000-50 B

What formula i need to apply so that i get values which are in column "Value to be returned"

View 6 Replies
View Related
Feb 12, 2014

I'm currently using the formula

=IFERROR(INDEX(startmonthsp,MATCH(TRUE,INDEX(ISNUMBER(E2:AR2),0),0)),"").

The startmonthsp name refers to the months across the top of my pivot table. This formula works but I now need one small change. If the value in the columns are zero or less then I do not want the formula to return that column header (bypass it until it gets a positive value above zero).

Attached is a workbook that shows my formula in action.

Excel Forum Question.xlsx

View 2 Replies
View Related
Oct 26, 2012

how to return the highest value of repeated controls in column A.

when comparing the numbers repeated in columns "A"

will return the highest value in column "D"

control

plug

block

value

Formula

115

20

1012

28,9

31,2

this is the highest value of repeated controls in column A

[Code] .......

View 3 Replies
View Related
Jun 23, 2009

How do I count the number of cells that have a value greater than 0 in a range of cells?

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