Hi,

I have a cell that is set to watch two other cells and flag a warning if
they do not match. I have it so the flag will come up if they do not match
but it doesn't clear when they do match. Can anyone help?


=IF(OR(OR(AND($B$26="Yes",LEFT($E$1,6)<"Anchor"), AND($B$26="Yes",LEFT($E$1,5)<"Major")),AND($B$26= "No",LEFT($E$1,6)="Anchor"),AND($B$26="No",(LEFT($ E$1,5)="Major"))),"Major Indicators do not match","")
--
Thanks for your help.
Karen53

You the evqluate formula too to help isolate which part of the and is not
working. All items need to be true

Click cell with formula
Tools Menu - Formula Auditing - Evaluate Formula

"Karen53" wrote:

Hi,

I have a cell that is set to watch two other cells and flag a warning if
they do not match. I have it so the flag will come up if they do not match
but it doesn't clear when they do match. Can anyone help?


=IF(OR(OR(AND($B$26="Yes",LEFT($E$1,6)<"Anchor"), AND($B$26="Yes",LEFT($E$1,5)<"Major")),AND($B$26= "No",LEFT($E$1,6)="Anchor"),AND($B$26="No",(LEFT($ E$1,5)="Major"))),"Major Indicators do not match","")
--
Thanks for your help.
Karen53

Nested AND and OR statement with < can be problematic even when evaluating
the formula. I ususally try to avoid < with the nested statements.

Try something like this, it is a little longer but easier to trace.

=IF($B$26="Yes",IF(OR(LEFT($E$1,6)="Anchor",LEFT($ E$1,5)="Major"),"","Major
Indicators do not
match"),IF($B$26="No",IF(OR(LEFT($E$1,6)="Anchor", LEFT($E$1,5)="Major"),"Major Indicators do not match","")))
--
Paul Cordts


"Joel" wrote:

You the evqluate formula too to help isolate which part of the and is not
working. All items need to be true

Click cell with formula
Tools Menu - Formula Auditing - Evaluate Formula

"Karen53" wrote:

Hi,

I have a cell that is set to watch two other cells and flag a warning if
they do not match. I have it so the flag will come up if they do not match
but it doesn't clear when they do match. Can anyone help?


=IF(OR(OR(AND($B$26="Yes",LEFT($E$1,6)<"Anchor"), AND($B$26="Yes",LEFT($E$1,5)<"Major")),AND($B$26= "No",LEFT($E$1,6)="Anchor"),AND($B$26="No",(LEFT($ E$1,5)="Major"))),"Major Indicators do not match","")
--
Thanks for your help.
Karen53

Paul: I think the AND OR solution is better than nested IF statements. <
with AND and OR are perfecrtly acceptable and the much better approach. The
AND OR approach makes it easier to draw truth tables and use DeMorgan's Law.

"Paul Cordts" wrote:

Nested AND and OR statement with < can be problematic even when evaluating
the formula. I ususally try to avoid < with the nested statements.

Try something like this, it is a little longer but easier to trace.

=IF($B$26="Yes",IF(OR(LEFT($E$1,6)="Anchor",LEFT($ E$1,5)="Major"),"","Major
Indicators do not
match"),IF($B$26="No",IF(OR(LEFT($E$1,6)="Anchor", LEFT($E$1,5)="Major"),"Major Indicators do not match","")))
--
Paul Cordts


"Joel" wrote:

You the evqluate formula too to help isolate which part of the and is not
working. All items need to be true

Click cell with formula
Tools Menu - Formula Auditing - Evaluate Formula

"Karen53" wrote:

Hi,

I have a cell that is set to watch two other cells and flag a warning if
they do not match. I have it so the flag will come up if they do not match
but it doesn't clear when they do match. Can anyone help?


=IF(OR(OR(AND($B$26="Yes",LEFT($E$1,6)<"Anchor"), AND($B$26="Yes",LEFT($E$1,5)<"Major")),AND($B$26= "No",LEFT($E$1,6)="Anchor"),AND($B$26="No",(LEFT($ E$1,5)="Major"))),"Major Indicators do not match","")
--
Thanks for your help.
Karen53

Thank you both for your help.
--
Thanks for your help.
Karen53


"Joel" wrote:

Paul: I think the AND OR solution is better than nested IF statements. <
with AND and OR are perfecrtly acceptable and the much better approach. The
AND OR approach makes it easier to draw truth tables and use DeMorgan's Law.

"Paul Cordts" wrote:

Nested AND and OR statement with < can be problematic even when evaluating
the formula. I ususally try to avoid < with the nested statements.

Try something like this, it is a little longer but easier to trace.

=IF($B$26="Yes",IF(OR(LEFT($E$1,6)="Anchor",LEFT($ E$1,5)="Major"),"","Major
Indicators do not
match"),IF($B$26="No",IF(OR(LEFT($E$1,6)="Anchor", LEFT($E$1,5)="Major"),"Major Indicators do not match","")))
--
Paul Cordts


"Joel" wrote:

You the evqluate formula too to help isolate which part of the and is not
working. All items need to be true

Click cell with formula
Tools Menu - Formula Auditing - Evaluate Formula

"Karen53" wrote:

Hi,

I have a cell that is set to watch two other cells and flag a warning if
they do not match. I have it so the flag will come up if they do not match
but it doesn't clear when they do match. Can anyone help?


=IF(OR(OR(AND($B$26="Yes",LEFT($E$1,6)<"Anchor"), AND($B$26="Yes",LEFT($E$1,5)<"Major")),AND($B$26= "No",LEFT($E$1,6)="Anchor"),AND($B$26="No",(LEFT($ E$1,5)="Major"))),"Major Indicators do not match","")
--
Thanks for your help.
Karen53

I have the following semi-complex if statement:

IF(OR(7/31/2009X2,7/31/2010<Y2),IF(7/31/2009-X2<=30,((7/31/2009-X2)/31)*G2,IF(7/31/2009-Y2<=30,((7/31/2009-Y2)/31*G2),G2,0).

Basically a project has a start date and end date.
If the project encompasses the entire month of July 2009, the project is expensed the entire amount for that whole month. If the project starts or ends w/in July 2009, the number of days divided by 31 times the monthly cost is allocated for that project for that month. Otherwise, if the project does not start or end in July 2009, there is zero costs of the project. Is there anyone who can assist me in writing this function correctly?

Thank you.

On Tuesday, October 09, 2007 11:45 AM Karen5 wrote:

Hi,

I have a cell that is set to watch two other cells and flag a warning if
they do not match. I have it so the flag will come up if they do not match
but it doesn't clear when they do match. Can anyone help?


=IF(OR(OR(AND($B$26="Yes",LEFT($E$1,6)<"Anchor"), AND($B$26="Yes",LEFT($E$1,5)<"Major")),AND($B$26= "No",LEFT($E$1,6)="Anchor"),AND($B$26="No",(LEFT($ E$1,5)="Major"))),"Major Indicators do not match","")
--
Thanks for your help.
Karen53


On Tuesday, October 09, 2007 12:07 PM Joe wrote:

You the evqluate formula too to help isolate which part of the and is not
working. All items need to be true

Click cell with formula
Tools Menu - Formula Auditing - Evaluate Formula

"Karen53" wrote:


On Tuesday, October 09, 2007 12:24 PM PaulCordt wrote:

Nested AND and OR statement with < can be problematic even when evaluating
the formula. I ususally try to avoid < with the nested statements.

Try something like this, it is a little longer but easier to trace.

=IF($B$26="Yes",IF(OR(LEFT($E$1,6)="Anchor",LEFT($ E$1,5)="Major"),"","Major
Indicators do not
match"),IF($B$26="No",IF(OR(LEFT($E$1,6)="Anchor", LEFT($E$1,5)="Major"),"Major Indicators do not match","")))
--
Paul Cordts


"Joel" wrote:


On Tuesday, October 09, 2007 12:35 PM Joe wrote:

Paul: I think the AND OR solution is better than nested IF statements. <
with AND and OR are perfecrtly acceptable and the much better approach. The
AND OR approach makes it easier to draw truth tables and use DeMorgan's Law.

"Paul Cordts" wrote:


On Tuesday, October 09, 2007 1:50 PM Karen5 wrote:

Thank you both for your help.
--
Thanks for your help.
Karen53


"Joel" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
JustCode Visual Studio Development Add-In by Telerik
http://www.eggheadcafe.com/tutorials...y-telerik.aspx

I have the following semi-complex if statement:

IF(OR(7/31/2009X2,7/31/2010<Y2),IF(7/31/2009-X2<=30,((7/31/2009-X2)/31)*G2,IF(7/31/2009-Y2<=30,((7/31/2009-Y2)/31*G2),G2,0).

Basically a project has a start date and end date.
If the project encompasses the entire month of July 2009, the project is expensed the entire amount for that whole month. If the project starts or ends w/in July 2009, the number of days divided by 31 times the monthly cost is allocated for that project for that month. Otherwise, if the project does not start or end in July 2009, there is zero costs of the project. Is there anyone who can assist me in writing this function correctly?

Thank you.

On Tuesday, October 09, 2007 11:45 AM Karen5 wrote:

Hi,

I have a cell that is set to watch two other cells and flag a warning if
they do not match. I have it so the flag will come up if they do not match
but it doesn't clear when they do match. Can anyone help?


=IF(OR(OR(AND($B$26="Yes",LEFT($E$1,6)<"Anchor"), AND($B$26="Yes",LEFT($E$1,5)<"Major")),AND($B$26= "No",LEFT($E$1,6)="Anchor"),AND($B$26="No",(LEFT($ E$1,5)="Major"))),"Major Indicators do not match","")
--
Thanks for your help.
Karen53


On Tuesday, October 09, 2007 12:07 PM Joe wrote:

You the evqluate formula too to help isolate which part of the and is not
working. All items need to be true

Click cell with formula
Tools Menu - Formula Auditing - Evaluate Formula

"Karen53" wrote:


On Tuesday, October 09, 2007 12:24 PM PaulCordt wrote:

Nested AND and OR statement with < can be problematic even when evaluating
the formula. I ususally try to avoid < with the nested statements.

Try something like this, it is a little longer but easier to trace.

=IF($B$26="Yes",IF(OR(LEFT($E$1,6)="Anchor",LEFT($ E$1,5)="Major"),"","Major
Indicators do not
match"),IF($B$26="No",IF(OR(LEFT($E$1,6)="Anchor", LEFT($E$1,5)="Major"),"Major Indicators do not match","")))
--
Paul Cordts


"Joel" wrote:


On Tuesday, October 09, 2007 12:35 PM Joe wrote:

Paul: I think the AND OR solution is better than nested IF statements. <
with AND and OR are perfecrtly acceptable and the much better approach. The
AND OR approach makes it easier to draw truth tables and use DeMorgan's Law.

"Paul Cordts" wrote:


On Tuesday, October 09, 2007 1:50 PM Karen5 wrote:

Thank you both for your help.
--
Thanks for your help.
Karen53


"Joel" wrote:


On Friday, October 29, 2010 8:35 PM Jumma Khan wrote:

I have the following semi-complex if statement:



IF(OR(7/31/2009X2,7/31/2010<Y2),IF(7/31/2009-X2<=30,((7/31/2009-X2)/31)*G2,IF(7/31/2009-Y2<=30,((7/31/2009-Y2)/31*G2),G2,0).



Basically a project has a start date and end date.

If the project encompasses the entire month of July 2009, the project is expensed the entire amount for that whole month. If the project starts or ends w/in July 2009, the number of days divided by 31 times the monthly cost is allocated for that project for that month. Otherwise, if the project does not start or end in July 2009, there is zero costs of the project. Is there anyone who can assist me in writing this function correctly?



Thank you.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Styling the WPF ScrollViewer
http://www.eggheadcafe.com/tutorials...ollviewer.aspx

On Oct 29, 5:35*pm, Jumma Khan wrote:
I have the following semi-complex if statement:

IF(OR(7/31/2009X2,7/31/2010<Y2),
IF(7/31/2009-X2<=30,((7/31/2009-X2)/31)*G2,
IF(7/31/2009-Y2<=30,((7/31/2009-Y2)/31*G2),G2,0)

First, you cannot write a date as 7/31/2009 in an expression. That is
interpreted as the numeric expression 7 divided by 31 divided 2009.

Generally, it is better to use the DATE function, e.g.
DATE(2009,7,31). Thus, your date will be interpreted correctly
regardless of the Regional and Language setting, m/d/yyyy or d/m/yyyy
et al.

You can get away with writing "7/31/2009" if you use the string in an
arithmetic expression, e.g. --"7/31/2009"X2 or "7/31/2009"-X2. But
the first form is error-prone; if you inadvertently omit the double
negation (--), you are comparing a string and a number. That does not
generate an error, but it does not do the comparison you expected.


Basically a project has a start date and end date.

I assume that X2 is the start date, Y2 is the end date, and G2 is
monthly project cost.


If the project encompasses the entire month of July 2009,
the project is expensed the entire amount for that whole month.
*If the project starts or ends w/in July 2009, the number of days
divided by 31 times the monthly cost is allocated for that project
for that month. *Otherwise, if the project does not start or end in
July 2009, there is zero costs of the project.

=G2*MAX(0,1+MIN(Y2,DATE(2009,7,31))-MAX(X2,DATE(2009,7,1)))/31

However, I doubt that you really want a formula that works only for
July 2009. More generally, put the first and last dates of the month
in helper cells, e.g. Z2 and AA2. Then you would write:

=G2*MAX(0,1+MIN(Y2,AA2)-MAX(X2,Z2))/(AA2-Z2+1)

The last date in AA2 might be the formula =EOMONTH(Z2,0).
Alternatively, if you cannot use EOMONTH, then use =DATE(YEAR(Z2),
1+MONTH(Z2),0).


On Tuesday, October 09, 2007 11:45 AM Karen5 wrote:

For future note, it is a bad idea to post a new question as a response
to an old question, especially a discussion that is 2 years old. Many
people will not see your new posting. Start a new discussion.

Also, for broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/...ry/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

PS.... On Oct 30, 1:30*am, joeu2004 wrote:
=G2*MAX(0,1+MIN(Y2,DATE(2009,7,31))-MAX(X2,DATE(2009,7,1)))/31
[...or...]
=G2*MAX(0,1+MIN(Y2,AA2)-MAX(X2,Z2))/(AA2-Z2+1)

If you would like those to work even when X2 or Y2 is empty or appears
blank, change X2 to N(X2) and Y2 to N(Y2). For example:

=G2*MAX(0,1+MIN(N(Y2),DATE(2009,7,31))-MAX(N(X2),DATE(2009,7,1)))/31