Computer Support Forum

# If Statement in Excel with Dates

Question: If Statement in Excel with Dates

I am trying to do the following:If the date is before 1/1/2014 in cell I15 I want cell J15 to say "yes". If the date is after 1/1/2014 in cell I15 I want cell J15 to say "no".Then, in another cell, I want if cell I15 is between 1/1/2014 and 1/1/2015 I want the value in G15 to be placed in cell K15.

Relevance 100%
Preferred Solution: If Statement in Excel with Dates

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc. Answer: If Statement in Excel with Dates

For the J15 formula, what do you want to return if the date in I15 is equal to 1/1/2014?The same question holds for the formula in K15: What if the date equals one of the 2 dates that you are checking for?BTW, the solution will be based on the IF function. Have you read anything on the IF function in the Excel Help files or via a Goolge search? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 89.79%

I want to have an IF true/false statement stating that for a column of DATES, if the date is before "5/1/2013" TRUE and everything else false within the if statement. thank you in advance!

Answer:How do a lay out an EXCEL IF statement using DATES?

Try this:With your column of DATES in column A,put this formula in cell B1 and drag down.=IF(A1<DATE(2013,5,1),"True","False")What happens if the date is exactly 5/1/2013?See how that works for you.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

2 more replies
Relevance 89.79%

I want to have two dates within the if statement. This is what I made, but i need it corrected. =IF(J2>DATEVALUE("5/31/2014"),"GOOD","EXPIRED" J2>DATEVALUE("6/31/2014"),"GOOD","EXPIRING SOON")

Answer:EXCEL If Statement Using Dates

Fieldman,Sorry it's taken me a day to get back to you, I've been pretty busy...I don't know if I understand what you're wanting to do or not, but the only way that I'm aware of to put multiple "IF" statements into one field is to do it the way I demonstrated above. You can put as many as you'd like in there to achieve what you're trying to do.This is just a shot in the dark, but I've re-read your posts several times now and am wondering if it would work the way you want if you change the formula in A2 to something like this:=IF(A1>DATEVALUE("5/31/2014"),"GOOD",IF(A1>DATEVALUE("6/31/2014"),IF(A1>DATEVALUE("5/31/2014"),"EXPIRED","EXPIRING SOON"),"EXPIRING SOON"))I really think you will need to play with the formula above to make it work, but I think something like this is going to be your best bet.Law of Logical Argument: Anything is possible if you don't know what you're talking about.

10 more replies
Relevance 89.79%

I want to put hourly rates of \$6 alongside weekdays and \$7 for weekends.I did this it doesn't work:=IF(A2=weekday, 6, 7)Help?

Answer:EXCEL If else statement with dates

The WEEKDAY function must refer to a cell that contains a date.Assuming your system is set up such that Sunday is weekday 1 and Saturday is weekday 7, this should work:=IF(AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7),6,7)For any WEEKDAY not equal to 1 or 7, the function will return 6.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 69.7%

My basic IF statement is IF J6>110% and IF L6<-5000, return "REVISION NEEDED" otherwise "OK". I've got this part using this: =IF(AND(J6>110%,L6<-5000),"REVISION NEEDED","OK")The tricky part is that I need to add that IF C6=>9/1/2013, the previous formula should compare 110%. IF C6<9/1/2013, the previous formula should compare 115%. Any help would be appreciated. Thanks in advance.

Answer:Help with an IF statement using dates

I'm pretty sure this gets you what you want...Since everything seems to be dependent on the value in L6, I pulled that out separately. In other words, IF L6>=-5000, we don't care about anything else, because we're "OK".Once we know that L6 is <-5000, we can check the date and percentage. If either of the 2 AND conditions are FALSE, then all we care about is whether or not J6 is >110%.Try this:=IF(L6>=-5000,"OK",IF(AND(C6<DATEVALUE("9/1/2013"),J6>115%),"Revision Needed",IF(J6>110%,"Revision Needed","OK")))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 69.7%

(Cell F16) (Cell H16)2017-12-24 17:47:22 06:38:06 2017/12/25 06:38:06 AM (value in cell H16)=F16=DATEVALUE("2017-12-24") TRUE =H16>DATEVALUE("2017/12/24 00:00:00 AM") TRUE =H16-DATEVALUE("2017/12/24 00:00:00 AM") 06:38:06 I checked the statements separately and they work. But when I combine this I get an error.

Answer:I am using dates in an if/and statement.

2 more replies
Relevance 69.7%

I am trying to use an IF statement that contains a date and have not had success. What I want is if the date in A1 is greater than 06/01/2009 then provide a response of 1; if not then "". The data in A1 is converted as a date.

Answer:IF Statement using dates

You could also do something like:=IF(A1>DATEVALUE("6/1/2009"),1,"")MIKEhttp://www.skeptic.com/

7 more replies
Relevance 69.7%

Basically im creating a spread sheet for my bills to keep track if they are paid late ect..How do I write an If/Then statement in excel to change data to say late or current depending on the date? I have one field for the current date, a1, and a column of due dates, d4-d48 I want to know if the due dates are past todays current date to populate to say late or current in column I.any ideas ?

Answer:if statement for dates?

Enter this in I4 and drag it down:=IF(D4<\$A\$1,"Late","Current")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 69.7%

help!I have a range of data that i need to analyze.if a date is 3/1/2014, then i need it to return +15 days later in the next columnand i have many dates that i need to do this do... can you help?

Answer:if statement using dates

Something like this might work:=IF(AND(MONTH(A1)=3,DAY(A1)=1,YEAR(A1)=2014), A1+15,"")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more replies
Relevance 69.7%

I want a formula that will return a blank if there is no date and the exact date if there is one. Let's say A! will eventually have a date and the cell is formatted as date, but I do not want the date format in B1, simply blank if no date

Answer:If statement using dates

Assuming you're using Excel, this will work:=IF(A1="","",A1)B1 would need to be formatted as date. Otherwise you get a number.message edited by beachyhbt

4 more replies
Relevance 69.7%

Is there a way to have excel recognize month change using an if statement?I am setting up monthly charts that need to vary based on the month, these charts are for a running calendar that requires calculations of three month intervals and overall totals, but they need to look pretty.Currently I have my main page set up where they can enter the month started and have been able to get the subsequent pages to change to the proper month. I can get the dates to populate but when it comes to months that have <31 days it is showing the date for the next month.Just for an understanding; these sheets can be started any month of the year. The quarterly results must be reported from start month to date.

Answer:Using IF statement and dates.

Well thanks to any who read this! I was able to figure it out after I posted this but thought that others may have this issue, so here is the results.=IF(AL36="","",(IF((AL36+1)=(EDATE(\$G\$4,1)),"",AL36+1)))The outside if statement is to cover February, if this is not done it will work on the 29th cell if it is not a leap year but result in an error on the 30th cell.Where AL36 is the day before, G4 is the current month.So if AL36 is the 28th of Feb, 2012, then AL36+1 would be 29-Feb-12 and it would report it out, but if AL36 is the 28th of Feb, 2011 it would be blank.

2 more replies
Relevance 69.7%

I want a formula that will return a blank if there is no date and the exact date if there is one. Let's say A! will eventually have a date and the cell is formatted as date, but I do not want the date format in B1, simply blank if no date

More replies
Relevance 69.7%

Hi,
I have a Access 2000 db that has three different date section in one of the forms. I am triing to create a query that will pull out the oldest date and stick it into a seperate column. But, some of these date fields that I have are null. I have tried doing a IIF statement but that does not seem to work. Does any body have any ideas on how I could go about doing this. Thanks in advance for your time.

Answer:IIF statement and Dates

If you could post the code that you tried, we could probably alter it so it would work for you...

3 more replies
Relevance 68.88%

In Excel, I have coded each entry with a number between 1-10 (column AN14:AN113) and am trying to find the percentage that each number (1, 2, 3, etc.) occurs within a calendar year date range (01/01/11-12/31/11) within the column (E12:E113). Just need help coming up with the formula...Thanks!

Answer:If Then Statement with Dates, Then Counting

I'm a bit confused.Your number codes are in AN14:AN113 but your dates are in E12:E113.That's 2 less rows of dates than numbers. Is that correct?What version of Excel are you using?If it's 2007 or later, try this:Add another column (often called a Helper Column, which you could hide) to extract the Year from the dates in E12:E113.e.g. in F12 enter =YEAR(E12) and drag it down to F113Then you can use this formula for your percentages:e.g. for the number 1:=(COUNTIFS(\$AN\$12:\$AN\$113,1,\$F\$12:\$F\$113,2011))/COUNTIF(\$F\$12:\$F\$113,2011)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 68.88%

I am trying to extract some data on dates to a dashboard to present it in a pie chart. I have a list of dates relating to when something was tested. I need to establish which ones were tested in the last 3,6,12,12+ months, and those that haven't been tested (Blanks) then extract it onto another sheet on the document as simple numbers. Can anyone help?

Answer:IF statement. Dates with different outcomes

Hi,For this example the dates, including some blank cells, are in the range B2:B200,Use this formula to return the number of dates no more than 30 days before today:=SUMPRODUCT((\$B\$2:\$B\$200>=NOW()-30)*(1))To count the number of dates in certain ranges, use this formula:=SUMPRODUCT((\$B\$2:\$B\$200>=NOW()-60)*(\$B\$2:\$B\$200<NOW()-30)*(1))Repeat for each required range, and to pick up the remaining dates use this formula:=SUMPRODUCT((\$B\$2:\$B\$200<NOW()-365)*(\$B\$2:\$B200<>"")*(1))adjusted to match the end of the last range of dates. Note that this formula excludes counting empty cells.Finally to count empty cells use this formula:=SUMPRODUCT((\$B\$2:\$B\$200="")*(1))If these formulas were on another sheet and the dates are on Sheet1, you get something like this:=SUMPRODUCT((Sheet1!\$B\$2:\$B\$200>=NOW()-60)*(Sheet1!\$B\$2:\$B\$200<NOW()-30)*(1))Regards

4 more replies
Relevance 68.88%

Im trying to write an if statement which will display, in a cell, an yearly quarter.e.g.I have a cell with a May 24, 2011 date (cell N7), I want to write an if statement where if the N7 date falls after May 1 (cell P2), but before August 1, 2011 (cell Q2), the result returned is 2 (cell S3).If the same cell N7 has its date changed to August 24, 2011 the result returned is 3 (cell S3).the quarters are divided as follows:Q1 Feb 1 to April 31Q2 May 1 to July 31Q3 August 1 to Oct 31Q4 November 1 to Jan 31thanks to anyone who can help me out....

Answer:nested if statement and dates

With you Quarter start dates in e.g. C1:C4, something like this should work:=IF(AND(A1>=C1,A1<C2),"Q1",IF(AND(A1>=C2,A1<C3),"Q2",IF(AND(A1<=C3,A1<C4),"Q3",IF(A1>=C4,"Q4",""))))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 68.06%

i'm needing to access two cells with dates in each and determine another cell to see if it is within those two dates and if so, display another specific cell.

Answer:if statement to determine what falls between two dates and d

This formula will return Within! in D1 if the date in B1 is within the dates shown in A1:A2. It will return a blank cell otherwise.

A B C D
1 4/5/2013 4/8/2013 Within! =IF(AND(B1>=A1,B1<=A2),C1,"")
2 4/10/2013 Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 68.06%

So I am trying to create and IF statement that takes into account the submit date and the requested date for PTO wherein if the Requested Date is less than five days it should be flagged and if it's over five days it is good. So for example if the submit date was 03/05/2017 and they requested PTO for 03/08/2017 the formula should flag it. I was wondering if an IF statement would help with that or if I should be using some other formula. I really appreciate all help.

Answer:How to do an IF statement with multiple dates and criteria

There are few issues with your question.1 - You mention less than five days and over five days, but you don't say what should happen if the difference equals five days.2 - What do you mean by "the formula should flag it"? Should the cell that contains the formula say e.g. "Flagged"? Should one of the date cells turn a color? Should a Message Box pop up? Should bells and horns sound along with some flashing lights? ;-)3 - We don't know how your data is laid out, so all we can do is offer a generic suggestion.Something like this might work, as long as Excel recognizes the dates in A1 and B1 as dates. Simple math, such as subtraction, can be used with dates. Subtracting 2 dates simply returns the number of days between the entries.=IF(A1-B1<=5,"Flagged","Good")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 68.06%

I am trying to do an iif statement to work out a date due from a Loan date taking into account certain holiday dates. I have tried several different formats but keep getting the result of 30-Dec-1899.
I want the due date to be 14 days later but if the date falls within a holiday period, then I want it to display the first date of returning back.

I have tried the first part like this:
=iif([Loan date]+14>=25/10/2010<=31/10/2010,01/11/2010,[Loan date]+14)
The result is always 30/12/1899

Any suggestons?

More replies
Relevance 66.42%

I have no idea if this is even possible, but I would like to create a spreadsheet that auto-populates a series of dates based on one date that the user enters. Let's say I have a goal date to complete a project, and for every project I work on, something needs to happen 6 weeks prior to the goal date, another thing needs to happen 4 weeks prior to the goal date, and something else needs to happen 1 week and 5 days before the goal date. I would like to set up 4 cells in Excel: one for the goal date, and three for the various things that need to happen leading up to that goal date. I would like to be able to enter the goal date into the main cell and have the other three cells auto-populate with the correct date based on what I enter in the goal date. For example, if I enter a goal date of 11/01/2016, I would like the other three cells to automatically populate themselves with: 09/20/2016 (6 weeks prior), 10/04/2016 (4 weeks prior), and 10/20/2016 (1 week and 5 days prior). Is this possible? Can Excel return dates in that way?

Answer:How can I auto-populate dates in Excel based on other dates?

Sure, it's easy. Excel internally stores Dates and Times as numbers. Each day is a whole number and time is the decimal portion of that day. See here for a pretty good explanation of how Excel deals with Dates and Times as well as links to all the various Date and Time functions that Excel offers:http://www.cpearson.com/excel/datet...As for your question, simply subtract the number of Days required from the cell with the Goal Date.With 11/01/2016 in A1, use this to return 9/20/2016:=A1-42(42 is 6*7, or 6 weeks.)I'll leave the other two formulas to you. ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 65.19%

I am also new to Access 2010 so am slowly learning my way around. I need an iif statement that will allow me to create a new column telling me whether one date field (Course Date) is within or outside the range of two other date fields (Previous Effective Date) and (Previous Expiration). I hope that someone can help me solve this issue quickly. I've tried various ways to write the statement and none of them worked. One statement only returned the information as all being outside of the range, which I could clearly see was not true. Here are some examples of what I tried. None of these worked at all. Thanks. Joyce

=IIf(([course date]>=[Previous Effective Date] and <=[Previous Expiration Date]),"between dates",IIf([course date]<=[Previous Effective Date] and >[Previous Expiration Date]),"outside date range")

In/Out Range: IIf(([course date]>=[Previous Effective Date]),"between dates", And IIf([course date]>=[Previous Expiration Date],"outside date range "))

In/Out of Range: IIf(([course date]>=[Previous Effective Date] And <=[Previous Expiration Date], "between dates") And IIf([course date]<=[Previous Expiration Date] And >=[Previous Expiration Date], "outside date range"))

More replies
Relevance 57.4%

For some reason as I have added dates to cells, they are not getting converted to the Date type I have selected: 15/03/2009I type in my entries in this format:March 15, 2009 (usually it converts immediately but not this time)I've tried selecting the columns of cells with dates, Select>Format>Cells>Date and Type but nothing changed (although one date was converted to 03-15-2104Any suggestions?

Answer:converting excel dates in Excel 2003

Dat's cuz they're probably not being recognized as dates by Excel. My guess from afar is that the cells are formatted as Text and aren't accepting any other formats. Excel is finicky like that sometimes.Try this with one of your stubborn "dates"...let's assume it's in A1=DATEVALUE(A1)Now format that cell anyway you want.Then do a Copy...PasteSpecial...Values to eliminate the formula.

7 more replies
Relevance 56.99%

I need to write an if/then statement in excel that will do this

If C2 Equals MTM B2 Equals MTM. And If A2 Is Null And B2 Does Not Equal MTM Then B2 Equals Currently. And If A2 Has A Date Prior To The Current Date Then B2 Equals Currently. And If B2 Equals Currently Then C2 Becomes Null.

Answer:If Then statement in Excel

7 more replies
Relevance 56.99%

I need an "if condition" that says if a certain "date" is less then one year from September 30, 2011 display 0.00, if its more then 1 year but less then 2yrs display .05, if its more then 2yrs but less then 3yrs display .10, if its more then 3yrs but less then 4yrs display .15, if its more then 4 years display .20

Answer:if statement in excel

Here's one way to do it. Your dates would be in B1 and C1.=IF(DAYS360(B1,C1)<365,0,IF(DAYS360(B1,C1)>365,IF(DAYS360(B1,C1)<730,0.05,IF(DAYS360(B1,C1)>730,IF(DAYS360(B1,C1)<1095,0.1,IF(DAYS360(B1,C1)>1095,IF(DAYS360(B1,C1)<1460,0.15,IF(DAYS360(B1,C1)>1460,0.2,""))))))))You could also do it with and() like this:=IF(DAYS360(B1,C1)<365,0,IF(AND(DAYS360(B1,C1)>365,DAYS360(B1,C1)<730),0.05,IF(AND(DAYS360(B1,C1)>730,DAYS360(B1,C1)<1095),0.1,IF(AND(DAYS360(B1,C1)>1095,DAYS360(B1,C1)<1460),0.15,IF(DAYS360(B1,C1)>1460,0.2,"")))))

3 more replies
Relevance 56.99%
Question: Excel If Statement

I am trying to write a macro that compares two cells to see if they are equal. If they are equal it will copy a file (0-0.lis) from one folder (C:\Monthly_Results) to another. The file changes depending on which cell equals cell A13. I think I am close, but not sure.Sub surphace()Set r1 = Range("A1")Set r2 = Range("A2")Set r3 = Range("A3")Set r4 = Range("A4")Set r1 = Range("A5")Set r2 = Range("A6")Set r3 = Range("A7")Set r4 = Range("A8")Set r1 = Range("A9")Set r2 = Range("A10")Set r3 = Range("A11")Set r4 = Range("A12")Set r1 = Range("A13")If r1.Value = r13.Value ThenMy.Computer.FileSystem.CopyFile( _ "C:\Monthly_Results\0-0.lis",_ " C:\Aaron_Staley\0-0.lis", _ FileIO.UIOption.AllDialogs, _ FileIO.UICancelOption.DoNothing)End IfEnd Sub

16 more replies
Relevance 56.99%

How do I create an IF statement to calculate the following:

Between a series of dates, IF there is a 3 day or more lapse, I want to count 1. IF there is less than 3 days, do not count anything.

Thanks so much for helping!!

Tammy

Answer:Help with an Excel IF Statement

How do I create an IF statement to calculate the following:

Between a series of dates, IF there is a 3 day or more lapse, I want to count 1. IF there is less than 3 days, do not count anything.

Thanks so much for helping!!

TammyClick to expand...

=IF(B1-A1>=3,1,"")

Where B1 is the end date and A1 the start date

1 more replies
Relevance 56.99%

Hi,I have a macro that saves my file with a name and current date, see below -fileSaveName = "Order Data " & Format(Now(), "dd.mmm.yyyy") & ".xls"I want to amend this so that the file will save it as, for example -fileSaveName = IF(MONTH(NOW())=4,"Q1",IF(MONTH(NOW())=7,"Q2",IF(MONTH(NOW())=10,"Q3","Q4"))) & Format(Now(), "dd.mmm.yyyy") & ".xls"but I am unsure of the correct syntax.Thanks,Kasey

Answer:Excel VBA IF statement

Well, it'd depend on exactly where you define the start of your quarters, but I imagine it'd look something like this:"Q" & Int((Month(Now) - 1) / 3) + 1 & "." & Format(Now(), "dd.mmm.yyyy") & ".xls"How To Ask Questions The Smart Way

2 more replies
Relevance 56.99%

Hey Guys,I have an issue that needs to be addressed. I have a worksheet with 380 rows of yes/no questions. I need to summarize the yes no answers on sheet 2. What I want to do is list the questions, (in an organized fashion) That have been answered with a 'no'. BTW, Macros are not an option!Thanks for any help,Calico

Why not just sort on the "no" column?

3 more replies
Relevance 56.99%
Question: excel if statement

we are wanting to see if we can create an if statement in excel. basically, we are looking to create an if statement. in the first picture below, you see we have a "3" under quanity. then you also see the prices as well. we're wanting to do something like this - if "A5" (the quanity) is greater than zero, we want it to display the whole row (the qty,size, net, list, and total) into another spreadsheet. basically, if the qty is greater than 0 in the first form, it will then display that whole row in the 2nd spreadsheet(second picture) is there anyway to do this?

More replies
Relevance 56.99%

I Need To Write An If Then Statement That States

=if(b15<now(),"now",b15+1)

And I Need To Add The Rule That Its False If D15 Equals Mtm

Answer:If Then Statement In Excel

Something like this?? Not sure if this meets your logic or not...

Code:
=IF(AND(B15<NOW(),D15="Mtm"),NOW(),B15+1)
HTH

1 more replies
Relevance 56.99%

TABLE10 A B C D E1 PO CL ST DL OT2 Lab 1 10 03 203 Lab 2 15 05 304 Lab 3 20 08 40TABLE210 A B C D11 POS CLS TYP (FORMULA IN TABLE2, CELL D11) IF POS=Lab, CLS=2, AND TYP=ST, THEN 15IF POS=Lab, CLS=2, AND TYP=DL, THEN 05IF POS=Lab, CLS=2, AND TYP=OT, THEN 30D11=SUMPRODUCT(--(\$A\$1:\$A\$4=\$A\$11),--(\$B\$1:\$B4=\$B\$11),--( I?M STUCK, PLEASE HELP!

Answer:Excel If Then Statement

Since we now know that Column A may contain different values than shown in your examples, it sounds to me like we first need to lookup the combination of A11 and B11 and find the Row that contains the matching set of values in Table 1 Columns A & B.After that, we need to lookup the value found in C11 in C1:E1 and see which Column it is in.We can then use the Row number and Column number with the INDEX function to return the value at that "intersection".=INDEX(array, row_num, column_num)Here's what I would do:I would use another column (e.g. F) and Concatenate the values in Table 1 Columns A and B. You can use any column you want and then hide it if you don't want to see it.For example, in F2 enter this and then drag it down to F4:=A2&B2This will give you values like Lab10, Oper25, etc.Then, assuming you used F2:F4 for your concatenated strings, use this formula in D11:=INDEX(\$C\$2:\$E\$4,MATCH(A11&B11,\$F\$2:\$F\$4,0),MATCH(C11,\$C\$1:\$E\$1,0))The first MATCH function will lookup the combination of A11 and B11 in F2:F4 and return the Row number of the array where it was found. Note: The value that is returned is not the Row number of the Excel spreadsheet, but the Row number of the array F2:F4. i.e. F2 is Row 1, F3 is Row 2, etc.The second MATCH function will lookup the value in C11 in C1:E1 and return the Column number of the array where it was found. Again, that's the Column number of the array C1:E1, not the column number of the spreadsheet. i.e. Column C is Column 1, etc.Finally, t... Read more

16 more replies
Relevance 56.99%

Hi :)I'm having problems using an IF Statement.I have a spreadsheet with multiple lines of data. I need a formula that starts in cell D3 and searches E3 thru AK3, to see if they all contain 'No', if they do i want it to return the value in A3. If any of the value's says 'Yes', then i want it to return a 0.I was thinking it might looks something like:=IF(COUNTIF(E3:AK3,"No") A3, 0)i thought this would be simple to do, but am feeling lost, any help you could give me would be very welcome.thank you so much

Answer:IF Statement for Excel

The simplest method is this:=IF(COUNTIF(E3:AK3,"No")=33, A1, 0) since there are 33 columns in E3:AK3Another method is this, which uses the COLUMNS function to return the number of columns in a range, which in this case is 33.=IF(COUNTIF(E3:AK3,"No")=COLUMNS(E3:AK3),A1,0)You could also use this, as long as there are no blank cells in E3:AK3If there will never be any blank cells in E3:AK3, this should work also:=IF(COUNTIF(E3:AK3,"No")=COUNTA(E3:AK3),A1,0)COUNTA counts the number of cells that contain data, so if it is equal to the number of cells that contain "No", then the IF condition is TRUE. However, if there are any blank cells in E3:AK3, this formula won't get you the results you want.

2 more replies
Relevance 56.99%

How do I create an IF statement to calculate the following:

Between a series of dates, IF there is a 3 day or more lapse, I want to count 1. IF there is less than 3 days, do not count anything. The dates are in one column. See the below example:
Paid Sick 5/ 3/2005
Paid Sick 5/ 8/2005
Paid Sick 8/ 2/2004
Paid Sick 8/ 3/2004
Paid Sick 10/20/2004
Paid Sick 10/21/2004
Paid Sick 11/12/2004
Paid Sick 1/ 5/2005
Paid Sick 1/ 6/2005
Paid Sick 10/11/2004
Paid Sick 10/16/2004
Paid Sick 10/17/2004
Paid Sick 10/18/2004
Paid Sick 11/30/2004

Thanks for your help!

Tammy

Answer:Help Excel If Statement

Not sure about your date series, but see if this is what you are after.

3 more replies
Relevance 56.99%

I am trying to get Excel to evaluate if g3 is the "Red" then evaluate b6 to determine the price. It works until I get to 501. For some reason Excel wants the * but I do not know why. Any suggestions?=IF((AND(G3="Red",B6<=250)),25,((IF((AND(G3="Red",B6<=500)),35,((IF((AND(G3="Red",B6<=1000)),40*((IF((AND(G3="Red",B6<=2500)),50*((IF((AND(G3="Red",B6>=2501)),75,"")))))))))))))

Answer:If and statement in Excel

First, I'll discuss your formula, then I'll suggested a method using VLOOKUP that might be more efficient.I copied your formula into a spreadsheet. I put Red in G3 and started entering values in B6.Starting at 501, I get a #VALUE error which is what I would have expected. The * is the multiplication operator and Excel can't figure out what to multiply by 40.As soon as I changed the *'s to commas, the formula works fine.=IF((AND(G3="Red",B6<=250)),25,((IF((AND(G3="Red",B6<=500)),35,((IF((AND(G3="Red",B6<=1000)),40,((IF((AND(G3="Red",B6<=2500)),50,((IF((AND(G3="Red",B6>=2501)),75,"")))))))))))))However, you have way too many parenthesis. Even though the formula works, it can be written in a shorter manner:=IF(AND(G3="Red",B6<=250),25,IF(AND(G3="Red",B6<=500),35,IF(AND(G3="Red",B6<=1000),40,IF(AND(G3="Red",B6<=2500),50,IF(AND(G3="Red",B6>=2501),75,"")))))That said, you might want to consider a VLOOKUP formula instead of a Nested IF. By using VLOOKUP and a lookup table for your quantity/price relationships, you can enter your values in the table and never have to touch the formula when the criteria change.For example, if you set up a table like this...
A B
1 0 25
2 251 35
3 501 40
4 1001 50
5 2501 75... then you can use this formula:=IF(G3="Red",VLOOKUP(B6,\$A\$1:\$B\$5,2,1),"")The range_lookup argument of "1" means that VLOOKUP will find the value that is equal to or less than the value in B6 and retu... Read more

5 more replies
Relevance 56.99%

Okay here it goes...if you are setting it up it looks like this...

____A________B__________C_________D____ E_____

1 IH IC RH RC
2 VS11 \$20 \$30 \$40 \$10
3 MS11 \$50 \$60 \$70 \$10
4 CH52 \$80 \$90 \$100 \$10

Okay now I need to use this info on another spreadsheet....

I want to be able to type in VS11IH and \$20 pop up. I already know =IF(B21="VS11IH",20,0) and it works, but my problem is how do I put in multiple IF stmts? I want to be able to put in any code for say that is listed and the right amount pop up. So I tried =IF(B21="VS11IH",20,0),(b21="VS11IC",30,0), and so on.It didnt work.

Anyways I have 5 pages full of what looks like the spreadsheet above...I have made 4 boxes one for IH,IC,RH,and RC. So I need the IH box to hold all of the codes and thier specific price...the only way I can think to do it is to have a very very very long IF statement and i I can I will, but I thought it would only hold up to 5. Any suggestions would be awesome!!!!!!!!!!!

Answer:need help with an excel IF statement

Originally posted by courtney23:
Okay here it goes...if you are setting it up it looks like this...

____A________B__________C_________D____ E_____

1 IH IC RH RC
2 VS11 \$20 \$30 \$40 \$10
3 MS11 \$50 \$60 \$70 \$10
4 CH52 \$80 \$90 \$100 \$10

Okay now I need to use this info on another spreadsheet....

I want to be able to type in VS11IH and \$20 pop up. I already know =IF(B21="VS11IH",20,0) and it works, but my problem is how do I put in multiple IF stmts? I want to be able to put in any code for say that is listed and the right amount pop up. So I tried =IF(B21="VS11IH",20,0),(b21="VS11IC",30,0), and so on.It didnt work.

Anyways I have 5 pages full of what looks like the spreadsheet above...I have made 4 boxes one for IH,IC,RH,and RC. So I need the IH box to hold all of the codes and thier specific price...the only way I can think to do it is to have a very very very long IF statement and i I can I will, but I thought it would only hold up to 5. Any suggestions would be awesome!!!!!!!!!!!
Thanks in advance! Click to expand...

Try VLOOKUP, or INDEX/MATCH instead of IF
as an example

=VLOOKUP(A1,RANGE,2,0) to get IH, change the 2 to 3 to get IC etc.

Post back if you need more help

2 more replies
Relevance 56.99%
Question: excel if statement

I would like to construct an if statement to do the following:

if A25 is less than 32 then 0%
if A25 is between 32 and 48 then 10%
if A25 is over 48 then 15%

this is what I have come up with but it doesn't seem to work:

=IF(AND(A25>=32,A25<48),A25*-1,IF(A25>48,15,0))

can someone help me out.

thx

Do you want the result just to be the number 0, 10, or 15 or do you want the result to be a certain percentage of the value in cell A25?

If you just want a number, this should work:

Code:
=IF(A25<32,0,IF(A25>48,15,10))

If you want a percentage of the value in A25, this should work:

Code:
=IF(A25<32,0,IF(A25>48,A25*0.15,A25*0.10))

1 more replies
Relevance 56.99%
Question: Excel If Statement

Hi,

I am writting a simple if statement in excel. My formula is =IF(WEEKDAY(V\$2:AZ\$2;2)>5;"x";"").

The problem is excel is appending a single quote(') in each cell whether the above formula returns a true or a false.

I want to get rid of this single quote(').

I have also attached the snapshot of my excel.

Thanks,
Neeraj Jain

Your formula should be:
=IF(WEEKDAY(V\$2,2)>5,"x","")
You've used semi-colons instead of commas and referred to a range of dates when you should have referred to a single date

2 more replies
Relevance 56.99%

If A1 and B1 match a row within A3:B10, then show values of matched row in columns C, D & E. A B C D E CLASS CAT ST Rate OT Rate Sun/Hol Rate1 Laborer Group 5 2 CLASS CAT ST Rate OT Rate Sun/Hol Rate3 Laborer Group 1 \$9.00 \$13.50 \$18.004 Laborer Group 2 \$9.50 \$14.25 \$19.005 Laborer Group 3 \$10.00 \$15.00 \$20.006 Laborer Group 4 \$10.50 \$15.75 \$21.007 Laborer Group 5 \$11.00 \$16.50 \$22.008 Laborer Group 6 \$11.50 \$17.25 \$23.009 Laborer Group 7 \$12.00 \$18.00 \$24.0010 Laborer Group 8 \$12.50 \$18.75 \$25.00FOR C1, answer should be ?\$11.00?: =IF(AND(A1=A3:A10, B1=B3:B10),C3:C10,??)FOR D1, answer should be ?\$16.50?: =IF(AND(A1=A3:A10, B1=B3:B10),D3:D10,??)FOR E1, answer should be ?\$22.00?: =IF(AND(A1=A3:A10, B1=B3:B10),E3:E10,??)ORFOR C1, answer should be ?\$11.00?: =IF(AND(COUNTIF(A3:A10,A1),COUNTIF(B3:B10,B1)),C3:C10,"")FOR D1, answer should be ?\$16.50?: =IF(AND(COUNTIF(A3:A10,A1),COUNTIF(B3:B10,B1)),D3:D10,"")FOR E1, answer should be ?\$22.00?: =IF(AND(COUNTIF(A3:A10,A1),COUNTIF(B3:B10,B1)),E3:E10,"")What?s wrong with my statements. Please advise. Thanks.

Answer:Excel if and statement

Try this:With your data looking like this:
A B C D E
1) Laborer Group 5 \$11.00 \$16.50 \$22.00
2)
3) Laborer Group 1 \$9.00 \$13.50 \$18.00
4) Laborer Group 2 \$9.50 \$14.25 \$19.00
5) Laborer Group 3 \$10.00 \$15.00 \$20.00
6) Laborer Group 4 \$10.50 \$15.75 \$21.00
7) Laborer Group 5 \$11.00 \$16.50 \$22.00
8) Laborer Group 6 \$11.50 \$17.25 \$23.00
9) Laborer Group 7 \$12.00 \$18.00 \$24.00
10) Laborer Group 8 \$12.50 \$18.75 \$25.00
In cell C1 enter the formula:=SUMPRODUCT(--(\$A\$3:\$A\$10=\$A\$1),--(\$B\$3:\$B\$10=\$B\$1),(C3:C10))Then drag it right for cells D1 & E1See how that works.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 56.99%

Here is my statement:=IF(B207<=149,"6N/25L",IF(OR(B207>=150,B207<=199),"7N/25L",IF(B207 >=200,"8N/25L")))What I am trying to do is:Cell value of 149 or to less show up as 6N/25L, Cell value between 150-199 to show up as 7N/25L, Cell value of 200 or higher to show up as 8N/25L.The statement will work for the cells values up to 199. When it reaches 200 or greater it stays at 7N/25L.  Any ideas why the last part of this statement is not working?Thanks.

Answer:Help with Excel IF/THEN statement.

Are 6N, 7N, 8N, and 25L supposed to be cell references?  If so, they are not valid; you have the numerical part and the alphabetical part of the cell reference reversed.  In other words, it should be N6, N7, etc.

4 more replies
Relevance 56.99%

HiI am a math teacher. During my classes I conduct a good number of paper-based quizzes (over 40) for my students and record their marks in an Excel spreadsheet. Typically these quizzes have a negative marking system in that the student is penalized a certain number of marks for an incorrect answer. Hence a student gets +4 marks for a correct answer, -1 for an incorrect answer and 0 if the question is not attempted. The final score is calculated after deducting marks for incorrect responses, if any. A quiz always has 20 questions. I want to keep a record of both, the number of questions the students attempt and the number of their correct answer. This helps me calculate their final score. If a student, say Mary, attempted 12 questions out of which she got 9 correct, the data entry is done as 9.12 (9 point 12). There is room for two kinds of human error while someone enters this data on the computer.Error 1) The marks may be entered as 12.9 instead of 9.12.Error 2) The marks may be entered as 9.22 instead of 12 (although the max questions were only 20).I have entered the marks in cell B2 and put the formula in another cell say Z2. =(IF((B2-INT(B2))*100>20,"ERROR",IF((B2*100-MOD(B2*100,100)-MOD(B2*100,100)*100)>0,"error",INT(B2)-(((B2-INT(B2))*100)-INT(B2))*0.25)))I need to be sure of the following:i) The number of correct answers cannot exceed the number of questions attempted i.e. avoid Error 1.ii) The total attempts cannot exceed 20 (Max questions being 20; Error 2)Ideally t... Read more

Answer:Help for Excel IF Statement

I need to be sure of the following:i) The number of correct answers cannot exceed the number of questions attempted i.e. avoid Error 1.ii) The total attempts cannot exceed 20 (Max questions being 20; Error 2)This formula should work for problems 1 & 2:=IF((MOD(B2,1)*100)>20,"error",IF(INT(B2)>(MOD(B2,1)*100),"error",""))As for why your formula does not work, my best guess would be that somewhere your order of precedence is incorrect.In Excel, it multiplication and division and they have equal precedence, are done left to right in the order they come. Then addition and subtraction which have equal precedence. What is the output of the formula supposed to look like?MIKEhttp://www.skeptic.com/

7 more replies
Relevance 56.99%

I am currently trying to create a formula that returns a threat level with text like "serious" "moderate" "none" with each box filled green yellow or red based on threat level. It is for an inventory order list. I have my par level in Column C, My current inventory in D, and the needed amount in E. Do you know some sort of way I could do this?

Answer:Need help with an Excel If statement

Nevermind I figured it out

2 more replies
Relevance 56.99%

Can I ask for the correct syntax of this...=IF(AND(G35>=8000000,G10>=4000000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=7000000,G10>=3500000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=6000000,G10>=3000000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=5000000,G10>=2500000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=4000000,G10>=2000000,((G35*0.025)*0.18)*0.4,IF(AND(G35>=3000000,G10>=1500000,((G35*0.025)*0.18)*0.4,0))))))

Answer:and statement in excel

It appears that you failed to close off your =AND() functions:=IF(AND(G35>=8000000,G10>=4000000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=7000000,G10>=3500000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=6000000,G10>=3000000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=5000000,G10>=2500000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=4000000,G10>=2000000),((G35*0.025)*0.18)*0.4,IF(AND(G35>=3000000,G10>=1500000),((G35*0.025)*0.18)*0.4,0))))))MIKEhttp://www.skeptic.com/

5 more replies
Relevance 56.99%

need if statement that says if n2 is 9000.00 or less, take N2 times 2.70%, if it is greater than 9000.00, then zero.=if(n2<=9000.00,*2.70%, if>9000.00,0)

Answer:using the if statement in excel

Correction: You need an if statement that says:If N2 is 9000 or less:?) Multiply N2 by 2.7% ?) Otherwise, zero.=IF(N2<=9000, N2*2.70%, 0)EDIT: ??? It happens ???How To Ask Questions The Smart Waymessage edited by Razor2.3

5 more replies
Relevance 56.99%

I'm trying to figure out what is wrong with this formula:

=IF(D2="","", IF(D2=1,1, IF(D2=2,2, IF(E2="","",IF(E2=1,1,IF(E2=2,2,IF(E2=3,3,IF(E2=4,4,IF(E2=5,5,IF(F2="","",IF(F2=1,1,IF(F2=2,0,IF(F2=3,0)))))))))))))

Basically, if D2=1, put value of 1 and E2=1, put value of 1 and F2=2, put value of 0, so column G should have a value of 110

but with the formula above, I'm only getting 1. Please help

More replies
Relevance 56.99%
Question: Excel IF Statement

I have searched the site but cant seem to find any IF statement that could do what I am wanting. Not even sure if an IF statement is what I need to be using.I am creating an accounts spreadsheet and have each month in a separate workbook. I have created a drop down list of 8 reasons for money to move in or out. next to this will be the amount then a comment. The problem I have is that I want there to be a total in and out for each of the 8 reasons, and also a total in and out for them all and red for a loss and black for a profit. I could colour them after but this sheet will be distributed to other users to fill in. Is this something that can be done? I have made it sound a lot harder than it needs to be please contact me for any more info ThanksIan

What version of Excel are you using?  The Subtotals function comes to mind as possible technique that might serve your purpose.  A reference: http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=358

2 more replies
Relevance 56.99%

I am trying to build a report which shows if a date and time is between 9-5Column 5 has a date/time value of 04/01/2010 13:36:00, I need to output this as time and then find out if this time is between 09:00 and 17:00

Answer:Excel IF statement for 9-5

9 more replies
Relevance 56.99%

I have a set of unit bonus's and I want the computer to put the right value in the bonus screen for me. For example20 cars = \$40025 = \$50030 = \$600etc. I have the system total the cars already into a cell. So I want a formula that says if cell B61 is >=20 return \$400, if b61 is =>25 return \$500 etc.

Answer:I want and if and statement for excel

What you're technically looking for is a "Nested IF Function;" basically it is where you insert a function as an argument for another function.... =IF(B61>=30,600,IF(B61>=25,500,IF(B61>=20,400))) What I've done here is "nested" a second IF function in the "IF FALSE" argument. So, walking though the function it will read as: IF B61 is great than or equal to 30, if this is true it will return the value of 600, if this condition is false, it will preform the next IF function, asking if the value is greater than or equal to 25... and so on.A couple of concerns: Are you planning on making more "Bonus Brackets" for any persons under 20 sales (ex:11) or any persons over 30sales?? Also, are you planning on entering this function into one cell and then simply copying it over or down to other adjacent cells? if so, it may be best to use a different type of Cell Referencing for B61... If you'd like, you can PM. It would be very helpful if I could see an example of the spreadsheet. Hope this helps!! Best of luck!

2 more replies
Relevance 56.99%
Question: Excel if statement

Here is one for an expert waiting to happen!I have a two spreadsheets, Clients and Invoiceif just ONE of the cells in spreadsheet Clients!K4 to Clients!K85 = Yes then cell in spreadsheet Invoice!M15 = corresponding value in Clients!A(whatever that row number is)Can anyone please help?

he'll be online soon enough.

3 more replies
Relevance 56.17%

I have the following formula and it works fine but if I do not have anything entered into the "E" or "D" column it displays the #VALUE!. I was trying to combine the IFERROR or ISERROR function with it but cannot figure it out. Does anyone know how I can get rig of the #VALUE!

=IF(E4="10",C4*0.1,IF(E4="20",C4*0.2,IF(E4="30",C4*0.3,IF(E4="40",C4*0.4,IF(E4="50",C4*0.5,IF(E4="60",C4*0.6,IF(E4="70",C4*0.7,IF(E4="80",C4*0.8,((D4/B4)*C4)*A4))))))))

Answer:Office 365 Excel IF statement with #VALUE!

You could try an iferror around the fornula

iferror ( you if formula , "")
Will leave the cell blank

Are the numbers formatted as text

"20" assumes a text value and not a number otherwise remove the ""

E4 ="20" change to E4 = 20

1 more replies
Relevance 56.17%
Relevance 56.17%

I have from E5:E11 and F5:F11 data that is being picked up from an adjoining worksheet.In those cells E5:E11 the product words Blue, Fesc, Bfr, BSC, Bent could appearIn the next column F5:F11 is a qty of boxes by type soE5 could read Blue, F5 could read 3. E6 could read Bent, F6 could read 9E7 could read Blue, F7 could read 1etc etcI would like to do an if statement in a set of cells that tells me in those 7 rows how many boxes of each product do I have.. So one column would say Blue with the total number of boxes of blue under it etcThe worksheet where the data for E5:E11 and F5:11 change everyday.I'm sure this is probably easy for some of you but I'm stumped. Can anyone help me please?RegardsWayne

Answer:I have an excel IF statement question

I believe what your looking for is a =SUMIF() function.If your data looks like this:
E F
5) blue 3
6) bent 5
7) blue 1
8)
9) blue 4
In cell E9 enter the color blueIn cell F9 enter the formula:=SUMIF(E5:E7,E9,F5:F7)or if you don't want to use cell E9then just enter your color like:=SUMIF(E5:E7,"blue",F5:F7)MIKEhttp://www.skeptic.com/

4 more replies
Relevance 56.17%

How or what formula would I use to create an If statement that can handle more than seven nested functions?

Here's what I'm trying to accomplish,

It's a bonus tracking system. example cell A1 would equal margin budget percentage.

Achieve below 95% of budget pays \$0.00
Achieve greater than or equal to 95% and less than 97% of budget pays \$800
Achieve greater than or equal to 97% and less than 98% of budget pays \$850.
Achieve greater than or equal to 98% and less than 99% of budget pays \$900
Achieve greater than or equal to 99% and less than 100% of budget pays \$950
Achieve greater than or equal to 100% and less than 101% of budget pays \$1000
Achieve greater than or equal to 101% and less than 101% of budget pays \$1100
Achieve greater than or equal to 102% and less than 103% of budget pays \$1200
Achieve greater than or equal to 103% and less than 104% of budget pays \$1300
Achieve greater than or equal to 104% and less than 105% of budget pays \$1400
Achieve greater than or equal to 105% of budget pays \$1500

Answer:Nested If statement (Excel)

6 more replies
Relevance 56.17%

Hello all,

I am creating a Macro on Excel, that extracts the data from each tab and paste the data as text on a new Book. The problem I have is that when you run it VBA records the Book number like Book5 (if it was book 5 at the time) my case is Windows("Book11").Activate.
I need a generic BookXX number or statement that tells excel it doesent matter what book it is. So If I run the Macro again and it happends to be Book26 it would do the same thing.
Anybody can help on this?
Here is a copy of a sample macro with the ERROR opens on book14 here.

Sub asdf()
'
' asdf Macro
' Macro recorded 7/10/2007 by
'

'
Sheets("2007 IS").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=21
Range("A54").Select
Windows("TEST.xls").Activate
Sheets("2008 IS").Select
Range("A1:Q51").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book14").Activate
Range("A55").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D59").Select
ActiveWindow.SmallScroll Down:=33
End Sub

Answer:Generic Statement on Excel VBA

Hi,

I'm not sure I deciphered your goal from the macro, so I edited the code following my own thinking. Maybe you will like it, though.

Code:
Sub asdf()

Dim NewBook As Workbook
Set NewBook = ActiveWorkbook

ThisWorkbook.Sheets("2007 IS").Activate
Selection.Copy
With NewBook.Sheets(1).Range("A1")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipBlanks:=False, Transpose:=False
End With

ThisWorkbook.Sheets("2008 IS").Range("A1:Q51").Copy
With NewBook.Sheets(1).Range("A55")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, skipBlanks:=False, Transpose:=False
End With
End Sub
Have fun with it

Jimmy

2 more replies
Relevance 56.17%

How can i put the same dates upto 73 row and after that a blank second date will come ?I have 60000 rows in one sheet, and I have to put the same dates for 73 rows and a blank is there after every 73 rows.My starting date is 1/1/2012 and it continues to 26/10/2013. So I have to put 1/1/2012 in 73 rows and after a blank in next 73 rows the values will come 2/1/2013 and so on ..?

Answer:Excel Problem If statement

The easiest way would probably be to use VBA, but sorry to say, my VBA skills are just above nil.But, you could try this and see how it works for you.In the first 73 cells enter your first date, 01/01/2013Next in cell 75, because 74 is blank, enter the formula: =IF(A1<>"",A1+1,"")Next drag down 6000 cells and you should get what your looking for.Don't forget to do a Copy / Paste / Values after your done dragging down,because you only have formulas in the cells, not real values.See how that works.MIKEhttp://www.skeptic.com/

6 more replies
Relevance 56.17%

I need assistance with an excel if statement.I need to return a calculation to a given field that is based on one of five different values.  The end calculation will have a different multiplier as well.   This is being used to determine potential lease payments, based on the total purchase price, and a rate amount.   The details are as follows:Purchase price up to \$25,000 will have a .03287 multiplier, between \$25,001 and \$75,000 the multiplier is .03251, between \$75,001 and \$100,00 the multiplier is .03215, between \$100,001 and \$300K the multiplier is .03178 and between \$300,001 and \$500K the multiplier is .03178.The ending output will be a \$ amount that is calculated on an input field.  for example the total input into Cell D32 is \$58,999, the corresponding output will be \$1,918.05.What would this if statement look like?Thanks...

Answer:Excel if statement question

I have no idea as I havent worked with excel forever; just make sure you use the "IF" function

4 more replies
Relevance 56.17%

Hello, I think I'm looking for an if and or statement nested. This is what I'm looking for if s2 = yes and t2,u2 or v2 is blank then return 1.

Answer:Excel nested if and or statement

You say you want a 1 if those conditions are met, but you don't say what you want if they're not.This formula will result in a Blank cell if the conditions are not met.=IF(AND(S2="Yes",OR(T2="",U2="",V2="")),1,"")

3 more replies
Relevance 56.17%

I have an excel spreadsheet and need to create a formula that will provide the following results. I have attached the spreadsheet = the text in "G" and" H" and the result should be in "I"

shoud be = if "G" and "H"curr then "I" =curr, if "G" and "H"= prev, if "G" then "I" = curr and "H"= prev then "I" = check, if "G" = prev and "H" =curr then I =check

Could someone assist me with the formula?

Answer:Solved: Excel if and Or statement

7 more replies
Relevance 56.17%

debug an excel IF statement=if(a1<>b1,"ERROR,"")a1= 1234.56b1= c1*d1

Answer:Need to debug and excel IF statement

I know what's wrong with the formula you posted, but I'll wait until you actually ask for some help, and maybe even tell us what problems you are having.debug an excel IF statementIs that an order or a question?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

14 more replies
Relevance 56.17%

Hello all,

I am attempting to create a if/then formula

=IF(or(G4=0, G4=1, G4=2, G4<2),"110","95","80","80")

and I am getting errors.

I am not by any means an expert on excel, so any assistance would be appreciated.

the layout is this:

cell G4 will have an input of a number 0 +
Cell C16 needs to display a value based on G4 as follows:

if G4 - 0 then show 110.00
if G4 - 1 then show 95.00
if G4 - 2 then show 80.00
if G4 - 2+ then show 80.00 <- not sure how to combine this with the above one

Any help would be GREATLY appreciated

Aaron

Answer:Solved: If Then statement in Excel

7 more replies
Relevance 56.17%

I would like to have a calculation of the following. I want a cell in the Statistics tab to calculate the following B35. If any cells in I6 to I12, J6 to J12, K6 to K12, L6 to L12 then copy all the information from Cell O6 to O12.
For example 01_Interface tab I6, I8, J6, J8, J12, K6, K8, L6, L8, L12 has the text FAIL then on Statistics tab B35 should have the following text 1-VGY29, 1-5Y9UN, 1-VGY29
01_Interface tab I6, I8, J6, J8, J12, K6, K8, L6, L8, L12 has the text BLOCK then on Statistics tab B35 should have the following text 1-5K3AA, 1-5K3BB, 1-VGY29
If the Cell O has nothing do not copy. Keep in mind I will be adding more Tabs to this workbook so would like the code to be as simple as possible to use with multiple tabs without change much of the macro.
The excel file is located at www.watlaorattanaram.org/test2.zip or you can get it from the attachment

More replies
Relevance 56.17%

hi all, just cutting my teeth into the higher level of Excel and need some help.

I want to create a template ultimately to do three things:

1. calculate the avg value of a column of numbers (in attachment column "SqFtPrice")
2. calculate the median value of the same column of numbers
3. and finally for the more complicated issue: I want to pull out rows that are a certain amt UNDER the avg and median prices. For instance, the average value comes out to \$200, I would want to pull out line items (the whole row) that are \$10, \$20, \$30 below that avg value and place those rows in their own section on the sheet.

I will be copying the excel data from a txt file that will always have different amount of information so I want a dynamic template that could adapt to the different data. i attached a sample txt file to see what I mean.

i know it's a lot, but would love a nudge in the right direction!

thanks!

Attachments
http://www.mediafire.com/?hdn0w6aqia7akwh
http://www.mediafire.com/?28ru7jc1wi1y63v

More replies
Relevance 56.17%

Hi,

I was wondering if someone could help me get an Excel IF statement right. I am making a test, and I need to convert the raw score into a standard ten. Basically, I need a formula that says IF the raw score is 0 to 3 then the STEN is 1. If the raw is 4 then the STEN is 2. If the raw is 5 or 6 then STEN is 3 etc...

Is there any way to do this? The raw score is in cell A15 for example, so it will be IF A15 =

I have managed to get the if statement with true and false working, but not with multiple values.

Any help would be greatly appreciated

Thanks a lot,
Dan

Answer:Microsoft Excel IF Statement

16 more replies
Relevance 56.17%

Hi, I'm trying to figure out a formula for my boss and I'm completely stumped. Here's the situation:

A1= Any Date
B2= A Value 1-7 (Using the function WEEKDAY, where it takes the date given and spits out a value 1=Sunday, 2=Monday, 3=Tuesday, etc.)

What I want, is to skip having to change the digit to the word. I want A1=date, B1= Value between 1 and 7, and C1= the word "Monday" or "Wednesday" or whatever.

I've tried soooo many different ways to get this to work and I have no idea what to do!

My favorite attempt was:

=IF(B1=1,Sunday,IF(B1=2,Monday,IF(B1=3,Tuesday,IF(B1=4,Wednesday,IF(B1=5,Thursday,IF(B1=6,Friday,IF(B1=7,Saturday)))))))

Of course, I have Excel 2002, nice of my company to stay up to date haha

Any help would be greatly appreciated.

Thanks,

kpf103

Answer:Solved: Help with Excel If-then Statement.

Wow. Of course. I forgot my quotes around the text values.

=IF(B1=1,"Sunday",IF(B1=2,"Monday",IF(B1=3,"Tuesday",IF(B1=4,"Wednesday",IF(B1=5,"Thursday",IF(B1=6,"Friday",IF(B1=7,"Saturday")))))))

That worked so nevermind! haha

2 more replies
Relevance 56.17%

I need help with an excel spreadsheet and an IF statement. I have 5 columns with numbers from 0 through 9. If the value in the cell is "0," instead of "0," I want the cell to display "KP 0" If it is any value from 1 through 9, instead of just what the value is, I want it to display, "Keypad (whatever the value is from 1-9)" like this: "Keypad 1" or "Keypad 7" etc.

Can someone help me with the correct formula? I am doing this so that I can then have those columns populate another spreadsheet with the rest of the code for a macro I am using in a clientele program. Ultimately, I want to paste code into a macro to run each time I need it to, but those values (the 1-9 values) will change daily, so I need to be able to paste it and then run it each day. The code ultimately looks like this:
Sess0.Screen.Sendkeys("REV<Ctrl+M>")
Sess0.Screen.WaitHostQuiet(g_HostSett
Sess0.Screen.Sendkeys("2<Ctrl+M>")
Sess0.Screen.WaitHostQuiet(g_HostSett
Sess0.Screen.Sendkeys("<KP 0><Keypad 1><Keypad 6><Keypad 7><Keypad 6><Ctrl+M>") <-- these are the values 0-9 that need to display as "KP," (if 0) or "Keypad," (if 1-9)
Sess0.Screen.WaitHostQuiet(g_HostSett
Sess0.Screen.Sendkeys("H<Ctrl+M>")
Sess0.Screen.WaitHostQuiet(g_HostSett

Answer:Solved: Excel IF statement help?

Why dont you just add this to your other post, in a certain way you're duplicating issues.

1 more replies
Relevance 56.17%

Hello, For the following times, Time 1 Time 216:00 18:3916:00 15:5018:30 19:1215:30 13:1615:30 16:0010:55 11:1509:52 10:0814:00 11:15If (time 2) is greater than (time 1), then I want the difference? If not, I want the output to be zero...I tried the following formula =IF(B2>A2,B2-A2,0)but the output appears to be zero all the time and I wonder why? if anyone could help me, I would really appreciate it..thanks

Answer:how to use If statement for times using Excel

Works for me:
Time 1 Time 2 Diff
16:00 18:39 2:39
16:00 15:50 0:00
18:30 19:12 0:42
15:30 13:16 0:00
15:30 16:00 0:30
10:55 11:15 0:20
9:52 10:08 0:16
14:00 11:15 0:00

If your seeing this:
Time 1 Time 2
16:00 18:39 0.110416667
16:00 15:50 0
18:30 19:12 0.029166667
15:30 13:16 0
15:30 16:00 0.020833333
10:55 11:15 0.013888889
9:52 10:08 0.011111111
14:00 11:15 0

Then Custom Format your totals to [H]:MMMIKEhttp://www.skeptic.com/

2 more replies
Relevance 56.17%

I am working in Excel 2007 on a machine that runs Windows XP and am trying to create an IF/THEN statement and I'm stumped!

There are four columns which are:
Price (I8)
Included (J8)
Running Total (K8)
Running Balance (L8)

Inside the INCLUDED column is a drop-down box which will allow you to choose an "x" if applicable. What I would like to happen is that if I choose the "x" then the PRICE will not be included in the RUNNING TOTAL or the RUNNING BALANCE. If there isn't an "x" in the INCLUDED box then the PRICE would be added to the RUNNING TOTAL and the RUNNING BALANCE.

Thanks so much!!!!

Answer:Solved: Excel IF/THEN Statement

12 more replies
Relevance 56.17%

Hello,

I'm trying to make a football (soccer) table using the if statement to display whether a side has won, drawn or lost.

This is the current IF statement I'm using,

=IF(R3>S3,"W","L")

As you can see I can only make the statement display W and L

I know that if I put R3=S3,"D", that will show D but I do not know how to make an if statement show 3 results.

If someone could assist that will be great thanks.

Answer:Excel IF statement with 3 outcomes

Hi Alex, and welcome to PC Review

You need another IF function inside your IF function. This should work:

=IF(R3>S3,"W",IF(R3<S3,"L","D"))

3 more replies
Relevance 56.17%

I have a cell with =IF(K22<5,20,0). but i would like to tell it if it = 0 then put 0 in that cell... anyone?

Answer:Solved: excel if statement

6 more replies
Relevance 55.76%

In excel 2007 I have a budget sheet that is by day for the entire year in the first column, and then next to that I have a column for expenses. I need the formula to look at another spread sheet and search for a a specific date within a column, so lets say the 1st of the month. If the date is there I then need the sales amount that is on the same row but different column associate with the date to then populate that amount onto the expense column in the first spreadsheet.

Answer:Excel 2007 not sure if I can use Vlookup and if statement

So why do you think that you can't use VLOOKUP?Have you tried it?As long as the dates in both spreadsheets match exactly, and the dates in the "second" spreadsheet are in a column to the left of the expense column, it should work.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 55.76%

I am trying to crete a form with a drop down list to select the following: So if cell A1 says "Alcohol" then in cell B1 show a drop down list of the Countries that ship Alcohol. If selected A1Biological Substance then B1 show a drop down list to show at all countries that ship biological substance, and if A1 says Frozen Fish then b1 shows all countries related to Frozen Fish and so on. Can anyone help?

Answer:Excel If statement & Data Validation

So A1 could be Alcohol, Biological Substance, or Frozen Fish, and you need the drop down list to change depending on what is entered in A1?If A1 was always Alcohol, it would be easy to make a dropdown list of countries that ship Alcohol.If you need this list to be dependent on the value in A1, then you need a dependent list. This should give you everything you need. http://www.contextures.com/xlDataVa...

2 more replies
Relevance 55.76%

I am hoping someone here can help me - I'm seriously about at my wits end with this whole formula right now.

Here's what I've got

See attached file for details

I need to figure out a way for the results of a formula to say IF COUNTIFS column = TRUE then display the next Matrix Category the Function Number appears in.

Can anyone help?

Answer:Solved: NEED HELP! IF THEN ELSE Statement in Excel 2007

Hello.

I don't know COUNTIFS. And not sure I understand your requirement. But apart from that ( ) ... does:

=IF(C2=TRUE,OFFSET(A2,MATCH(B2,INDIRECT("B"&ROW()+1&":B\$100"),0),0))

in D2 and copied down work for you?

(change B\$100 to however far down your list goes)

1 more replies
Relevance 55.76%

My situation is I want to take three types of a location and if at each three it is either a poster or light then it will be this cost:Locations:PP&SAllAt location either:poster or lightthen if it is say P with poster then it is = 28...so each is if:P - poster - 28P - light - 20P&S - poster - 10 +28P&S - light - 75+20All - poster - 21all - ligh - 150How can I make this an excel if statement for one box based on two drop down lists for location and if poster or light.

Answer:Trying for a multi level if statement in Excel

Assuming A1 contains your location and A2 contains "poster" or "light"...=IF(A1="P",IF(A2="poster",28,20),IF(A1="P&S",IF(A2="poster",38,95),IF(A2="poster",21,150)))Since Excel evaluates formulas from left to right and stops when it find a TRUE condition, we don' have to check for every combination.For example, once A1="P" is determined to be TRUE, we only have to check for "poster". Since you are using data validation, if the value isn't "poster" then it must be "light" so we don't have to specifically check for "light".The same hold for "ALL". If A1 isn't "P" or "P&S", then it must be ALL, so the only thing that needs to be checked is A2. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 55.76%

Have an Excel formula =SUM(Q\$2:Q4)+M4 but am unsure how to add to it so it does the following:

I want write an IF statement and add it to the above formula so that if the Cell "M4" is blank i.e. no numbers or text in it - then the formula should produce "0" or be blank also.

Thanks in advance, have been messing around with this for a while.

J

Answer:Solved: Excel formula IF statement help

peace be upon you
Try this

=IF(ISBLANK(M4),"",SUM(Q\$2:Q4)+M4)

best regards

2 more replies
Relevance 55.76%

I have autopopulated a sheet basing a data sheet using vlookup formula.
Help needed to autopopulate a statement. Please find attachment.

Answer:Solved: Excel Help to autopopulate a statement

10 more replies
Relevance 55.76%

HOW CAN I PROBABLY GET THE SUM OF THE 2 IF STATEMENT?MY IF STATEMENTS:=IF(A1=90,"1","0") MEANS THE STATEMENT SHOWS 1 IF IT IS CORRECT "0" IF WRONG=IF(A2=40,"1","0") MEANS THE STATEMENT SHOWS 1 IF IT IS CORRECT "0" IF WRONGAND IF I ADD THE 2 IF STATEMENT ITS ALWAYS SHOW 0 =SUM(A3,A4) I NEED IT TO BECOME 2 BECAUSE 1 + 1 = 2 :D BUT I ALWAYS GET 0I ALREADY ANSWER IT CORRECTLY???SORRY FOR MY BAD ENGLISH... HOPE YOU CAN HELP ME....

Answer:Adding the if statement i always get zero (ms excel 2007)

When you put the quotes around the "1" and "0", Excel interprets them as text values, not numbers.Some mathematical functions will work with text, others won't.For example, using your formulas, =SUM(B1:B2) will return 0, but =B1+B2 will return 2.To ensure that all mathematical functions will work, remove the quotes.=IF(A1=90,1,0)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 55.76%

This formula is a working formula that returns the correct value based on what is entered in C6=IF(C6<=56000000,"Small",IF(C6>=125000000,"Large","Medium"))However, when C6 is zero or blank the formula returns the value Small; I want it return nothing ""How can I adjust formula to return blank when C6 is zero or blank

Answer:Excel IF statement help to return a blank

You will need to use the OR() function if you want to check for both Blank and Zero,one is not equivalent to the other.Something like this should work:=IF(OR(C6="",C6=0),"",IF(C6<=56000000,"Small",IF(C6>=125000000,"Large","Medium"))) MIKEhttp://www.skeptic.com/

8 more replies
Relevance 55.76%

I am trying to do an if statement to include a data validation list. So if cell A1 says "Plasma" then in cell B1 show a drop down list of the size of plasma's. If A1 says anything else then I don't want the drop down list to show at all because they will need to ensure some other text. Can anyone help?

Answer:Excel If statement & Data Validation

Hi,Try this:In Cell G2 enter Plasma (no quotes around it and no equal sign)In cells H2 to Hx enter a list of allowable Plasma valuesSelect the cells H2 to the last Plasma value entry in column H, and in the address box (the area that shows the cell address just above the A of the first column, replace the single cell address with Plasma (no quotes around it), then hit Enter while the cursor is still in the box. In cell I2 enter No selectionIn cell A1 add data validation as a List and enter: =\$G\$2:\$G\$3 in the 'Source:' boxIn cell B1 add data validation as a List and enter: =IF(\$A\$1="Plasma",INDIRECT(\$A\$1),I2) in the 'Source:' boxNow when Plasma is selected in cell A1, cell B1 will have a drop-down list containing the list of available Plasma values.If A1 is blank or a value other than Plasma, the drop-down in B1 will show "No selection"This works by the data validation in B1 using the formula:IF(\$A\$1="Plasma",INDIRECT(\$A\$1),I2) - so if A1 contains the word Plasma it uses the INDIRECT function to return the range named Plasma, which is the list of values in column H. If the value in A1 is not Plasma, the text in cell I2 is used for validation - in this case the text "No selection".Regards

4 more replies
Relevance 55.76%

Hello,I'm making a spread sheet and in cell J14 I have a value that will hold an amount. In cell K14 I would like to add an if statement that will calculate the following:If the value is less than is less than 18, then leave it blank. If the value is between 18 and 23 ,put "coaching". If the value is between 24 and 29, put "verbal". If the value is between 30 and 35, put "written". If the value is between 36 and 38, put "90Day". If the value is 39 or greater, put "termination". Can anyone help me out with this?Thank you.

Answer:Excel Nested If Statement Question

Hi,Cell J14 contains the value to be testedCreate a table in cells C1 to D6 as follows: C D
1 0
2 18 coaching
3 24 verbal
4 30 written
5 36 90Day
6 39 termination
In cell K14 enter this formula:=VLOOKUP(J14,\$C\$1:\$D\$6,2)The text requested will show in cell K14.You can of course have the table anywhere, even in a different worksheet, just make sure the formula points to it, e.g., =VLOOKUP(J14,Sheet3!\$C\$1:\$D\$6,2)If you prefer nested IFs then use this:=IF(J14<18,"",IF(J14<24,"coaching",IF(J14<30,"verbal",IF(J14<36,"written",IF(J14<39,"90Day","termination")))))The VLOOKUP() version is better if you are going to use this formula in more than one place, then if you need to change the break-points or the text, you only have to make one change (in the table), rather than changing every formula. Regards

2 more replies
Relevance 55.76%

I have a drop down list (A14) with different ports and depending on which port is selected, cell C16 gives you the corresponding value. The problem I face is that, in cell D16, there are varying "minimum" calculations that need to be arrived at depending on the port of choice as there are different rates for each port. Is there an IF function, Choose function, Data Validation or any combnation that I can use complete the calculations in D16?

Answer:Excel IF Statement & Data Validation

7 more replies
Relevance 55.76%

First I am trying to figure out a way to see if there is a Date entered into (A2) and then check to see if there is a Date in (B2) and then a (C2) and a (D2) and based on the answer, place the latest date into E2 and then calculate the number of days/years in between those dates.For instance in this example? (A2) has a date but (B2), (C2), & (D2) do not. So the lastest activity is 2/26/2001 and that needs to be placed into (E2)A2 = 2/26/2001B2 = NullC2 = NullD2 = NullE2 = 2/26/2001In this example? (A3) has a date and (B3) has a date but (C3), & (D3) do not. So the lastest activity is 2/23/2005 and that date needs to be placed into (E3) A3 = 11/12/1999 B3 = 02/23/2005 C3 = Null D3 = Null E3 = In this example? (A4) has a date, (B4) has a date, (C4) has a date but (D4) does not. So the lastest activity is 5/3/2011 and that date needs to be placed into (E4) A4 = 11/12/1999 B4 = 02/23/2005 C4 = 5/3/2011 D4 = Null E4 = 5/3/2011

Answer:MS Excel formula IF statement needed?

Put this in E4. It will return a blank cell if there is no dates in the range:=IF(COUNT(A4:D4)>0,MAX(A4:D4),"")The double-quotes return a blank cell. Anything you put between the double quotes, e.g. "No Data", will be treated as a text string. Since there is nothing between "", the formula will return "nothing". Technically, it has to return something, so it returns a blank cell.Put this in F4 and drag it to H4:=IF(COUNT(A4:B4)=2,DATEDIF(A4,B4,"y") &" years, "&DATEDIF(A4,B4,"ym") &" months, " &DATEDIF(A4,B4,"md") &" days","")The columns will increment as you drag it to the right, so it will always be checking to see if both cells contain data via the COUNT function.Just an FYI...re: "the following formula "=MAX(A2:D2)" does not work when all these cells are empty. It says "1/0/1900""Technically, it works just fine. Here is the logic:- If all cells are blank, the MAX function returns a 0.- Dates are stored in Excel as integers and Times are stored as the decimal portion of the Date number.e.g. 4/9/2017 10:05AM is stored internally as 42834.42035- Day 0 in Excel is 1/0/1900, Day 1 in Excel is 1/1/1900, etc.e.g. 4/9/2017 10:05AM is 42834.42035 days since 1/0/1900 00:00AM (the beginning of time as far as Excel is concerned.)- If a cell contains a 0, e.g. =MAX(blank cells) and then formatted as a Date, Excel will see that as Day 0 and display 1/0/1900.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

13 more replies
Relevance 55.76%

i use the iferror a lot in 2007 to give me a blank or a 0 when a formula has an error.. it doesnt exist in 2003 what is a solution.. for instance iferror(k6/g6," ")

Answer:iferror statement in excel 2003

How about:=IF(ISERROR(K6/G6)," ",K6/G6)BTW, this will return a space if there is an error, as will your IFERROR function above.If you want a blank cell you should use:=IF(ISERROR(K6/G6),"",K6/G6)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 55.76%

I have 2 sheets on my workbook what I want to do is compare (Sheet2)C = (Sheet3)V and if the result is true then copy (Sheet2)D text into (Sheet3)W.

=IF(V2=Sheet3!C2, Sheet3!D2, "") but the result I had is always FALSE or ""

Columns C and V are numbers

Sheet2
C D

0001 Computadoras de Escritorio
0001 Computadoras de Escritorio
0004 Workstations
0004 Workstations
0005 Servidores para computadora
Sheet3
V W

0001
0001
0001
0004
0005

I don't know how to solve this so any help will be appreciated.

thank you in advance

More replies
Relevance 55.76%

I am trying to get make a macro/script to hide cells based on what cells on another sheet are filled.

If A1 is filled in sheet 1 then I want 2 columns to show on sheet 2
If A2 is filled in sheet 1 then I want 4 columns to show on sheet 2
Etc.

I currently have macros to hide the appropriate columns and have them assigned to buttons. I want to eliminate the buttons and have sheet 2 automaticly format based on which cells on sheet 1 are filled.

Any suggestions? I am sure I will hvae to use VB. I think I can get it to run a macro if a cell is filled but how do I get it to run based off of the highest cell thats filled. If A5 is filled then use macro for 10 columns ignoring cells A1-A4.

Hope this makes sense.

Answer:Solved: Excel, VB, IF Statement to Run Macro

11 more replies
Relevance 55.76%

I have confused myself trying to write what more experienced people would say is a simple IF formula.

It is easier to show than explain.

Row 1 If this number is "better" than either/or both Row 2 or Row 3 enter Pass in Row 4. If it is "inferior" to BOTH enter Fail.
Row 2 This number could be positive or negative
Row 3 This number could be positive or negative
Row 4 Pass or fail

OK? These are simple percentages but I gotten brain lock.

If Row 1 has gained more or lost less then either it is Pass.
If Row 1 has gained less or lost more that both it is Fail.

EXAMPLES
Row 1 8% -6% -1% 10%
Row 2 7% 0% -2% 11%
Row 3 9% -5% 2% 12%
Row 4 Pass Fail Pass Fail

I hope I have explained it clearly but I have confused myself and nested IF statements until my eyes ache.

Thanks

Answer:Solved: Excel IF statement more than iffy

=IF(OR(A1>A2,A1>A3),"Pass","Fail")

Rgds,
Andy

1 more replies
Relevance 55.76%

Never mind...

Answer:Excel: Nested If Function/Statement Help?

Why not just create a user defined function that utilizes a select statement to evaluate the value in question??

Regards,
Rollin

1 more replies
Relevance 55.76%

Hello, I need some help with an excel sheet that I'm doing. I need an IF statement... IF columnA contains a certain value, then copy corresponding row values from columnB, columnC, columnD to another sheet (or preferably a different spreadsheet). Thanks for any help, this is my first time posting at this site.

Have you looked at the VLOOKUP function?VLOOKUP can search the first column of a table and return values from other columns in the same Row. e.g.=VLOOKUP(your_value, Sheet1!\$A\$1:\$D\$10,2,0)will return the value from Column B of Sheet1 in the same Row that your_value was found.You should note that the "2" in the formula does not correspond to Column B specifically, it refers to Column 2 of the table A1:D10. If the table was F1:H10, the 2 would refer to Column G, the 2nd Column in the table.Search Google for VLOOKUP to get a more detailed explanation and some examples.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 55.76%

IF \$500*3% IS GREATER THAN \$20, \$20, IF \$500*3% IS LESS THAN \$20, 3% HOW DO I DO THE FORMULA TO ENTER THIS IN MY SPREADSHEET?A4 \$500 B4 \$20 C4 ???

Answer:Excel Formula Help: IF Statement With Percentages

First, a couple of posting tips...1 - Please do not post in all caps. All caps is the internet equivalent of yelling, and nobody likes to be yelled at.2 - When posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "Excel formula help needed" we wouldn't be able to tell one question from another and the Archives would essentially be useless. I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used.Thank you for your understanding.DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 55.76%

I have user form entering information into database. In the database I want to put the date and time into next column. If there is something in data column put the date and time (in 2 columns ?) if nothing there put "" .Trying : =IF(P9>=0,"(today)","") but this only puts text in cell 'today'.What have I got wrong. Have tried a few other combinations without luck.

Answer:Excel 2000 If' Statement w/Date

Hi,You have "(today)" inside double quotes.Excel treates text inside these double quotes as text - hence you get (today) displayed in the cell.The Excel function is TODAY() (no quotes)The formula becomes: =IF(P9>=0,TODAY(),"")This will likely show as a number such as 4040940409 is the Excel date number for 19 August 2010You can then format the cell with a date format such as dd/mmm/yyYou can use NOW() to get date and time:=IF(P9>=0,NOW(),"")then format something like this: dd/mmm/yy hh:mmThere is also a problem with how you have used IF to test for an empty cell.Empty cells often get treated as containing zero - Excel does a lot of 'implicit' conversions.You IF function test is TRUE for empty cells - which is not what you want.Try this: =IF(P9<>"",NOW(),"")An alternative is to test if there is a number in the cell: =IF(ISNUMBER(P9),NOW(),"")There is one other issue regarding using TODAY() or NOW() - both are 'volatile' functions, which means that they re-calculate every time any cell on the worksheet changes. This limits their use for time- or date-stamping.It is possible to use a Visual Basic macro to time- or date-stamp cells. If that is what you need please look at this post ...and come back if you need more help.Regards

4 more replies
Relevance 55.76%

trying to write a simple IF statement.=if(B2>9:45,"Late","Good").When doing this it keeps giving me #VALUE! result. When i change to read just>945 it work but i wont to know how i can stick with using times and not numbers.If anyone can help i would greatly appreciate it.Thanks

Answer:how to write if statement in excel for state

=IF(B2>TIMEVALUE("9:45"),"Late","Good")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 55.76%

I was wondering if anyone could help me solve this little problem. I am producing a test where you have a set of questions, and there are 8 statements for each question. You have to give an answer of 1 to 10 for each statement depending on how much you agree with it, and you have to scatter the 10 points evenly over the 8 staments to add up to 10 (I hope this makes sense). I was wondering on what would be the best way to do this? I will make a cell at the bottom of each question adding up all the points, but how can I make the cell go red if you go over 10, and red if you go under? Also, I want to make it say you have gone over or under the 10 points. I'm sure it can be done, I just don't know how.

Thanks a lot for any help!

Dan

Answer:Excel IF statement type problem

9 more replies
Relevance 55.76%

I am looking to make an IF statement for the following:In cell J1 there is a date in it and I want I1 to read "Calibration Due" if the date in column J is less than or equal to 45 days from today's date, and remain blank if this is not true.Any help would be great!Thanks!

Answer:IF statement in Excel using today's date

Try:=IF((J1+45)>=TODAY(),"Calibration Due","")MIKEhttp://www.skeptic.com/

4 more replies