[Solved] Nested AND function within IF function

[AndrewScottMusic]

Hello Fellow Excel Avoiders

I'm trying to construct a formula whereby the cell shows a result if two conditions are met.

In English, it's this:

IF Reference Cell result displays Y AND reference cell value is greater than 0,, then result is some simple calculation.
If one of those conditions is not met, then result shows some different calculation.

I'm experimenting with variations on this:

=IF(D5="y" and IF(G5>0));(F5-E5)/2;F5-E5)

No matter how many brackets or parentheses I add, I keep getting Error 508 or 509.

You can see the problem in the attached file at cell H5.

Test nested IF AND.ods

(17.27 KiB) Downloaded 51 times

This will probably be super simple for someone who uses this app everyday, but for me it's hyper-frustration!!

[robleyd]

Looking at the HELP - F1 - for AND() we can see:

Syntax

AND(LogicalValue1; LogicalValue2; ... LogicalValue30)
LogicalValue1; LogicalValue2; ... LogicalValue30 are 1 to 30 conditions to be checked. All conditions can be either TRUE or FALSE. If a range is entered as a parameter, the function uses the value from the range that is in the current column or row. The result is TRUE if the logical value in all cells within the cell range is TRUE.

Example
The logical values of entries 12<13; 14>12 and 7<6 are to be checked:
=AND(12<13;14>12;7<6) returns FALSE.
=AND(FALSE; TRUE) returns FALSE.

So you would want something like (not tested)

Code: Select all

=IF(AND(D5="y";G5>0);(F5-E5)/2;F5-E5)

[MrProgrammer]

IF Reference Cell result displays Y AND reference cell value is greater than 0,, then result is some simple calculation.
If one of those conditions is not met, then result shows some different calculation.

Hi, and welcome to the forum. Thank you for the explanation in English of your goal.

I'm trying to construct a formula whereby the cell shows a result if two conditions are met.

Read section 9. Using functions and cell ranges in Ten concepts that every Calc user should know where you will find an example.

No matter how many brackets or parentheses I add, I keep getting Error 508 or 509.

This is what often happens for people who try to guess at formula syntax.
[Tutorial] Calc formula terms
[Tutorial] Order of Operations in Calc

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.