insert/replace with randbetween

[Halfdef]

Hello,

I am trying to use the randbetween function only for cells that currently have data in them. I am replacing the data that I have in the cells with the new randomly generated data. The data that I am trying to replace is between 1-10.

In other words, if the cell has a value (it will be between 1-10) then I want to randomly generate another value between 1-10.

I know how to use the randbetween 1-10, just not in an if statement.

I have tried this is if statements and cannot get it to work.

I am trying to get this done asap, thank you for the assistance.

Best,
Andrew

[martindwilson]

please change title

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title

[Halfdef]

Thank you martindwilson,

I have changed the title. What I am looking to do is replace existing values with randomly generated ones. But I don't want to input anything in the currently empty cells.

All assistance is welcome!

Best,
hd

[martindwilson]

still says

urgent!

get rid of that, the mods will only delete any answers until thats done

[Halfdef]

currently I am using this formula, but it will not allow it. "hijklmnop" is just random so that i can then find and replace that string with empty cell.

=IF(originalcell=blank, "hijklmnop", originalcell+RANDBETWEEN(-2,2))

[martindwilson]

=IF(A1="","hijklmnop", A1+RANDBETWEEN(-2,2))
mind you thats not between 1 and 10 is it?

[Halfdef]

martindwilson

thank you for the assistance. I have input the formula that you offered, but it does not seem to work. Do you have any other pointers? Are there steps or edits that you are not indicting?

Best,
hd

[martindwilson]

perhaps you are not explaining this as clearly as you think!
this is what i get

[Halfdef]

Hi MartindWilson,

Ok, it works. I was making an manual error.

But you are right, that is not between 1 and 10. So I adjusted it.

This is what I am now using: =IF(A2="","hijklmnop", RANDBETWEEN(1,10))

This looks solid, right?

If you are game, I have one more question: If I wanted to replace text values in cells with new values, and I had to randomly generate them from a few options (eg fun, fast, full) so that the new values, what would be the best way to modify the formula?

What I am trying to do with this second step is change existing values on a five point scale to a different five point scale. I know that I could do find and replace, but I was think that there would be a way to more faster with a formula.

Thank you for the assistance!

Best,
Andrew

[martindwilson]

=IF(ISTEXT(A1),CHOOSE(RANDBETWEEN(1,3),"fun","fast","full"),"do something else")

[Halfdef]

thank you for the assistance!!