Search : in
By :

IF Advance Function

Last answer on Sep 8, 2009 9:46:07 am BST earthworm, on Apr 27, 2009 3:15:55 pm BST 
 Report this message to moderators

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

Configuration: Windows XP
Internet Explorer 8.0

Best answers for « IF Advance Function » in :
Boost disk access- Deactivate Access time ShowBoost disk access (Deactivate access time) Under Windows 2000/XP/2003/Vista (NTFS only) From a command line Fromthe registry Under Linux Operating system Almost all operating systems use existing file systems with advanced functions:...
[VBA] A function that returns the color of an active MFC Show[VBA] A function that returns the color of an active MFC This function returns the value of the active format in conditional formatting. With the function below, two values can be returned. Mode = 0: the value of Interior.ColorIndex...
[Myth] aMSN allows you to know if someone has blocked you on WLM ShowaMSN allows you to know if someone has blocked you on Windows Live Messenger Myth Reality Explanations Myth aMSN software allows us to know who blocked us on Windows Live Messenger. Reality FALSE Explanations When it was launched,...
Spreadsheets - Mathematical Functions ShowStandard Functions Function Description ABS() This function returns the absolute value of a number. It therefore returns the number, if it is positive, or the opposite (positive) if it is negative ODD(value) Rounds a number up to the...
Spreadsheet - Functions ShowThe Concept of a Function Spreadsheets generally have a large number of integrated functions for processing data. There are hundreds, categorised as follows: Arithmetic functions that offer basic tools to process numerical data Statistical...
Spreadsheets - Conditional expressions ShowWhat is a Conditional Structure? Conditional structures are instructions that allow to test if a condition is true or not. Conditional structures may be associated together. To successfully execute these tests using logical functions, spreadsheets...
Ask your question Reply

1

mubashir aziz, on Apr 28, 2009 7:17:29 am BST
  • +1

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 .......

   
Reply to mubashir aziz

2

earthworm, on Apr 28, 2009 7:24:16 pm BST

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

   
Reply to earthworm

3

mubashir aziz, on Apr 29, 2009 4:42:18 am BST

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.

   
Reply to mubashir aziz

4

earthworm, on Apr 29, 2009 7:57:55 pm BST

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.

   
Reply to earthworm

5

mubashir aziz, on Apr 30, 2009 8:57:55 am BST
  • +1

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")

   
Reply to mubashir aziz

6

earthworm, on Apr 30, 2009 2:49:15 pm BST

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

   
Reply to earthworm

7

earthworm, on Apr 30, 2009 6:18:28 pm BST

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

   
Reply to earthworm

8

mubashir aziz, on May 6, 2009 3:30:30 am BST

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 .....


Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

   
Reply to mubashir aziz

9

aimheart, on Sep 8, 2009 9:02:51 am BST

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??

   
Reply to aimheart

10

mubashir aziz, on Sep 8, 2009 9:34:11 am BST

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






Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

11

aimheart, on Sep 8, 2009 9:44:27 am BST

Perfect. Just the job, thanx.

   
Reply to aimheart

12

 mubashir aziz, on Sep 8, 2009 9:46:07 am BST

U welcome
Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz
Ask your question Reply
They need your help