Needing Help on a If then Then Function

[mussonip]

Hello,
I am building a spread sheet for hypothetical returns in a retirement annuity. I can use a standard return of a percentage in some cases.
In other cases I am using a =RANDBETWEEN(0,999)/100 function to get a percentage between 0 and 10%. This works well.
However, we all know that sometime the market can give a low or negative return over the year. My annuity uses S&P Index Future to only capture and credit the upside in a market, eliminating any negative returns in the product owned by the retiree.
In that case I would like to create a function that works like this
"If percentage is greater than say 2% make no change... if 1.99% or less, then enter zero.


Is This doable? It seems simple, but I am an excel novice.

Thank you in advance

[AliGW]

How about this?

=IF(RANDBETWEEN(0,999)/100>=0.02,RANDBETWEEN(0,999)/100,0)

[Glenn Kennedy]

That won't work...as RANDBETWEEN runs twice, once with the >0.02* criterion and once without it.... so the first RANDBETWEEN can be TRUE and the second one be less than 2%...

* It should be 2, not 0.02, but in any event, it doesn't work...

[AliGW]

Ah, yes - silly me (on both counts)!!!

[Glenn Kennedy]

I think you need 2 columns... in A1, copied down
=RANDBETWEEN(0,999)/100

and in B1, copied down:
=IF(A1<=2,0,RANDBETWEEN(200,999)/100)

[Glenn Kennedy]

My turn to get it wrong. Use this:

=IF(RAND()<=0.2,0,RANDBETWEEN(200,999)/100)

[AliGW]

How does that stop the RANDBETWEEN from generating a number lower than 2?

[mussonip]

Hello Ali,
That was fast resspnse. I have entered it, it still seems to be returning values <2.0.
Can I send you this sheet to review?

Thank you

[Glenn Kennedy]

Should have gone to Specsavers... (I was there yesterday...)

=IF(RAND()<=0.2,0,RANDBETWEEN(200,999)/100)

[AliGW]

So it would appear ... Should have gone to Specsavers!!!

[mussonip]

Hello Glenn,
I think you have solved it., I have to run out for the rest of the day ( arrgh, part time job), but will apply it when I get back tonight from your sheet into mine.
Thank you, Thank you.

Pete

[AliGW]

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[mussonip]

Hello Glenn.
I used your solution. It works great. However, column B is doing a new RANDBETWEEN calculation.
I would like column B to hold the results from Col A that are >2 but enter those results in col A that are <2 as zero with out a new calculation in that cell in Col B.
Is that do-able?

Thanking you in advance for your knowledge and time.
Pete

[Glenn Kennedy]

See post 6...