Issues with a formula - Results no correct

spydey

Active Member
Joined
Sep 19, 2017
Messages
293
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:

LouisT

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

Forum statistics

Threads
1,081,864
Messages
5,361,746
Members
400,654
Latest member
Pinaki Chatterjee

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top