Unable to use Nested IF formula

[mark888]

Hi Guys,

I have a pretty tough problem which I need some help on.
Been trying to get this to work but tried using normal IF formula but can't get it to work.


I have an Excel file as attached which I would need to have a formula as below if possible:

1) If C1=4 then if value in A1>8 hours then B1 should show value as N else if A1<8 hours then B1 should show as Y

2) If C1=3 then if value in A1>4 hours then B1 should show value as N else if A1<4 hours then B1 should show as Y

3) If C1=2 then if value in A1>2 hours then B1 should show value as N else if A1<2 hours then B1 should show as Y

4) If C1=1 then if value in A1>1 hours then B1 should show value as N else if A1<1 hours then B1 should show as Y


In short, I am trying to combine all the IF and Else statement to make it into 1 long formula.

The value of C1 will be depending on certain criteria as below:

4 = 8 hours
3 = 4 hours
2 = 2 hours
1 = 1 hour


Attach is the sample of the file which I need the data from. Hopefully someone here can help me out guys.

Book2.xlsx

Regards,
Mark

[tchock]

Hello Mark,

i'm not sure it's the best solution, but try this:

=IF(C1=4,IF(VALUE(MID(A1,3,2))>8,"N","Y"),IF(C1=3,IF(VALUE(MID(A1,3,2))>4,"N","Y"),IF(C1=2,IF(VALUE(MID(A1,3,2))>2,"N","Y"),IF(C1=1,IF(VALUE(MID(A1,3,2))>1,"N","Y")))))

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

You could simplify that somewhat if your column A cells contained a more 'normal' Time value.

[mark888]

Hello Mark,

i'm not sure it's the best solution, but try this:

=IF(C1=4,IF(VALUE(MID(A1,3,2))>8,"N","Y"),IF(C1=3,IF(VALUE(MID(A1,3,2))>4,"N","Y"),IF(C1=2,IF(VALUE(MID(A1,3,2))>2,"N","Y"),IF(C1=1,IF(VALUE(MID(A1,3,2))>1,"N","Y")))))

Hi tchock,

Tried your suggestion but when it does not work. When I changed the value to more than 4 hours in column A3, it still shows result as Y.

[mark888]

Hi,

One way

Please Login or Register  to view this content.

You could simplify that somewhat if your column A cells contained a more 'normal' Time value.

Hi Richard,


Tried your formula as well but it does not show the correct value. Maybe it's the time problem? I can't manipulate the time because it's pulled from a tool and I have about 2500 records to check.

[tchock]

Hi tchock,

Tried your suggestion but when it does not work. When I changed the value to more than 4 hours in column A3, it still shows result as Y.

Maybe your conditions should have been ">=" instead of ">"? For example:
If C1=4 then if value in A1>=8 hours then B1 should show value as N else if A1<8 hours then B1 should show as Y

In this case, you just have to add the "=" in your formula:
=IF(C1=4,IF(VALUE(MID(A1,3,2))>=8,"N","Y"),IF(C1=3,IF(VALUE(MID(A1,3,2))>=4,"N","Y"),IF(C1=2,IF(VALUE(MID(A1,3,2))>=2,"N","Y"),IF(C1=1,IF(VALUE(MID(A1,3,2))>=1,"N","Y")))))

[samba_ravi]

=IF(LEFT(A1,FIND(" ",A1)-1)+RIGHT(A1,8)>CHOOSE(C1,1/24,2/24,4/24,8/24),"N","Y")
Try this in B1 and copy towards down

[mark888]

Maybe your conditions should have been ">=" instead of ">"? For example:
If C1=4 then if value in A1>=8 hours then B1 should show value as N else if A1<8 hours then B1 should show as Y

In this case, you just have to add the "=" in your formula:
=IF(C1=4,IF(VALUE(MID(A1,3,2))>=8,"N","Y"),IF(C1=3,IF(VALUE(MID(A1,3,2))>=4,"N","Y"),IF(C1=2,IF(VALUE(MID(A1,3,2))>=2,"N","Y"),IF(C1=1,IF(VALUE(MID(A1,3,2))>=1,"N","Y")))))

Hi again tchock,

Yes you are right. That worked well but when I inserted days in cell A1 before the time as 2 14:17:43, it shows me N when it should be Y. Any ideas on how to fix this?

Kind Regards,
Mark.

[mark888]

=IF(LEFT(A1,FIND(" ",A1)-1)+RIGHT(A1,8)>CHOOSE(C1,1/24,2/24,4/24,8/24),"N","Y")
Try this in B1 and copy towards down

Hi Siva,

Tried it but it did not work as well for the days in front of the time.

Kind Regards,
Mark.

[samba_ravi]

=IF(--RIGHT(A1,8)>CHOOSE(C1,1/24,2/24,4/24,8/24),"N","Y")
Try this

[tchock]

Hi Mark,

I'm not sure I understand why you should have "Y" instead of "N"...it seems to me that, based on your conditions and unless you want to add an additional one, the correct result for 2 14:17:43 should still be "N".

[Richard Buttrey]

Hi Richard,


Tried your formula as well but it does not show the correct value. Maybe it's the time problem? I can't manipulate the time because it's pulled from a tool and I have about 2500 records to check.

Hi,

It returns the same values that you show in column B. See attached.

Can yo explain further what the problem is?

[mark888]

=IF(--RIGHT(A1,8)>CHOOSE(C1,1/24,2/24,4/24,8/24),"N","Y")
Try this

Hi Nflsales,

Tried your formula and it still works for the time formula but if days are involved it still shows as Y instead of N. Not sure if there is anything can be done to fix this?

Kind Regards,
Mark.

[mark888]

Hi,

It returns the same values that you show in column B. See attached.

Can yo explain further what the problem is?

Hi Richard,

Sorry my mistake I did not make it clear.
The problem is because the format is in dd hh:mm:ss so the formula for the time works well but if I insert days in front, it returns the value of Y when it should be N.

Kind Regards,
Mark.

[mark888]

Hi Mark,

I'm not sure I understand why you should have "Y" instead of "N"...it seems to me that, based on your conditions and unless you want to add an additional one, the correct result for 2 14:17:43 should still be "N".

Hi Tchock,

Sorry my mistake it was a typo on my side. When I inserted days in cell A1 before the time as 2 14:17:43, it shows me Y when it should be N.

Kind Regards,
Mark.

[Jonmo1]

Try

=IF(REPLACE(A1,1,FIND(" ",A1),"")+0>TIME(C1,0,0),"N","Y")

EFMark888.xlsx

[Jonmo1]

Wait, that just used straight hours based on Value in C of 1 2 3 and 4...
To do 1 2 4 and 8 hours based on value of C being 1 2 3 and 4 it would be

=IF(REPLACE(A1,1,FIND(" ",A1),"")+0>TIME(CHOOSE(C1,1,2,4,8),0,0),"N","Y")

[mark888]

Wait, that just used straight hours based on Value in C of 1 2 3 and 4...
To do 1 2 4 and 8 hours based on value of C being 1 2 3 and 4 it would be

=IF(REPLACE(A1,1,FIND(" ",A1),"")+0>TIME(CHOOSE(C1,1,2,4,8),0,0),"N","Y")

Hi Jonmo,

Tried the formula and it works for the time perfectly but when I changed the days from 00 to more than 1 day(01,10), it still shows as Y when it should be N. Is there any fix so that it calculates the days in front as well?

Kind Regards,
Mark.

[Jonmo1]

Try
=IF(REPLACE(A1,1,FIND(" ",A1),"")+LEFT(A1,FIND(" ",A1))>TIME(CHOOSE(C1,1,2,4,8),0,0),"N","Y")

[mark888]

Try
=IF(REPLACE(A1,1,FIND(" ",A1),"")+LEFT(A1,FIND(" ",A1))>TIME(CHOOSE(C1,1,2,4,8),0,0),"N","Y")

Hi Jonmo1,

Tested it and it works perfectly now. Thats just pure genius ..I don't think I could ever had solved that. Thank you very much to you and all of the rest who had tried to help out. You guys are the best!!!

Kind Regards,
Mark.

[Richard Buttrey]

Hi Richard,

Sorry my mistake I did not make it clear.
The problem is because the format is in dd hh:mm:ss so the formula for the time works well but if I insert days in front, it returns the value of Y when it should be N.

Kind Regards,
Mark.

Hi,

Can you clarify what you mean by 'insert days in front'. Does the current 0 mean zero days? Of course the formula as given will still work whilst the number of days is a single digit. If you have more than 9 days then as stated it will not work. But since the formula is a simple string slicing formula just increment the start point of the MID bits of the formula. The foolproof way to do it would be to find the position of the first space character and build that into the formula as the starting point for the MID functions.

[Jonmo1]

You're welcome.

[mark888]

Hi,

Can you clarify what you mean by 'insert days in front'. Does the current 0 mean zero days? Of course the formula as given will still work whilst the number of days is a single digit. If you have more than 9 days then as stated it will not work. But since the formula is a simple string slicing formula just increment the start point of the MID bits of the formula. The foolproof way to do it would be to find the position of the first space character and build that into the formula as the starting point for the MID functions.

Hi Richard,

Yes the current 0 in front is meant as zero days. Sorry for confusing you.
On the bright side, Jonmo1 has came up with the solution. Thanks for all your help Richard

Kind Regards,
Mark.

[samba_ravi]

Hi Siva,

Tried it but it did not work as well for the days in front of the time.

Kind Regards,
Mark.

=IF(REPLACE(A1,1,FIND(" ",A1),"")+LEFT(A1,FIND(" ",A1))>TIME(CHOOSE(C1,1,2,4,8),0,0),"N","Y")
and =IF(LEFT(A1,FIND(" ",A1)-1)+RIGHT(A1,8)>CHOOSE(C1,1/24,2/24,4/24,8/24),"N","Y") [Post 7]
booth are giving the same result
i did not find any difference in the end result of the above formula

[mark888]

=IF(REPLACE(A1,1,FIND(" ",A1),"")+LEFT(A1,FIND(" ",A1))>TIME(CHOOSE(C1,1,2,4,8),0,0),"N","Y")
and =IF(LEFT(A1,FIND(" ",A1)-1)+RIGHT(A1,8)>CHOOSE(C1,1/24,2/24,4/24,8/24),"N","Y") [Post 7]
booth are giving the same result
i did not find any difference in the end result of the above formula

Hi Siva,

Good morning and sorry for the late reply as it was almost close to midnight yesterday. Yes I have tried your formula and it is working exactly the same as Jonmo1's formula. I'm not sure how come when I tried it last night it wasnt working. I must have definitely copied the wrong formula from you. Apologies for that.

Kind Regards,
Mark.