IF Advance Function [Solved/Closed]

Report
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
-
 sns -
Hello,

Ok here is the situation

Data is

9:00 am
4:30 pm

Now i want the If Formula to function , that if the range between 9:00 am to 4:30 pm happens the value turns to
true , but if the range exceed like 4:31 pm , i want the false value to reflect

in other words

Example

A1 = 9:00 AM
A2 = 4:30 PM

any time like before 9:00 am and after 4:30 pm should be false and data between 9-4:30 should be true.

Waiting for expert opinion.

ALSO PLEASE HELP ME WITH THIS

How can I have my entire row highlighted using the conditional format
function based on the value of only one cell? I can only get the single cell
to highlight.

My data is simple:
PO# Inv# Amt Status
ABC 123 50.25 Partial

When the status reads "Partial" I want the entire row to be highlighted, not
just the cell that reads "Partial"

Please Do example the calclation in steps for easy understanding

12 replies

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
Hi,


A1 = 9:00 AM
A2 = 4:30 PM

Formula=IF(AND(A1>=0.375,A2<=0.1875),"TRUE","FALSE")


First of all write in B1 = value(A1) = 0.375
THEN B2= VALUE(A2) = 0.1875

Theses are the values of 09:00 AM & 04:30 am. Now you can use them in Formula.




How can I have my entire row highlighted using the conditional format
function based on the value of only one cell? I can only get the single cell
to highlight.

My data is simple:
PO# Inv# Amt Status
ABC 123 50.25 Partial

When the status reads "Partial" I want the entire row to be highlighted, not
just the cell that reads "Partial"



Suppose you have data from A2 to D20 .....

1. Now first select cell A2 and select the whole data upto D20 by dragging mouse ( hope you can do it easily) but make sure your first cell is A2 ..
2. Now go to format > conditional formatting > Condition 1 -- Formula is and now write this =$D2="Partial"
3. select any color and now your rows will be highlighted .......
8
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3983 users have said thank you to us this month

earthworm
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
OK I am confused

IF(AND(A1>=0.375,A2<=0.1875),"TRUE","FALSE")

Y did u use these 2 Bold values instead i would have write it simple time , and if u did how did u get this value

secondly , Please Advice the function of "AND" in excel

Thirdly , i m using EXCEL 2007 , where to put this formula

=$D2="Partial" What does this formula mean

can you please elaborate

Please Upload the excel for both the above example and provide me the link so that i can get an idea

PERHAPS A VERY SIMPLE EXAMPLE WITH "AND" FUNCTION WILL BE EASY TO UNDERSTAND .

I TRIED THIS HIGHLIGHT FUNCTION IT DIDNT WORK :(
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
Ok here is another solution with details ........ now u can put any date in column C2 and result will be shown in C3 ....... but keep in mind that A1 value must be less then A2 ..........

A1= 9:00 AM		
A2= 4:00 PM	C2=08:59 AM
		         C3=IF(AND(C2>=VALUE(A1),C2<=VALUE(A2)),"TRUE","FALSE")
Ok I have a question!

If the value in A2 is less than or equal to 75 I want cell A1 to change to red format. Does anyone know how to do this??
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
0.375 is the value of 09:00 AM which i got from writing in Cell B1 as B1 cell = value(A1) = 0.375 where A1 cell is equal to 09:00 AM

AND function will return True if the Time will be between 09:00 AM & 04:30 PM. So when both conditions will true then True part will work else it will return False.


A1 = 9:00 AM
A2 = 4:30 PM

Formula=IF(AND(A1>=0.375,A2<=0.1875),"TRUE","FALSE")


First of all write in B1 =
value(A1) = 0.375
THEN B2= VALUE(A2) = 0.1875

Theses are the values of 09:00 AM & 04:30 am. Now you can use them in Formula. Hope you have got it ......

About =$D2="Partial" you will have to write it in Conditional formatting box ... i've 2003 so hard to tell you about 2007.
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
u r not getting the point

this foruma which u gave me is not working

let me show u

A1:9:00 AM
A2:4:00 PM


C2: This is where when i put the data , Then i want the cell c2 to checks if the data is between range 9:00 AM and 4:00 PM if the Result is true /false i want the result to be displayed in cell C3

C3: THIS IS WHERE I WANT THE RESULT TO BE DISPLACED after i input that data in C2


now if u were my place , which formula will u put in cell C3 ?

DATA SUMMARY
A1: 9:00 AM
A2: 4:00 PM

A1:A2 IS complete data range
C2: Where i input the value that checks from value A1:A2
C3: I want the true/false result from the Value which i input in cell c2

Waiting for expert advice.

BTW I follow that highliig the formula like " formula is " =$A#$1 if i remove the $ before 1 i get two rows highlted at one.
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
One more thing

C3=IF(AND(C2>=VALUE(A1),C2<=VALUE(A2)),"TRUE","FALSE")


How can i hide the "false" when no value is entered . i want the cell to show neither true/false when no value is intered

any modification on the above formula

IF(ISNA DOSENT WORK :(
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
If A1 or A2 is empty then use this formula ......

=IF((OR(A1="",A2="")),"",IF(AND(C2>=VALUE(A1),C2<=VALUE(A2)),"TRUE","FALSE"))

About highlighting the row you should give thre reference cell as $A1 keeping in mind that your desired result is in Cell A1 , A2 and so on ........but one thing if the same value existing in two rows then two rows will be highlighted .....


Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
Select cell A1
Go to Format
Conditional Format
Condition 1: Select Formula Is : $A$2<=75
Adjust the fomrat from format Tab of Condition 1






Perfect. Just the job, thanx.
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
u welcome
how to input a value of cell in second option with function if
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
PERFECT

FINALLY U SOLVED MY QUERY

THANK YOU VERY MUCH :)


Also one more thing

in excel 2003 or excel 2007 how i can i highlight an entire row if the value matches from the single cell in conditional formatting . i tried for example by default the formula is =$A$1 so i changed to = $A1 but that shows two highlited rows at same time , which is not my desired result.

waiting
i use google spreadsheet. my problem is
in col A1 have time like 0:0:14 and want
if time(a1 cel) is less than 20sec than b1 background color is green, if time(a1 cel) is less than 10sec than b1 background color is yellow