IF Statement

[Graeme]

Hello

Please see the attached.

Column G displays Y or N depending on the data in columns D, E and F, using this formula:

=IF(OR(D3="Active",E3>TODAY()-180,F3>500),"Y","N")

As it is, if Active is entered in column D (Or the column E or column F criteria is true) then the IF Statement puts a Y in column G. If Inactive is entered in column D then the IF Statement puts an N in column G.

I would like to add another option to column D, (I've been searching for a solution to no avail!) so that if Departed is entered into column D then N is put into column G.

How would I edit my formula to allow this please?

Regards

Graeme

[HansV]

For example:

=IF(D3="Departed","N",IF(OR(D3="Active",E3>TODAY()-180,F3>500),"Y","N"))

[snb]

Code: Select all

=((D3="Active")+(E3>TODAY()-180)+(F3>500))*(D3<>"Departed")

[ChrisGreaves]

How would I edit my formula to allow this please?

Hi Graeme, my brain is incapable of writing a spreadsheet formula from such conditions. I have to use pencil and paper.

20230925_093329.jpg

I have written out your three conditions at the top of the sheet; your fourth condition is enclosed in square brackets. (as in "optional for now")
Then I draw a large rectangle and divide it into partitions. Since all your conditions are binary choices, each side is divided into two parts. You can use the same idea for ternary conditions (typically <, =, >) and divide a side into three parts.
Your first listed condition divides the rectangle into a left-hand and right-hand side.
Your third condition divides the rectangle into a top and a bottom half.
Your second condition had more characters in it, so I reserved that for the left-hand side of the rectangle, dividing the top and bottom half into quarters.

With all this in place I carefully fill each of the eight portions with a result, which will produce groups of like results.

From that I can write the spreadsheet expression with a fair chance of getting it correct the first time.

There is, of course, always an exercise for the reader which here is to add the fourth condition by splitting the left-hand and right-hand portions into quarters.

Most times I find it easiest to complete the 8-portion rectangle first, test that (in the spreadsheet) and THEN add the extra condition and test THAT new configuration.

But you probably know this technique anyway.
It has a name.
It is not a State Transition Table.
It is not a Venn Diagram.
But I have forgotten what it is called. I was taught this a long time ago.

Cheers, Chris

[Graeme]

For example:

=IF(D3="Departed","N",IF(OR(D3="Active",E3>TODAY()-180,F3>500),"Y","N"))

Thanks Hans

So another IF, separated from the IF(OR) statements.

Perfect.

Code: Select all

=((D3="Active")+(E3>TODAY()-180)+(F3>500))*(D3<>"Departed")

Thanks snb

Your code returns a 1 for Member 5. I was looking for either a Y or an N.

I'm sorted now though cheers.

But you probably know this technique anyway.

Chris, thanks for that.

I'm actually not aware of this technique.

And as you know, I'm not one to complain, but it didn't make any sense to me! Maybe if I draw it out on a sheet of A4? Wait here a sec, I'll be back.

Graeme

[ChrisGreaves]

Maybe if I draw it out on a sheet of A4? Wait here a sec, I'll be back.

Graeme, I will further suggest that you take the easy way out:-
(1) Locate a simple two-condition =IF formula in your spreadsheet, it will probably be a nested IF formula
=IF (CONDITION1, =IF (CONDITION2,ACTION,ACTION),ACTION)
That sort of structure. Draw a diagram for that one statement. That should make you comfortable with drawing a logic diagram for a two-condition IF formula.

(2) Repeat but for a three-condition formula, a nested nested structure. But again, from within your own spreadsheet, a formula and conditions with which you are familiar.

(3) Then get daring: Search Eileen's Lounge for a question about nested Ifs, and without looking at the suggested formula, write down the conditions and then draw th diagram. There is a chance that your IF statement generated from your diagram will not match the suggested solution, but that will usually be because we each have our own brain.
Mostly!

Cheers, Chris

[snb]

@Gra

The 'Y' or 'N' is functionally equal to 1 or 0 (binary/boolean)
the amount of information is identical.
It is always possible to overcomplicate things:

Code: Select all

=if(((D3="Active")+(E3>TODAY()-180)+(F3>500))*(D3<>"Departed");"Y","N")