Complicated IF statement driving me crazy

[Clelv]

Hi All

A

B

C

D

E

F

G

H

I

1

SU ID

Transfer Location

Start Date

Start Details

Date of 1st Transfer

Transfer Details

Date of 2nd Transfer

Transfer Details

End Date

2

09/01/2017

RR

05/04/2017

DC

»»»

»»»

29/04/2017

24

24

3

25/01/2017

RR

21/04/2017

DC

06/05/2017

RR

20/05/2017

15

15

4

04/01/2017

RR

10/04/2017

DC

»»»

»»»

06/05/2017

26

26

5

26/01/2017

RR

25/04/2017

DC

»»»

»»»

20/05/2017

25

25

6

18/01/2017

RR

18/04/2017

DC

»»»

»»»

13/05/2017

25

25

7

05/01/2017

RR

08/04/2017

trev

»»»

»»»

22/04/2017

14

14

8

21/03/2017

RR

30/05/2017

DC

»»»

»»»

10/06/2017

11

11

9

01/03/2017

DC

30/05/2017

RR

»»»

»»»

24/06/2017

90

0

10

02/03/2017

RR

30/05/2017

DC

»»»

»»»

09/06/2017

10

10

11

16/03/2017

DC

10/06/2017

RR

15/06/2017

DC

17/06/2017

88

88

12

30/03/2017

RR

26/06/2017

DC

»»»

»»»

Current

0

0

13

28/11/2016

RR

06/03/2017

DC

»»»

»»»

01/04/2017

26

26

14

02/05/2017

DC

»»»

»»»

»»»

»»»

01/06/2017

30

0

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

This is a table of data i've been working on. the second to end column adds up how many days the Service user required DC (DAY CARE). I somehow got there with this formula:

=IFERROR(IF(AND($I3="DC",$E3="DC"),$J3-$H3+$F3-$D3,
IF(AND($G3="DC",$I3="RR"),$H3-$F3,
IF(AND($E3="DC",$G3="RR"),$F3-$D3,
IF(AND($E3="DC",$G3="»»»"),$J3-$D3,$J3-$F3)))),0)


In the end column i'm attempting to do the same but have written the formula using <> to enable me to type text other than "DC" but get the same results as in cell F7 where i've used "trev" instead of "DC" and have been given the same outcome.

For some reason though this formula misses row 9. where i want to count the days between E9 and C9 and i just cannot get to the bottom of it and it's driving me nuts!! this is the formula i'm using. I would be really grateful if someone can spot the flaw/flaws. Thanks

=IFERROR(IF(AND($I3<>"RR",$E3<>"RR"),$J3-$H3+$F3-$D3,
IF(AND($G3<>"RR",$I3="RR"),$H3-$F3,
IF(AND($E3<>"RR",$G3="RR"),$F3-$D3,
IF(AND($E3<>"RR",$G3="»»»"),$J3-$D3,$J3-$F3)))),0)

 

[Joe4]

Your columns look off.

For example, you are checking column I for codes like "DC" and "RR", but in your data sample, column I is dates.

 

[mole999]

can you throw your table into something that will keep the column letters i.e IF I3= DC which is by comparison a date etcetera

your refs are shown as two apart yet are 5 when calculated

you may get an issue with $J3-$H3+$F3-$D3, Addition is carried out before subtraction so you could get $H3+$F3 -$J3 -$D3, if you need ($J3-$H3)+($F3-$D3) brackets then use them

IF statements evaluate the FIRST true and return that as the only result

 

[Clelv]

Yes you're right, sorry, I wished I could just upload the whole document! I don't think the bottom formula works for row 11 either. Thanks for having a look.

=IFERROR(IF(AND($H2="DC",$D2="DC"),$I2-$G2+$E2-$C2,
IF(AND($F2="DC",$H2="RR"),$G2-$E2,
IF(AND($D2="DC",$F2="RR"),$E2-$C2,
IF(AND($D2="DC",$F2="»»»"),$I2-$C2,$I2-$E2)))),0)

=IFERROR(IF(AND($H2<>"RR",$D2<>"RR"),$I2-$G2+$E2-$C2,IF(AND($F2<>"RR",$H2="RR"),$G2-$E2,IF(AND($D2<>"RR",$F2="RR"),$E2-$C2,IF(AND($D2<>"RR",$F2="»»»"),$I2-$C2,$I2-$E2)))),0)

 

[Clelv]

Hi mole999,

I will have to think about your response as it didn't sink in reading it initially! I will definitely play with the addition and see if that helps.

I'm just not sure I have the brain power for this one!!

Thanks for looking

 

[Joe4]

For some reason though this formula misses row 9. where i want to count the days between E9 and C9 and i just cannot get to the bottom of it and it's driving me nuts!! this is the formula i'm using. I would be really grateful if someone can spot the flaw/flaws.

So, the first formula for row 9 looks like this:

=IFERROR(IF(AND($H9="DC",$D9="DC"),$I9-$G9+$E9-$C9,IF(AND($F9="DC",$H9="RR"),$G9-$E9,IF(AND($D9="DC",$F9="RR"),$E9-$C9,IF(AND($D9="DC",$F9="»»»"),$I9-$C9,$I9-$E9)))),0)

while the second one looks like this:

=IFERROR([COLOR=#ff0000]IF(AND($H9<>"RR",$D9<>"RR"),$I9-$G9+$E9-$C9[/COLOR],IF(AND($F9<>"RR",$H9="RR"),$G9-$E9,IF(AND($D9<>"RR",$F9="RR"),$E9-$C9,IF(AND($D9<>"RR",$F9="»»»"),$I9-$C9,$I9-$E9)))),0)

I highlighted in red the condition that row 9 meets in each formula.

In your second one, it meets your first check. But note that G9 is "»»»". So when you try to include that in your calculation, you get an error. And you have told your errors to return 0 (via your IFERROR statement).

 

[Clelv]

Omg, that's a needle in a hay stack to me! I would never have spotted that.

Ok, well I'll have to have a think about how to work around that. Thanks for that spot I really appreciate that, I'll let you know how I get on.

Thanks

 

[Joe4]

The best thing to do in cases like this, is walk your actual example through your formula, and see what conditions are met. It isn't that hard to do.
On row 9:
D9="DC"
and
H9="»»»"

And then the first thing you are checking in your formula is:
IF(AND($H9<>"RR",$D9<>"RR")
So, is H9 something other than "RR" (yes) and is D9 something other than "DC" (yes).

So you would then apply the formula in the TRUE part of that IF Statement, which is:
$I9-$G9+$E9-$C9

Just substitute those values in there, and you get:
(24/06/2017)-("»»»")+(30/05/2017)-(01/03/2017)

Since you cannot subtract "»»»", that will result in an error. And your formula says if there is an error, return 0.

I find the best way to handle complicated questions/formulas like this is to first right down in plain English, how you want it to work (all the conditions and the order of which things should be checked). Once you have clearly defined that, then work on building your formula.

 

[Clelv]

That's really useful,

You explained it simply and so I could follow and I'll use your tips.

I tried being sneaky and adding in a (OR(H9<>"RR",H9<>"»»»")*AND($D9<>"RR"), but it didn't like it! So will probably have to rework the statement from scratch. So Close!! lol.

Thanks for your response.

 

[Joe4]

[COLOR=#ff0000][B]OR[/B][/COLOR][COLOR=#333333](H[/COLOR][COLOR=#333333][COLOR=#333333]9<>"RR",[/COLOR][/COLOR][COLOR=#ff0000][B]H[/B][/COLOR][COLOR=#333333][COLOR=#333333][/COLOR][/COLOR][COLOR=#ff0000][B]9<>"»»»"[/B][/COLOR][COLOR=#333333][COLOR=#333333])[/COLOR][/COLOR]

That will always return TRUE.
Think about it, what it is asking is:
Is H9 NOT equal to "RR"
- or -
is H9 NOT equal to "»»»"?

With an OR statement, only one of the conditions needs to be TRUE for the whole thing to be TRUE.
Since something cannot be both "RR" and "»»»" at the same time, that statement will ALWAYS return true.
So you want to use AND, not OR.

If I correctly understand what you are trying to do, I think you just want to add another condition to your original AND:

[B]IF(AND($H9<>"RR",[COLOR=#ff0000]$H9<>[/COLOR][/B][B][COLOR=#ff0000]"»»»"[/COLOR],[/B][B]$D9<>"RR")[/B]