If with choices

I need to look at a condition and then display from choices which have a weightage.

Example: if (cell A = "yes", "yes", choice)

where choice is either N or NO. N and NO should be picked 20%, 80% of the times respectively.

I tried with rand like this

if (cell A = "yes", "yes", if(rand()<0.2,"N", "NO"))

But I see this changing every time there is a change in the sheet. In my case there are going to be tons of changes. I can use paste special and get away but I was wondering if there is any other option available in excel.

Thanks for the help.

Hi DILIPandey,

Thanks for the quick reply.

The triggering element is random. So if the 2nd if statement is encountered 100 times then approximately 20 times I need to see "N" and and 80 times I need to see "NO"

Here is an example sheet.

Thanks again

You could change the Workbook Calculation to Manual. It is in the Options. Note that you would then need to force any future calculations (hit F9) and that would again reset your "N" or "No" cells. Paste values may be your best choice.

Pauley