Get random value in the range of plus minus 10 of a cell value in Excel

0 votes

I want to calculate a random value for each positive number in a column, but I want the value to be placed in a 10% range, so it can only be 10% higher or lower than the original value.

The result must be greater than zero, which is the second prerequisite.

I attempted a formula that looked like this, but it appears to be incorrect because it occasionally returns values that are lower than zero, which contradicts the second condition.

=IF(RAND()<0.5,(B2+(B2/100)*90)-(RANDBETWEEN(B2-0.1,B2+0.1)),(B2-(B2/100)*90)+(RANDBETWEEN(B2-0.1,B2+0.1)))
Dec 25, 2022 in Others by Kithuzzz
• 38,010 points
441 views

1 answer to this question.

0 votes

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if you are only working with integers and your value is in B2? Since 0.9 * a positive number is still a positive number, this cannot return a negative number if B2 is positive.

If you require floating point numbers, you can create a random number between 0 and 1 using the RAND() function, then divide it by 5 and remove 0.5 to get a number between -0.1 and 0.1. Then, to produce a result between 0.9 and 1.1 times the original number, add 1 and multiply that result by the original value:

=(1+(RAND()-0.5)/5)*B2
answered Dec 25, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel-How can I get the address of a cell instead of a value?

There are various difficulties in this. Which ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,420 points
261 views
0 votes
1 answer

How to programmatically get the values of a spilled Excel range in VBA?

By using the Text property, I was ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,420 points
684 views
0 votes
1 answer
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
508 views
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
502 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,238 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,426 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
699 views
0 votes
1 answer

Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,420 points
440 views
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,420 points
531 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP