IF( OR IF)

[Silvabod]

Cells in Col K contain EITHER text OR null, as the result of a function
test.
Cells in Col L contain EITHER text OR null, as the result of a different
function (which is triggered only when the function in Col K cells yields a
text value). Since 2nd function is dependant on the first, both cannot exist
in the same cell (circular reference).

Need - Cells in Col M to contain EITHER the textvalue in K (if positive) OR
the textvalue in L (if positive) else null

I can't get my head round the correct function nesting layout - help?

=IF(OR(K1,"WINNER","WINNER",""(L1, "BOOBY", "BOOBY")"") don't work! (nor can
I find a google example).

In practical terms - Cells K1:K60 function tests the adjacent row of 6
cells, if ALL 6 are positive, displays WINNER.
If ANY cell in Col K yields WINNER, Cells L1:L60 function tests same row of
6 cells, if ALL 6 are negative, displays "BOOBY". If both tests fail, cells
K and L are left blank. These functions work.

Problem is that Winner and Booby are displayed in adjacent columns - what I
want is just a ONE column print display, SO - hide cols K & L, copy text in
K or L into M (and there cannot be text in BOTH K and L in same row, it
can't be both a winner and booby!).

 

[Max]

Try in M1:
=IF(K1="WINNER","WINNER",IF(L1="BOOBY","BOOBY",""))

 

[Silvabod]

Max, logic looks good, but unfortunately doesn't work.
I have deliberately created a "Winner", scenario to test, which demonstrates
that the functions of Cols I and J do work - your suggested solution is in
K1:K6 with no output (and no error).
(the OP had wrong col ID's - these are corrected here)

Is it because Col I and J contents are functions, and what I need is the
RESULT of the formulae? (absolute value?)
How do I do that?

(Tried posting the small spreadsheet, as attachment but it didn't post).

 

[Max]

:
....

(Tried posting the small spreadsheet, as attachment but it didn't post).

Luckily it didn't <g>. Please do not post any attachments to the newsgroups.

You could use a free filehost, say*: http://flypicture.com/
to upload your sample file, and then post just the *link* to d/l the file

*Another alternative: http://cjoint.com/index.php

Try the above, and post the link in your response
(Think I need to take a closer look at your file/set-up)

 

[Arvi Laanemets]

Hi

What do you mean with 'null' as function result? Logically it must be or 0
or "", as you can't set the cell value to Null in Excel, is it?

=IF(K1<>"",K1,IF(L1<>"",L1,""))
or
=IF(K1<>0,K1,IF(L1<>0,L1,""))

 

[Silvabod]

I'm a novice - for "null" read "" (sorry, thought they were the same
thing).
Have tried your solution, the result is a blank cell when it should be
"WINNER", so regrettably, it doesn't work.

=IF(K1<>"",K1,IF(L1<>"",L1,""))

Cell K1 contains a complex multiple function, the result of which is
"WINNER" only if all the functions return a positive (exact reverse for L1,
different text).
If the function conditions are not fully met, cells K1 and L1 remain as "".

What I need is
IF K1'a actual display is "WINNER", M1 = "WINNER" ? I think I'm in the
murky realms of Absolute values ?

Silvabod


..

 

[Silvabod]

In cell M1
=IF(K1<>"",K1,IF(L1<>"",L1,"")) - works.
Thanks, everyone, for your help.

If anyone's downloaded the worksheet, you will note that the col. ID's are
different to those posted - the "real" subject columns are I, J and K. (that
was not the cause of earlier "not working" problem).

Thanks again, Silvabod

 

[Biff]

Hi!

Check your inbox!

Biff

 

[Silvabod]

Already did, and replied - now have the best of all worlds, a choice! Have
combined the best of both worksheets.
Thanks, Biff (publicly, in addition to teh private ones).
Silvabod

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Already did, and replied - now have the best of all worlds, a choice!
Have combined the best of both worksheets.
Thanks, Biff (publicly, in addition to teh private ones).
Silvabod

 

[Max]

Just thought you might also be interested in this file ..
http://savefile.com/files/7565212
Randomization_Lotto_program.xls

It's a breeze to use/set-up, and great for generating random picks
for whatever game you're into ..

 

[Silvabod]

Alas! link gives "page cannot be displayed" so cannot check.

But - my spreadsheet is not a game. It's specifically designed as a weekly
print report for a noticeboard.

Once populated with players/players numbers, all user has to do is enter the
6 "lottery draw" numbers each week, and print the thing.
Entering the 6 weekly numbers automatically flags the "matches" and when
there's a winner (6 matches in one row), flags both "winner" and "booby
prize" (for NO matches) - replacing the weekly hour or more spent manually
checking and highlighting, and avoiding dyslexic error ...

The "random pick" you mention is I believe more appropriate to an
individual. Mine is a "group" service report benefitting the publican (who
runs it for free, all money collected goes out in prizes) and the 60
punters, who come in to check their progress.

If anyone wants it, post a request with e_mail ID. It's only 40kbs, and
free.
If not for the lottery, it could be useful for the code - esp how to
visually highlight "matched" cells WITHOUT colour! (i.e for black-and-white
reports - it's done via cute conditional formatting).

Silvabod

 

[Max]

... The "random pick" you mention is I believe
more appropriate to an individual ..

Not really, albeit that would be the obvious.
It could be also be put to a number of creative uses,
including the running of private "self-organized" lotto/lucky draws,
independent of national lotteries' results <g>.

 

[Silvabod]

Depending on your age - "Housey-Housey" (Victorian kids game, still popular
even now, or, commercially, Bingo (same thing) ? See what you mean!

Mine's not a competition/game, just a utility (though it wouldn't be
difficult to incorporate a random number generator to make it so).
Silvabod

 

[Guest]

=IF(AND(OR(E6>=0,J6>=0,O6>=0)),(E6+J6+O6)," ") why must there be a entry
into 2 of cells in formula for it to produce a total. want it to show any
entry or total if more than one. Also is there a way to check for blank cell?

 

[Bob Phillips]

Do you mean

=IF(OR(AND(E6>=0,J6>=0),AND(E6>=,O6>=0),AND(J6>=0,O6>=0)),E6+J6+O6," ")


test for blank with

=ISBLANK(A1)
or
A1=""
or
LEN(A1)=0

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

=IF(AND(OR(E6>=0,J6>=0,O6>=0)),(E6+J6+O6)," ")

Perhaps try instead: =IF(SUM(E6,J6,O6)=0,"",SUM(E6,J6,O6))

Also is there a way to check for blank cell?

One way, e.g. in R6: =ISBLANK(E6)
which returns TRUE if E6 is empty

 

[paulrm906]

COULD SOMEONE PLEASE HELP ME.
A1 = 01-Jan-05 = The first day of the year.
B1 = 16-June 05 = Their starting date.
C1 = 10-Oct 05 = Their finishing date.

example I have a program for my staff when they resign to count how
many days they have worked here this year and to make sure they have
not take to many days holiday and in cell A1 I have 01-Jan-05 and in
cell B1 I have their starting date for example 16-June-05 and in cell
C1 I have their finishing date for example 10-Oct-05. Now I am trying
to work out a formula in D1 to tell me how many days the employee has
worked here this year, so what I want is a formula to count the days
between cell C1 (their finishing date) and A1 but if B1 is greater then
A1 I want the formula to count the number of days between C1 and B1. I
have been trying to work this out now for sometime.

And many thanks in advance if you can help me.

 

[paulrm906]

A1 = 01-Jan-05 = The first day of the year.
B1 = 16-June 05 = Their starting date.
C1 = 10-Oct 05 = Their finishing date.

example I have a program for my staff when they resign to count how
many days they have worked here this year and to make sure they have
not take too many days holiday and in cell A1 I have 01-Jan-05 and in
cell B1 I have their starting date for example 16-June-05 and in cell
C1 I have their finishing date for example 10-Oct-05. Now I am trying
to work out a formula in D1 to tell me how many days the employee has
worked here this year, so what I want is a formula to count the days
between cell C1 (their finishing date) and A1 but if B1 is greater then
A1 I want the formula to count the number of days between C1 and B1. I
have been trying to work this out now for sometime.

And many thanks in advance if you can help me.

 

[Bob Phillips]

=C1-MAX(A1,B1)

--

HTH

RP
(remove nothere from the email address if mailing direct)