Hello,

I have a function which checks for data in a certain field if data is
in the field I want my function to do nothing and the value currently
in the field to stay there, however; if there is not data in the field
I would like a random number to be chose. The problem I am having is
when the function is false I don't know how to just keep the same value
when I do it like this:

=IF(A3="",(RANDBETWEEN(1,E2)))

it fills the field with FALSE, if the value is false there will be data
in this field already and I want that data to remain there. Any idea?

Thanks for your help!!


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=375800

=IF(A3="",RANDBETWEEN(1,E2),A3)
should work
note, no bracket in front of RANDBETWEEN
--
Greetings from New Zealand
Bill K

"matty_g" wrote in
message ...

Hello,

I have a function which checks for data in a certain field if data is
in the field I want my function to do nothing and the value currently
in the field to stay there, however; if there is not data in the field
I would like a random number to be chose. The problem I am having is
when the function is false I don't know how to just keep the same value
when I do it like this:

=IF(A3="",(RANDBETWEEN(1,E2)))

it fills the field with FALSE, if the value is false there will be data
in this field already and I want that data to remain there. Any idea?

Thanks for your help!!


--
matty_g
------------------------------------------------------------------------
matty_g's Profile:
http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=375800

Good evening matty_g

I think your problem here is nothing more sinister than a slight syntax
hiccup. The =IF() function must always have the condition plus two
arguments (what to do if TRUE, and what to do if FALSE) - you were
missing the FALSE argument so Excel just filled in the best it could
rather than give you an error. Your slightly rewritten formula should
help out.

=IF(A3="",RANDBETWEEN(1,E2),A3)

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=375800

Thanks for your help,

however I am still having the problem with this fact, when the value is
false there is already a value in the field. I would like to just keep
this value and do nothing to it. That is my problem.

something like this =IF(A2="",RANDBETWEEN(1,E2),'-do nothing-')

do nothing mean just leave it the way it is. I was also trying to
figure out how to possibly save the value to a variable then display
the variable if false. Not sure if this is possible. I have some
programming experience but not much excel. It was just a thought due
to the programming I have done. anyways any help would be greatly
appreciated

Thanks again


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=375800

Hi, thanks

the problem is that when false, there is already a value in that field
which I would just like to keep there, not get one from anywhere else.
Like this:

=IF(A3="", RANDBETWEEN(1,E2), '-do nothing-')

meaning just leave the current value there. I don't know how to
accomplish this. I was looking at the possiblity of some how saving the
value into some sort of variable. I have some programming experience
but not a whole lot with excel so this was just a thought.

Any help would be greatly appreciated!


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=375800

Matty

I read your conditions as...........
if a3 is empty take a random number between 1 and e2
If a3 is not empty, return a3.

The functions in our replies
=IF(A3="",RANDBETWEEN(1,E2),A3) should do that.

"the problem is that when false....." which cell is false?

Where are you entering the function? In cell e3?

--
Greetings from New Zealand
Bill K

"matty_g" wrote in
message ...

Hi, thanks

the problem is that when false, there is already a value in that field
which I would just like to keep there, not get one from anywhere else.
Like this:

=IF(A3="", RANDBETWEEN(1,E2), '-do nothing-')

meaning just leave the current value there. I don't know how to
accomplish this. I was looking at the possiblity of some how saving the
value into some sort of variable. I have some programming experience
but not a whole lot with excel so this was just a thought.

Any help would be greatly appreciated!


--
matty_g
------------------------------------------------------------------------
matty_g's Profile:
http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=375800

the data should go into f2...but when I put f2 for the value if false it
gives me an error. But if it is false I just want the value in f2 to
remain the same


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=375800

One thing I see missing from the above posts is that, because you are
setting up a circular reference, you need to turn iteration on via the
Options dialog. Then enter the formula f2=IF(a3="",RAND....,f2)

HTH


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=375800

perfect!!! thanks a lot, iteration being off was the problem!


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=375800