Apache OpenOffice Community Forum

Posted: **Thu Aug 01, 2024 9:16 am**

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 56 times

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

Posted: **Thu Aug 01, 2024 9:31 am**

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)

Posted: **Thu Aug 01, 2024 4:37 pm**

AndrewScottMusic wrote: ↑Thu Aug 01, 2024 9:16 am 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.

AndrewScottMusic wrote: ↑Thu Aug 01, 2024 9:16 am 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.

AndrewScottMusic wrote: ↑Thu Aug 01, 2024 9:16 am 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.

Apache OpenOffice Community Forum

[Solved] Nested AND function within IF function

[Solved] Nested AND function within IF function

Re: Nested AND Function within an IF Function

Re: Nested AND Function within an IF Function