I am using the formula below, which works fine. I am wondering if there is a way I can have the formula randomly add or subtract 1 from the result. I don't want to have to decide if it's going to add or subtract, I want the formula (or VBA) to "decide" automatically.
Please Login or Register to view this content.
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
Hi Tom,
This formula will return a one or negative one.
=IF(RANDBETWEEN(0,1)=0,-1,1)
Does that work with what you have?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi,
Perhaps
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
My bad guys, I gave y'all misleading information. The add or subtract should be .001, not 1.
Marvin.....Not quite what I was looking for, but, that formula will be useful to me for something else.
Richard......I do believe you are on the right track, I just need it to add or subtract by .001 instead of 1 like I originally posted.
In that case just modify toOriginally Posted by gmr4evr1
Formula:Please Login or Register to view this content.
Odd Anomolies.xlsx
Dang it, you beat me too it. I kind-a figured that was all I needed to do, but I was testing it to be sure. Upon testing I found something strange happening. Odd anomalies and strange phenomenon.
When I drag the formula in M1 down to M2, the values in column E change. If I drag the formula down to any row, then drag up to row 2, then back down again, I get a different "answer" in column N every time. I don't think this should be happening as both column E and M have the same exact formula and the formula is for columns C and D. I am cun-fuddled. I have attached the file.
This is because formulae in various columns use the RAND function which is volatile and changes when cells on the worksheet are changed (as per dragging down a column).Please Login or Register to view this content.
Last edited by JohnTopley; 11-01-2015 at 04:29 PM.
Crud.....I didn't know that. That might defeat the entire purpose of what I was doing.. Thanks John, for the info.
Just put the RAND part of the formula in a single cell somewhere (which returns +0.001 or -0.001), then your formula could be:
=IF(C3="","",AVERAGE(LEFT(C3,5),D3))+$X$1
assuming X1 contains the RAND formula. Then you can copy this across and down as required.
Hope this helps.
Pete
In my sample sheet that I posted above, I put this part of the RAND in R1
Then changed the formula in the other cells toPlease Login or Register to view this content.
But now, whatever value is in R1 it adds or subtracts that value to all the cells in the column. Ex: R1 has a value of 0.001, the formulaPlease Login or Register to view this content.
Is in E3 through E17 and they all add 0.001.Please Login or Register to view this content.
If R1 has -0.001, then they all subtract 0.001.
What I want is for the cells in E to randomly add or subtract 0.001. Ex: E2, E3, E7 and E9 will add 0.001 while E4 through E6, E8 and E10 will subtract 0.001. Then, when the values in C3 and/or D3 change, the cells in E randomly add or subtract in a different order.
Hopefully I didn't make that explanation confusing.
I think what you mean is that you want the random subtraction/addition, but for this NOT to change if other cells change.
As RAND is volatile, I don't know how you can achieve that.
Incidentally, the formula I gave you is slightly incorrect - it should be:
=IF(C3="","",AVERAGE(LEFT(C3,5),D3)+$R$1)
although this is not what you need.
Hope this helps.
Pete
Right, when the cells in C and/or D change, I still want column E to do random subtraction/addition.
I thank you all for your help and I will keep looking into this or an alternative method.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks