Issues with a formula - Results no correct

[spydey]

So I have a sheet where I have some dates and some results, I am trying to combine a few different formulas into a single one so that I don't have to look at several columns to get my results.

When I have the formulas separated, it works fine, but when I try to combine them, I don't get the expected results.



Initial Date: 02/28/2018

	A	B	C	D	E	F	G	H
1	TEST1	TEST2	Adjusted Date	Results	Type	Start	End	Days
2	02/28/2018 - PASS	02/28/2018 - TRUE	02/28/2018	TRUE	Red	01/28/16	03/01/16	33
3	04/02/2018 - PASS	04/02/2018 - TRUE	04/02/2018	TRUE	Blue	03/02/16	04/19/16	49
4	07/24/2018 - PASS	07/24/2018 - TRUE	07/24/2018	TRUE	Yellow	06/16/16	10/06/16	113
5	09/30/2018 - PASS	09/30/2018 - TRUE	09/30/2018	TRUE	Blue	09/10/17	03/09/18	181
6	09/30/2018 - PASS	09/30/2018 - TRUE	09/30/2018	TRUE	Red	03/13/18	05/11/18	60
7	09/30/2018 - PASS	09/30/2018 - FALSE	09/30/2018	FALSE	Red	07/26/18	10/01/18	68
8
9

<tbody>
</tbody>

Ok, I will try and explain it best I can.

The Adjusted Date:

• C2 is always the Initial date
• C3:C is calculated as follows:
  • =If(E3="Blue",$C2+$H3,$C2)

Basically, if the Type is "Blue", take the # of days from the same row and add it to the adjusted date from the row above.

The Results:

• D2 is always TRUE
• D3:D is calculated as follows:
  • =C3>=G3

Basically, if the adjusted date from the same row is greater than or equal to the end date of the same row, then true, else false.


TEST2:

• =Text(C2,"mm/dd/yy")&" - "&D2

Just a simple concatenation of the Adjusted Date and Results, giving me the Date and the results.

TEST1:
What I would like for Test1 to do is basically do columns C, D, and B into one single formula.

Except instead of TRUE or FALSE, I want it to say PASS or FAIL.
However, because the TEST1 outputs are comprised of a date and text combined, I have to use some Text(Left(Find to isolate just the date do do my additions and comparisons.
Also, because I want the True/False to be Pass/Fail, I have to use an IF statement to achieve that (unless there is a different way).

So basically this is what I have for TEST 1:

• A2 is as follows:
  • =TEXT(InitialDate,"mm/dd/yyyy")&" - "&If(InitialDate>=EndDate,"Pass","Fail")
    • Works as expected
• A3:A are as follows:
  • =If(E3="Blue",Text(Left(A2,Find(" ",A2,1)-1))+H3,"mm/dd/yyyy")&" - "&If(Text(Left(A2,Find(" ",A2,1)-1))+H3,"mm/dd/yyyy")>=G3,"Pass","Fail"),Text(Left(A2,Find(" ",A2,1)-1)),"mm/dd/yyyy")&" - "&If(Text(Left(A2,Find(" ",A2,1)-1)),"mm/dd/yyyy")>=G3,"Pass","Fail"))
  • Also, there is an additional part in the above formula where if E3 doesn't equal "Blue, then I have to figure out if the above date is greater or equal to the End date of the same row and place a corresponding pass/fail.

I get the correct dates, but it says PASS for every single one, when really, it should say FAIL on the last one, if not more.

My data set is quite a bit larger than the example above.

I have gone over it probably a good 20 times, reworked it multiple times, broken it out to its simplest forms, and tried piecing it all together.

The smaller pieces work, but putting it together just isn't for some reason.

I don't want to use helper or hidden columns if I can avoid it.

Any thoughts, pointers, ideas?

I bet it is something super simple like forgetting a close parenthesis or a missing quote, or something .... hahahahahahaha

Thanks in advance for your help!

-Spydey

 

[LouisT]

I'm trying to follow along and right now i'm wondering why if all you're really trying to do is to more or less Concatenate 3 cells/formulas AND change True to Pass and False to Fail, why aren't you just referencing column E to change False to Fail?

So I have a sheet where I have some dates and some results, I am trying to combine a few different formulas into a single one so that I don't have to look at several columns to get my results.

When I have the formulas separated, it works fine, but when I try to combine them, I don't get the expected results.



Initial Date: 02/28/2018

	A	B	C	D	E	F	G	H
1	TEST1	TEST2	Adjusted Date	Results	Type	Start	End	Days
2	02/28/2018 - PASS	02/28/2018 - TRUE	02/28/2018	TRUE	Red	01/28/16	03/01/16	33
3	04/02/2018 - PASS	04/02/2018 - TRUE	04/02/2018	TRUE	Blue	03/02/16	04/19/16	49
4	07/24/2018 - PASS	07/24/2018 - TRUE	07/24/2018	TRUE	Yellow	06/16/16	10/06/16	113
5	09/30/2018 - PASS	09/30/2018 - TRUE	09/30/2018	TRUE	Blue	09/10/17	03/09/18	181
6	09/30/2018 - PASS	09/30/2018 - TRUE	09/30/2018	TRUE	Red	03/13/18	05/11/18	60
7	09/30/2018 - PASS	09/30/2018 - FALSE	09/30/2018	FALSE	Red	07/26/18	10/01/18	68
8
9

<tbody>
</tbody>

Ok, I will try and explain it best I can.

The Adjusted Date:

• C2 is always the Initial date
• C3:C is calculated as follows:
  • =If(E3="Blue",$C2+$H3,$C2)

Basically, if the Type is "Blue", take the # of days from the same row and add it to the adjusted date from the row above.

The Results:

• D2 is always TRUE
• D3:D is calculated as follows:
  • =C3>=G3

Basically, if the adjusted date from the same row is greater than or equal to the end date of the same row, then true, else false.


TEST2:

• =Text(C2,"mm/dd/yy")&" - "&D2

Just a simple concatenation of the Adjusted Date and Results, giving me the Date and the results.

TEST1:
What I would like for Test1 to do is basically do columns C, D, and B into one single formula.

Except instead of TRUE or FALSE, I want it to say PASS or FAIL.
However, because the TEST1 outputs are comprised of a date and text combined, I have to use some Text(Left(Find to isolate just the date do do my additions and comparisons.
Also, because I want the True/False to be Pass/Fail, I have to use an IF statement to achieve that (unless there is a different way).

So basically this is what I have for TEST 1:

• A2 is as follows:
  • =TEXT(InitialDate,"mm/dd/yyyy")&" - "&If(InitialDate>=EndDate,"Pass","Fail")
    • Works as expected
• A3:A are as follows:
  • =If(E3="Blue",Text(Left(A2,Find(" ",A2,1)-1))+H3,"mm/dd/yyyy")&" - "&If(Text(Left(A2,Find(" ",A2,1)-1))+H3,"mm/dd/yyyy")>=G3,"Pass","Fail"),Text(Left(A2,Find(" ",A2,1)-1)),"mm/dd/yyyy")&" - "&If(Text(Left(A2,Find(" ",A2,1)-1)),"mm/dd/yyyy")>=G3,"Pass","Fail"))
  • Also, there is an additional part in the above formula where if E3 doesn't equal "Blue, then I have to figure out if the above date is greater or equal to the End date of the same row and place a corresponding pass/fail.

I get the correct dates, but it says PASS for every single one, when really, it should say FAIL on the last one, if not more.

My data set is quite a bit larger than the example above.

I have gone over it probably a good 20 times, reworked it multiple times, broken it out to its simplest forms, and tried piecing it all together.

The smaller pieces work, but putting it together just isn't for some reason.

I don't want to use helper or hidden columns if I can avoid it.

Any thoughts, pointers, ideas?

I bet it is something super simple like forgetting a close parenthesis or a missing quote, or something .... hahahahahahaha

Thanks in advance for your help!

-Spydey

 

[spydey]

I'm trying to follow along and right now i'm wondering why if all you're really trying to do is to more or less Concatenate 3 cells/formulas AND change True to Pass and False to Fail, why aren't you just referencing column E to change False to Fail?

Great question!

The Adjusted Date and Results columns are just dummy columns to figure out why the actual formula of TEST2 is not working.

In my actual data set I don't have a Results column.

So I want to do the same thing as the concatenate of Adjusted Date and Results, but without the Results column acutally being present.

I am trying to determine within the formula what the results would be, without the need of the results column.

I hope that clarifies things.

-Spydeyu

 

[LouisT]

ahhh ok. So then you should be able to just nest the "results" formula where you need it by saying "If(C3>=G3,"Pass","Fail")" right?

Great question!

The Adjusted Date and Results columns are just dummy columns to figure out why the actual formula of TEST2 is not working.

In my actual data set I don't have a Results column.

So I want to do the same thing as the concatenate of Adjusted Date and Results, but without the Results column acutally being present.

I am trying to determine within the formula what the results would be, without the need of the results column.

I hope that clarifies things.

-Spydeyu

 

[spydey]

ahhh ok. So then you should be able to just nest the "results" formula where you need it by saying "If(C3>=G3,"Pass","Fail")" right?

Yes, that is what I thought too.

I did happen to figure it out. The issue was that when using the Text(Left(Find, even though I was putting in a "mm/dd/yyyy" format, the comparison operator was not acting correctly. All I had to do was add a 0 (zero) and it worked!!! Pretty simple.

Thanks for your input!

 

[spydey]

ahhh ok. So then you should be able to just nest the "results" formula where you need it by saying "If(C3>=G3,"Pass","Fail")" right?

Louis, just in case you wanted to know the solution, here its:

• =If(E3="Blue",(Text(Left(A2,Find(" ",A2,1)-1))+H3,"mm/dd/yyyy"))&" - "&If((Text(Left(A2,Find(" ",A2,1)-1)),"mm/dd/yyyy")+83)>=G3,"Pass","Fail"),(Text(Left(A2,Find(" ",A2,1)-1)),"mm/dd/yyyy"))&" - "&If((Text(Left(A2,Find(" ",A2,1)-1)),"mm/dd/yyyy")+0)>=G3,"Pass","Fail"))

It works great now!

-Spydey