IF, AND, DATE Syntax issue

[Matthew55]

Hello,

I started with a formula formula which looks only at a static date field $D$5; however the need has arisen such that a different date $AG49 should be used if it is greater than (>) $D$5. I added the syntax "IF($AG49>$D$5,$AG49,$D$5)" to the formula and receive the "Not trying to type a formula?" error. I'm not certain where the error lies - do I not have the appropriate number of parentheses, are they in the wrong place or are my arguments in an unacceptable order.

Help please...

[63falcondude]

This is not an answer to your question but note that IF($AG49>$D$5,$AG49,$D$5) can be written as MAX($AG49,$D$5).

Back to your question... It is difficult to diagnose a non-working formula without seeing a sample of the data that it is being used on.

If you create and share a small representative sample of your data along with the desired result of the formula (manually entered), you will be more likely to get a result that you are happy with.

To attach an Excel workbook, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Jonmo1]

Also, you're way over complicating your formula.
What is the purpose of this
DATE(YEAR(H49),MONTH(H49),DAY(H49))
Why deconstruct each element of the date in H49 with Year Month and Day functions, only to then reconstruct the same date with the DATE function ?
Why not just leave the reference as H49

And seem to do the same thing when comparing AG49 to D5

[Jonmo1]

I might also suggest posting a copy of the formula 'Before' you attempted any changes.

[Jonmo1]

I come up with this by just changing all those date manipulations back to just their cell references
And using the MAX function FalconDude suggested

=IF(AH49=0,0,IF(AND($D$6-H49<$AJ$10,I49="No"),ROUND((H49-MAX($D$5,$AG49)-1)/$AJ$10,2),ROUND(($D$6-MAX($D$5,$AG49)-1)/$AJ$10,2)))

That can be further simplified with some logical thinking..

Instead of

=IF(A=1,Y-X,Z-X)
Do
=IF(A=1,Y,Z)-X

This is a very subtle but significant difference.
The 2nd way, I only have to reference X once.

Applying that logic to the above formula I come up with this
=IF(AH49=0,0,ROUND((IF(AND($D$6-H49<$AJ$10,I49="No"),H49,$D$6)-MAX($D$5,$AG49)-1)/$AJ$10,2))

[Matthew55]

I attempted to attach the file. Please let me know if it did not come through.

[JohnTopley]

No file.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Matthew55]

I believe it is attached this time?

The cell(s) in question are in column AJ. In the "Before" in yellow on the attached the value is 1.00. The correct value is 0.89 and is in green.

[Jonmo1]

That formula is different from the one you posted.
Very different cell references, and even sheet references (which seem unnecessary because it's a reference to the same sheet containing the formula)
It's difficult to tell which part you want to apply this logic to IF($AG49>$D$5,$AG49,$D$5)

And the other formula with Vlookup ? Where did that come from?

[Matthew55]

My apologies. I had cleaned up the sheet references, but I mistakenly went back to a version I had not edited to create the redacted version. The cell containing the Vlookup is dependent upon column AJ which is where the difficulty lies, so if column AJ is correct, column AK follows.

As for all of the funky references and complications - I inherited the workbook and the formulas from my predecessor. He is more skilled than I when it comes to Excel, so I generally take what's there and try to "fix" it or make it work when we have changes. That being said, I obviously am not the most skilled either.

In the end, your post prior to the last works like a charm, and I thank you! And yes, much simpler and cleaner. I can easily apply this logic to other situations in the future.

Matt

[Jonmo1]

You're welcome.