IF Advance Function
Solved/Closed
earthworm
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009

Apr 27, 2009 at 10:15 AM
sns  Dec 12, 2010 at 10:37 PM
sns  Dec 12, 2010 at 10:37 PM
Related:
 IF Advance Function
 Network card function  Guide
 Free fire advance server  Guide
 Function key stuck on  Guide
 Agp function  Guide
 Find function on mac  Guide
12 responses
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Apr 28, 2009 at 02:17 AM
Apr 28, 2009 at 02:17 AM
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 .......
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 .......
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Apr 30, 2009 at 03:57 AM
Apr 30, 2009 at 03:57 AM
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??
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??
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Apr 28, 2009 at 11:42 PM
Apr 28, 2009 at 11:42 PM
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.
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.
Didn't find the answer you are looking for?
Ask a question
earthworm
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
Apr 29, 2009 at 02:57 PM
Apr 29, 2009 at 02:57 PM
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.
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.
earthworm
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
Apr 30, 2009 at 01:18 PM
Apr 30, 2009 at 01:18 PM
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 :(
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 :(
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
May 5, 2009 at 10:30 PM
May 5, 2009 at 10:30 PM
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 .....
=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 .....
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Sep 8, 2009 at 04:34 AM
Sep 8, 2009 at 04:34 AM
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
Go to Format
Conditional Format
Condition 1: Select Formula Is : $A$2<=75
Adjust the fomrat from format Tab of Condition 1
mubashir aziz
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
165
Sep 8, 2009 at 04:46 AM
Sep 8, 2009 at 04:46 AM
u welcome
earthworm
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
Apr 30, 2009 at 09:49 AM
Apr 30, 2009 at 09:49 AM
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
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
Apr 28, 2009 at 02:24 PM
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 :(