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
• 28,700 points
55 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
• 53,160 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
• 53,160 points
57 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
• 53,160 points
148 views
0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?

Try this: =CONCATENATE(LEFT(A1,1), B1) READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 53,160 points
100 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
• 53,160 points
112 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,630 points
213 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,630 points
386 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,630 points
175 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 in Others by narikkadan
• 53,160 points
39 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
• 53,160 points
129 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