excel formula

[RobcPettit]

Hi, Is it possible to have a formula in a cell to do the following:
Range("a1") can equal 1 to 5.
Range("b2") can equal either 'Playing', 'Winner' or 'Loser'

If A1 = 1 THEN B1 = " "
If A1 = 2 THEN B1 = "Playing "
If A1 = 3 THEN B1 = "Playing or Winner or Loser"
If A1 = 4 THEN B1 = "Playing or Winner or Loser"
If A1 = 5 THEN B1 = "Winner or Loser".
These values update automatically, I have no control over what the
values will be, but these are the only possible outcomes. What Id like
to do in 'C1' is:

If a1 greater than 1 then c1 = b1. I can do that. But if c1 = either
Winner or Loser, and the value in a1 = 1 I dont want c1 to update until
a1 = > 1. Does that make sense.
eg; a1 = 1, b1 = playing, c1 = "" (Im assuming Ive just started
updating data)
a1 = 2, b1 = Playing, c1 = Playing
a1 = 3, b1 = Winner or Loser, c1 = Winner or Loser (Once
winner/Loser entered, cycle starts again.)
a1 = 1, b1 = Winner or Loser (because of prev result.)
a1 = 2, b1 = Playing, all starts again etc.

Ive tried nesting If's but cant seem to crack it. Im thinking I may
have to write a function.

Regards Robert

 

[bobbo]

Type this into c1. I think this will do what you want. Your explanation
is a bit cryptic.



=if(and(a1=1,or(b1="WINNER","LOSER")),"",b1)

 

[RobcPettit]

Thankyou for your reply. I tried the formula but it would not return a
value. After re reading my post, your right it is a bit criptic. If I
have =b1 in c1. Then if the result in c1 = Winner or loser, I dont want
this value to change when a1 = 1. Its to do with a program that feed
info into excel, what tends to happen is a1 will = 1 b1 = playing, and
these values dont change for about 20 seconds. But if the value in b1 =
winning or losing the values change in about 2 seconds. I need to hold
this value long anough for me to run a macro to do things with the
data. Whence I thought if I could shove the results some were else. At
the moment though the values are changing at the same time. When a1 =
1, the game isnt in play whence the reason for wanting to skip 1.
Regards Robert

 

[bobbo]

RobcPettit wrote:
I think you were right in thinking that you might have to make a custom
function. You might try posting in the excel worksheet functions group.
The folks in there are quite clever and might be able to solve your
problem without having to code it.

HTH