Issues with a formula - Results no correct

spydey

Active Member
Joined
Sep 19, 2017
Messages
309
Office Version
  1. 2013
Platform
  1. Windows
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

ABCDEFGH
1TEST1TEST2Adjusted DateResultsTypeStartEndDays
202/28/2018 - PASS02/28/2018 - TRUE02/28/2018TRUERed01/28/1603/01/1633
304/02/2018 - PASS04/02/2018 - TRUE04/02/2018TRUEBlue03/02/1604/19/1649
407/24/2018 - PASS07/24/2018 - TRUE07/24/2018TRUEYellow06/16/1610/06/16113
509/30/2018 - PASS09/30/2018 - TRUE09/30/2018TRUEBlue09/10/1703/09/18181
609/30/2018 - PASS09/30/2018 - TRUE09/30/2018TRUERed03/13/1805/11/1860
709/30/2018 - PASS09/30/2018 - FALSE09/30/2018FALSERed07/26/1810/01/1868
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
 
Last edited:

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

LouisT

Board Regular
Joined
Apr 5, 2010
Messages
63
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

ABCDEFGH
1TEST1TEST2Adjusted DateResultsTypeStartEndDays
202/28/2018 - PASS02/28/2018 - TRUE02/28/2018TRUERed01/28/1603/01/1633
304/02/2018 - PASS04/02/2018 - TRUE04/02/2018TRUEBlue03/02/1604/19/1649
407/24/2018 - PASS07/24/2018 - TRUE07/24/2018TRUEYellow06/16/1610/06/16113
509/30/2018 - PASS09/30/2018 - TRUE09/30/2018TRUEBlue09/10/1703/09/18181
609/30/2018 - PASS09/30/2018 - TRUE09/30/2018TRUERed03/13/1805/11/1860
709/30/2018 - PASS09/30/2018 - FALSE09/30/2018FALSERed07/26/1810/01/1868
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

Active Member
Joined
Sep 19, 2017
Messages
309
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Apr 5, 2010
Messages
63
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

Active Member
Joined
Sep 19, 2017
Messages
309
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Sep 19, 2017
Messages
309
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top