Microsoft Excel Forum

Forums

Microsoft Excel Forum

This forum is for the discussion of Microsoft Excel.

Click Here To Subscribe to this forum and receive an email update whenever new posts are added, just scroll down to the bottom of this page and enter your email address in the comment form.

Permanent Link
Keywords: microsoft excel forum

Post New Topic

Read Using Time in an if statement by ROWENA S @ 9/18/2012

In the example below
Column 1 is start time
Column 2 is end time
Column 3 is duration
Column 4 is =IF(E3<30,"Y","N")
What is wrong with my formatting that this does not work properly.
Line 2 is obviously wrong.
Help!

1:49 1:53 0:04 Y
3:13 3:49 0:36 Y
12:48 12:53 0:05 Y
13:47 13:50 0:03 Y
8:35 8:36 0:01 Y

Reply from Richard Rost:

Remember, TIME is a fraction of a DAY. So if 1 equals a whole day, then .5 equals 12 hours. If you are checking minutes, you are actually checking (1/24/60). So 30 minutes is (1/24/60*30). If you compare the value to 30, you're saying 30 DAYS. Use the techniques I show in Excel Expert 2 to calculate the difference between two times.

Show Just This Thread Post Reply

Read IF Statement by William Clark @ 5/6/2012

How do i set up a If statement If i have a sum over 300 i substract 360

Reply from Richard Rost:

If your value is in A1, you would say in B1:

=IF(A1>300,A1-360,A1)

If you were to translate that to BASIC, it would read:

IF A1 > 300 THEN
B1 = A1 - 360
ELSE
B1 = A1
END IF

I cover this in my courses on IF statements.

Show Just This Thread Post Reply

Read if statement by Chris @ 7/27/2010

Trying to write this formula but can’t get it to work, see below

If column “B” has a N/A then put a 0 in column “C” if “B” does not equal N/A populate “C” with whatever is in column “A”
Amount Ending Balance Total
-4,668.91 3508.23
-0.42 8177.14
-818.48 N/A
-8,128.44 8177.56
+2,349.91 16306.0

Answer from Richard Rost:

Sounds like it should be:

C1: =IF(B1="N/A",0,A1)

Easy enough?

You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells.

See this FREE tutorial on my web site for more information on how to use the IF function:

http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=BlFAlEx

Show Just This Thread Post Reply

Read If Statement by Mike @ 2/22/2010

Thought this might be a little easier than vlookup.. Replace the letter with number value you want without the " "
=IF(A1>=95,"A",IF(A1>=75,"B",IF(A1>=40,"C","D")))

Reply from Richard Rost:

Mike, you are absolutely correct. An IF statement in this case would work just fine. Personally, I try to push people into VLOOKUP whenever possible if there are more than 2 values to check because when you start talking about 6, 7, 8, values, then you should be using VLOOKUP. IF statements tend to get real clunky.

Show Just This Thread Post Reply

Read If Statement by Jonny @ 2/11/2010

My question is, i need an if statement to assign 4 different numbers if the number in the reference cell is greater then or less then a certain number. (00-39, 40-74, 75-94, 95-99)

Answer from Richard Rost:

You could use a complex IF statement, but you might be better off with a VLOOKUP statement. Check out this tutorial:

You can use the VLOOKUP or HLOOKUP function.

VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table).

See this FREE tutorial for help with VLOOKUP:

http://599cd.com/tips/excel/vlookup/?key=BlFAlEx

Show Just This Thread Post Reply

Collapse All Topics

Post Your Comments or Subscribe
If you would like to be notified of new posts on this forum,
just enter your email address below. It will be kept private.

If you just want to subscribe to get email updates when this forum is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
Your Name:	Shown
Your Email:	NOT Shown
Subject:
Comments:
	Sorry about this step. It's just to keep the spam bots away:
Verify:	What is 2+7:

	Notify me when the this forum is updated
	Remember Me for my next comments


Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead. I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be. As always, I promise to never give away your personal information to anyone else, ever.