working with IF statements

[matty_g]

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!!

[dominicb]

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

[Bill Kuunders]

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

"matty_g" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[matty_g]

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

[Bill Kuunders]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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_g]

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

[MrShorty]

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

[matty_g]

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

[Bill Kuunders]

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

"matty_g" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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

"matty_g" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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

"matty_g" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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

"matty_g" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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

"matty_g" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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

"matty_g" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bill Kuunders]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>