If statements

[GORDON]

I have this formula which works 99% of the time, only in the last
instance do I get it to fail when I add if T<>""),"".
............O...............Q..................R.............S...................T
1) ........X
2) ........X...................................................X
3) ........X................X
4) ........X................X...................X
5) .......X.................X...................X.............X
6)
.........X................X....................X..............X................X

What I'm trying to do is create a formula so that the user does not
have to remember to put an "E" in one spot or not. My other formulas
will then take a count of all "E's in a column and give me a count.
This is what I have so far.

=IF((O2816<>"")*(S2816<>""),"E",IF((Q2816<>"")*(R2816=""),"E",IF((Q2816="")*(R2816=""),"",IF((Q2816<>"")*(R2816<>""),"",IF((S2816<>"")*(T2816=""),"E")))))

1)So in the first instance if there is a value in O then leave blank.
2) if there is a value in O and S then put an "E"
3) if there is a value O and Q then put an "E"
4) if there is a value in O, Q, and R then leave blank
5) if there is a value in O, Q, R and S then put an "E"
6) if there is a value in O, Q, R, S and T then leave blank
I am still missing a piece of the puzzle in step 6 where I can't get it
to put a blank. Is there an easier formula or what am I missing? Thanks
to anyone who can help

 

[Ron Coderre]

I know this is heading in a slightly different direction, but . . . Se
if this works for you:

=VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),{10000,"BLANK";10010,"E";11000,"E";11100,"BLANK";11110,"E";11111,"BLANK"},2,0)

(I just display "BLANK" in the formula to demonstrate. You'll probabl
change those to just "" )

It basically works from a table that looks like this:

O__P_________Q______R______S_____T_____Result____CalcVal
X__Anything___Blank__Blank__Blank__Blank__BLANK____10000
X__Anything___Blank__Blank__X_____Blank___E_______10010
X__Anything___X_____Blank__Blank__Blank___E_______11000
X__Anything___X_____X_____Blank__Blank___BLANK___11100
X__Anything___X_____X_____X_____Blank___E _______11110
X__Anything___X_____X_____X_____X ______BLANK____11111



Does that help?

Ro

 

[Ron Rosenfeld]

1)So in the first instance if there is a value in O then leave blank.
2) if there is a value in O and S then put an "E"
3) if there is a value O and Q then put an "E"
4) if there is a value in O, Q, and R then leave blank
5) if there is a value in O, Q, R and S then put an "E"
6) if there is a value in O, Q, R, S and T then leave blank
I am still missing a piece of the puzzle in step 6 where I can't get it
to put a blank. Is there an easier formula or what am I missing? Thanks
to anyone who can help

Your IF statement will be executed sequentially. The first time a TRUE
condition is found, that result will occur and execution of the function will
cease. So you really need to be testing in reverse order of your steps 1-6.

Perhaps rewriting it somewhat differently will make this more clear:

=IF(COUNTA(O2816,Q2816:T2816)=5,"",
IF(COUNTA(O2816,Q2816:S2816)=4,"E",
IF(COUNTA(O2816,Q2816,R2816)=3,"",
IF(COUNTA(O2816,Q2816)=2,"E",
IF(COUNTA(O2816,S2816)=2,"E",
IF(O2816<>"",""))))))

I don't know if you need to expand this further, but note that you have already
nested 5 IF's. The nesting limit in Excel is 7 functions so, if you need to
expand this formula, you will need a different approach.


--ron

 

[GORDON]

Thanks I'll try both and see how they'll work. What would happen if I
put some ands and ors would that make it easier. Like if A has a value
and....then put an E otherwise if A had a value ....was blank then leave
blank.

 

[Ron Rosenfeld]

Thanks I'll try both and see how they'll work. What would happen if I
put some ands and ors would that make it easier. Like if A has a value
and....then put an E otherwise if A had a value ....was blank then leave
blank.

For me, the way of clarifying the equation lies in putting each segment on a
different line, as I did, instead of running it all in one line. Indentation
would help also.

You should do whatever makes the logic that you are using clear to yourself. I
see no need to use AND's or OR's in the example you gave. But you could:

=IF(COUNTA(O2816,Q2816:T2816)=5,"",
IF(COUNTA(O2816,Q2816:S2816)=4,"E",
IF(COUNTA(O2816,Q2816,R2816)=3,"",
IF(COUNTA(O2816,Q2816)=2,"E",
IF(COUNTA(O2816,S2816)=2,"E",
IF(O2816<>"",""))))))

vs

=IF(AND(O2816<>"",Q2816<>"",R2816<>"",S2816<>"",T2816<>""),"",
IF(AND(O2816<>"",Q2816<>"",R2816<>"",S2816<>""),"E",
IF(AND(O2816<>"",Q2816<>"",R2816<>""),"",
IF(AND(O2816<>"",Q2816<>""),"E",
IF(AND(O2816<>"",S2816<>""),"E",
IF(O2816<>"",""))))))


Just use whichever seems more clear to you.

You should note that in your description of what you wanted to do, you did not
define the result you want if there are no entries in any of the cells. The
above formula(s) will return FALSE in that event; you may wish a different
result.

=IF(COUNTA(O2816,Q2816:T2816)=5,"",
IF(COUNTA(O2816,Q2816:S2816)=4,"E",
IF(COUNTA(O2816,Q2816,R2816)=3,"",
IF(COUNTA(O2816,Q2816)=2,"E",
IF(COUNTA(O2816,S2816)=2,"E",
IF(O2816="","undefined",""))))))

or

=IF(AND(O2816<>"",Q2816<>"",R2816<>"",S2816<>"",T2816<>""),"",
IF(AND(O2816<>"",Q2816<>"",R2816<>"",S2816<>""),"E",
IF(AND(O2816<>"",Q2816<>"",R2816<>""),"",
IF(AND(O2816<>"",Q2816<>""),"E",
IF(AND(O2816<>"",S2816<>""),"E",
IF(O2816="","undefined",""))))))


--ron

 

[GORDON]

I tried the formula and it worked somewhat. I think I may have misled
you in the wrong direction. I wanted the computer to automatically
either put an "E" or leave blank. When I removed some values in
columns (Q,R) it didn't go back to blank as I thought it would (O,S,T
are filled). It would work if I sequentialy put values in the columns
from O to T .

=IF(AND(O2816<>"",Q2816<>"",R2816<>"",S2816<>"",T2816<>""),"",
IF(AND(O2816<>"",Q2816<>"",R2816<>"",S2816<>""),"E",
IF(AND(O2816<>"",Q2816<>"",R2816<>""),"",
IF(AND(O2816<>"",Q2816<>""),"E",
IF(AND(O2816<>"",S2816<>""),"E",
IF(O2816="","undefined",""))))))

This is the formula now as sugusted by you:
=IF(AND(O2815<>"",Q2815<>"",R2815<>"",S2815<>"",T2815<>""),"",IF(AND(O2815<>"",Q2815<>"",R2815<>"",S2815<>""),"E",IF(AND(O2815<>"",Q2815<>"",R2815<>""),"",IF(AND(O2815<>"",Q2815<>""),"E",IF(AND(O2815<>"",S2815<>""),"E",IF(O2815="","Nil",""))))))
Any more suggestion as why I can't get it to work properly. For the
last statement if O="", I wanted it to leave it blank, this would take
care of any blank rows. Thanks for your help.

 

[GORDON]

I know this is heading in a slightly different direction, but . . . See
if this works for you:

=VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),{10000,"BLANK";10010,"E";11000,"E";11100,"BLANK";11110,"E";11111,"BLANK"},2,0)

(I just display "BLANK" in the formula to demonstrate. You'll probably
change those to just "" )

It basically works from a table that looks like this:

O__P_________Q______R______S_____T_____Result____CalcVal
X__Anything___Blank__Blank__Blank__Blank__BLANK____10000
X__Anything___Blank__Blank__X_____Blank___E_______10010
X__Anything___X_____Blank__Blank__Blank___E_______11000
X__Anything___X_____X_____Blank__Blank___BLANK___11100
X__Anything___X_____X_____X_____Blank___E _______11110
X__Anything___X_____X_____X_____X ______BLANK____11111



Does that help?

Ron

Ron, changed the formula to reflect the "" instead of blank

=VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),{10000,"";10010,"E";11000,"E";11100,"";11110,"E";11111,""},2,0)

It basically works from a table that looks like this:

O__P_________Q______R______S_____T_____Result____C alcVal
X__Anything___Blank__Blank__Blank__Blank__BLANK___ _10000
X__Anything___Blank__Blank__X_____Blank___E_______ 10010
X__Anything___X_____Blank__Blank__Blank___E_______ 11000
X__Anything___X_____X_____Blank__Blank___BLANK___1 1100
X__Anything___X_____X_____X_____Blank___E _______11110
X__Anything___X_____X_____X_____X ______BLANK____11111

This worked except when O was blank it gave me a #N/A and when O
was<>”” and T<>”” it gave me a result of #N/A. I would prefer in both
cases to leave it blank if possible. Any more help.

 

[Ron Coderre]

See how close this formula gets to achieving what you want:

=IF(ISBLANK(O2816),"",VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),{10000,"";10010,"E";11000,"E";11100,"";11110,"E";11111,""},2,0))

Let me know how close I came this time.

Regards,
Ron

 

[Ron Coderre]

I had another thought: Am I right in assuming that you are onl
interested in flagging the "E" items and all others combinations shoul
be blank?

If yes, I think this formula will work for you:

=VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),{0,"";10010,"E";10011,"";11000,"E";11001,"";11110,"E";11111,""},2,1)

It essentially performs an approximate look up of the calculated value
in the equivalent of this table:

CalcVal ReturnVal
____0.......=""
10010.......E
10011.......=""
11000.......E
11001.......=""
11110.......E
11111.......=""

You could also just put that table elsewhere in your workbook and refe
to it in the formulas:
=VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),Sheet2!$A$1:$B$7,2,1)

Do either of those work for you?

Ro

 

[GORDON]

I had another thought: Am I right in assuming that you are only
interested in flagging the "E" items and all others combinations should
be blank?

If yes, I think this formula will work for you:

=VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),{0,"";10010,"E";10011,"";11000,"E";11001,"";11110,"E";11111,""},2,1)

It essentially performs an approximate look up of the calculated values
in the equivalent of this table:

CalcVal ReturnVal
____0.......=""
10010.......E
10011.......=""
11000.......E
11001.......=""
11110.......E
11111.......=""

You could also just put that table elsewhere in your workbook and refer
to it in the formulas:
=VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),Sheet2!$A$1:$B$7,2,1)

Do either of those work for you?

Ron

Ron, both version worked, thanks.

 

[Ron Coderre]

You're welcome. Glad I could help.

Ro

 

[GORDON]

I had another thought: Am I right in assuming that you are only
interested in flagging the "E" items and all others combinations should
be blank?

If yes, I think this formula will work for you:

=VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),{0,"";10010,"E";10011,"";11000,"E";11001,"";11110,"E";11111,""},2,1)

It essentially performs an approximate look up of the calculated values
in the equivalent of this table:

CalcVal ReturnVal
____0.......=""
10010.......E
10011.......=""
11000.......E
11001.......=""
11110.......E
11111.......=""

You could also just put that table elsewhere in your workbook and refer
to it in the formulas:
=VLOOKUP(((O2816<>"")*10^4)+((Q2816<>"")*10^3)+((R2816<>"")*10^2)+((S2816<>"")*10)+((T2816<>"")*1),Sheet2!$A$1:$B$7,2,1)

Do either of those work for you?

Ron

Ron what else do i have to remove if I wanted to leave out
((S2816<>"")*10) to not include that column, but all other still hold
true.

 

[Ron Rosenfeld]

I tried the formula and it worked somewhat. I think I may have misled
you in the wrong direction. I wanted the computer to automatically
either put an "E" or leave blank. When I removed some values in
columns (Q,R) it didn't go back to blank as I thought it would (O,S,T
are filled). It would work if I sequentialy put values in the columns
from O to T .

My formula works as per your initial description.

Your formula came across all on one line which makes it very hard for me to
compare it to mine. I am assuming it is identical except for the row
reference.

In your initial description, you do not have a circumstance of O,S & T being
filled and wanting a blank result. So the formula sees that O & S are full,
and returns an "E".

If you want different behavior, you must define it.




--ron

 

[Ron Coderre]

The ExcelForum hasn't been able to process responses, lately. I've bee
trying to bu results have been very sketch. Hopefully, this time....

I think I'll need you to summarize the new rules. It appears tha
there is only one "E" criteria now: O<>"", Q<>"".

Is that true? Do values in other O:T cells have any impact?

Ro

 

[GORDON]

My formula works as per your initial description.

Your formula came across all on one line which makes it very hard for
me to
compare it to mine. I am assuming it is identical except for the row
reference.

In your initial description, you do not have a circumstance of O,S & T
being
filled and wanting a blank result. So the formula sees that O & S are
full,
and returns an "E".

If you want different behavior, you must define it.




--ron

Ron, you are correct in assuming that if O,S & T are filled it should
give me a blank. If O&S have value then it should palce an "E". My
question was I have another worksheet which follows the same process
but it has one less column, it didn't have column S. I wanted to
modify your formula so that it would no longer check for column S and I
didn't know which part of the table to delete if I got rid of the S
column. Sorry if I confused you.

 

[Ron Rosenfeld]

Ron, you are correct in assuming that if O,S & T are filled it should
give me a blank. If O&S have value then it should palce an "E". My
question was I have another worksheet which follows the same process
but it has one less column, it didn't have column S. I wanted to
modify your formula so that it would no longer check for column S and I
didn't know which part of the table to delete if I got rid of the S
column. Sorry if I confused you.

OK so we have to add a condition to your initial 6. I am going to insert it as
condition 4a for consistency; and then we will construct the IF statement
again, working from the most to the least checks:

1)So in the first instance if there is a value in O then leave blank.
2) if there is a value in O and S then put an "E"
3) if there is a value O and Q then put an "E"
4) if there is a value in O, Q, and R then leave blank
-->4a) if there is a value in O, S & T then leave blank
5) if there is a value in O, Q, R and S then put an "E"
6) if there is a value in O, Q, R, S and T then leave blank

=IF(AND(O2816<>"",Q2816<>"",R2816<>"",S2816<>"",T2816<>""),"",
IF(AND(O2816<>"",Q2816<>"",R2816<>"",S2816<>""),"E",
IF(AND(O2816<>"",Q2816<>"",R2816<>""),"",
IF(AND(O2816<>"",S2816<>"",T2816<>""),"",
IF(AND(O2816<>"",Q2816<>""),"E",
IF(AND(O2816<>"",S2816<>""),"E",
IF(O2816<>"","","undefined")))))))

Of course, there is another method of approaching this -- at least on this
worksheet. And that is to find some simpler algorithm, other than this brute
force translation, of coming up with the correct answers.

With your latest addition (4a above), I note that the following formula will
give the same answers:

=IF(O2816="","",IF(AND(COUNTA(Q2816:T2816)>0,
MOD(COUNTA(Q2816:T2816),2)=1),"E",""))

This formula takes advantage of the fact that if O2816 does not have a value,
your specifications are undefined. Howevere if there is a value in O2816, then
whether to return an "E" or a <blank> depends on whether the number of filled
cells in Q2816:T2816 is an even or odd number.

====================

With regard to eliminating the checking for column S, in the first formula, you
can't just eliminate the references to S in the first formula above, because
then you will have duplicate, conflicting entries in lines 2 and 3 of that
formula. So you also have to decide what you want in that circumstance.

Probably simplest to just construct a table, and your IF statement, as I did
above.

On the other hand, if there is some simple algorithm that is applicable, as in
my second formula, you could just use that.




--ron

 

[GORDON]

Can anyone help me with this problem. When I run this formula I
sometimes get a correct answer and sometimes I get an incorrect answer.
See my attachment. Here is the formula that has me a bit confused.
=IF((A3<>"")*(A3<$F$2),IF((B3<>"")*(B3<$F$2),NETWORKDAYS(A3,IF(B3<A3,A3,B3)),NETWORKDAYS(A3,IF(C3="",$F$2,A3)))+IF((C3<>"")*(C3<$F$2),NETWORKDAYS(IF((C3>D3)*(D3<>""),D3,C3),IF((D3="")-(D3>$F$2),$F$2,D3)),NETWORKDAYS(IF(B3<>"",IF((D3="")-(D3>$F$2),$F$2,D3),$F$2),IF((D3="")-(D3>$F$2),$F2,D3)))+1,"")

A3 contains date in
B3 contains date out
C3 contains letter out
D3 contains Completed
E3 contains the formula
F2 contains the End Date which changes every week.


+-------------------------------------------------------------------+
|Filename: Image1.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4015 |
+-------------------------------------------------------------------+