Issues with a formula - Results no correct

S

spydey

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

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

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:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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?


spydey said:
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

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

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
Click to expand...
 
Upvote 0
LouisT said:
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?
Click to expand...

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
 
Upvote 0
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?



spydey said:
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
Click to expand...
 
Upvote 0
LouisT said:
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?
Click to expand...

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!
 
Upvote 0
LouisT said:
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?
Click to expand...


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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
64
Bernie Galve
B
L
  • Solved
Help Please
2 3
Replies
21
Views
486
Asbestos_Jen
Asbestos_Jen
R
  • Question Question
Excel date format
Replies
7
Views
502
Cubist
Cubist
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
647
Peter_SSs
Peter_SSs
A
  • Solved
Date format changing ...
Replies
4
Views
196
adambc
A

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

Share this page

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
Back
Top