Previous Topic Next Topic

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
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
Related:

12 responses

Answer 1 / 12
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
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 .......
8
earthworm Posts 11 Registration date Saturday April 4, 2009 Status Member Last seen December 31, 2009 1
Apr 28, 2009 at 02:24 PM
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 :(
0
Answer 2 / 12
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
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")
4
Answer 3 / 12
aimheart
Sep 8, 2009 at 04:02 AM
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??
1
Answer 4 / 12
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
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.
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 12
earthworm Posts 11 Registration date Saturday April 4, 2009 Status Member Last seen December 31, 2009 1
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.
0
Answer 6 / 12
earthworm Posts 11 Registration date Saturday April 4, 2009 Status Member Last seen December 31, 2009 1
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 :(
0
Answer 7 / 12
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
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 .....


0
Answer 8 / 12
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
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






0
Answer 9 / 12
aimheart
Sep 8, 2009 at 04:44 AM
Perfect. Just the job, thanx.
0
Answer 10 / 12
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
Sep 8, 2009 at 04:46 AM
u welcome
0
Answer 11 / 12
sns
Dec 12, 2010 at 10:37 PM
how to input a value of cell in second option with function if
0
Answer 12 / 12
earthworm Posts 11 Registration date Saturday April 4, 2009 Status Member Last seen December 31, 2009 1
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
-1
rashel
Sep 3, 2010 at 02:32 AM
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
0

Similar discussions

"Sorry, this feature isn't available ... " how to fix this?
Anonymous User -
heclam -

3 responses
How to disable Fn keys
HENRY -
ZiaRahman -

30 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
If cell A1 has text then cell B2 has today's Date
Sasha -
ac3mark -

1 response