[Solved] Need help with Functions or a Formula, not sure

[MountainMan]

I am trying to do a Function or formula or something, not even sure what I need to be doing or how to do it. It is for some hypothetical situations. What I am wanting to do is to have a column of random numbers, then 2 columns of numbers in percentage based off of the random number in the cell on that same row. So say Column A for the list of random numbers, Column B and Column C for the profits by percentage. The hypothetical numbers need to look real for the situation though. Like this :

Code: Select all

random numbers	           deal profit	trade profit
0.5924370951212	           59.24%	        592.44%
0.986644999118732	           98.66%	        986.64%
0.69899366207306	           69.90%	        698.99%
0.29986947345673	           29.99%	        299.87%
0.175831086796236	           17.58%	        175.83%

As you can see, some of the numbers in the 2nd column would be OK for a business trade or a stock trade. All of the numbers in column 2 would even be fairly realistic for a stock trade if you got very lucky on some trades. The numbers in column 3 would not be realistic for business transactions (unless you were a bank maybe? ) but some of them might look realistic for a stock trade, those trades like google or apple when you catch the stock way down or got in on the ground floor and held on.

I can go through the whole spreadsheet doing formulas one at a time and make it look realistic, but it is very tedious if you have like 100 or more random numbers you want to make realistic. So, I tried to do a formula in the first cell of Column B and Column C, and then drag & fill below so that they would process the numbers on each row of Column A. I tried nested IF Functions, even tried nested IF & AND Functions, trying to get results that would look realistic as hypothetical numbers.

But I couldn't make it work. The only way I could think of to do it was to have a separate IF Function for each range : like if it is .01 to .1 do this, if it is .11 to .2 do this, and so on up to if it is .91 to .99 do this. I could get it to work using an IF Function for A1<=.3, then like nested IF for A1>.3, etc. But I don't know how to specify from .11 to .2 in a Function. I tried like if(A1=.11:.2,(A1*1),0) but that did not work, got an error code.

Does anyone know how I might set this thing up so that I can use nested IF Functions and specify a range like .11 to .2? I was thinking nested IF's like (A1>=.1 do this, A1>=.2, A1>=.3, etc) would not work because if the number was like .3 then it would meet the requirements of all 3 if statements.

If anyone can figure out what I'm trying to say and knows a way to do this setup I wold appreciate the help. Thanks

[FJCC]

You might be able to use a VLOOKUP() function to do what you want. Please explain what you want to do as the value of the random number changes. How is the random number handled differently if it is 0.2 versus 0.3?

[MountainMan]

Wow, thanks for such quick help FJCC. As I tried to explain, not very clearly I am sure, I am using these random numbers for hypothetical uses, like writing. As you can see, .98 when changed to a percentage multiplying by 1 is 98% and by 10 is 980%. That would not be very realistic for writing. So, I was wanting to have a way to automatically lower those numbers as they got higher.

So like .2 would be 20% in Column B and 200% in Column C, .3 would be 30% in B and 300% in C, and so on. So what I wanted to do was to reduce the size of those numbers over in Columns B & C, like maybe as the random number gets bigger, you divide it by a larger factor. Not even sure how to do that well, but maybe like .3 to .39 you divide by 1.5, .4 to .49 you divide by 1.75 or something and so on like that.

If you could specify .3 to .39 in the IF Function, then you could break the range up that way, but I don't know how to do that. So I guess I'm looking to do something like a bunch of nested IF's, say from .3 to .9, and each one use a slightly larger factor to reduce the number. I was just thinking that if you had 8 IF Functions (one for below .3, and 7 more from .3 up to .9) how would you work the Functions/math?

For example, if a cell had the random number .78251, it seems to me that it would meet the criteria of all of the IF's from .3 to .7, so it would satisfy 5 IF's. Wouldn't that confuse the formula or whatever it would be called and throw an error? Thanks for the help.

[robleyd]

Same question asked on AskLibreOffice

If you have a string of IF - effectively IF/ELSE - the first IF that meets the criteria will be actioned, so it is important to get them in the right order.

[MountainMan]

My apologize for asking the same question on both forums, I should have probably given them more time to look at the problem and provide an answer before posting elsewhere looking. Quite embarrassing.

[keme]

My apologize for asking the same question on both forums, I should have probably given them more time to look at the problem and provide an answer before posting elsewhere looking.

Posting about the same case in multiple channels is known as crossposting. Given that a few conditions are met, this is generally OK.

Condition 1: Crossposting adds to the purpose. Many of the helpers here are also there, but not everyone, so you have a better chance of getting a solution with crossposting.
Condition 2: Crossposting does not lead to superfluous response. Cross-link to make all responses visible to all potential users/respondents. (In each of your postings, provide a link to your other posting of the same question in that other channel.)

In most forums you are allowed to edit your own postings to insert a backlink. If the edit option is not available for the original post, provide the backlink by posting a comment, like robleyd did for you this time.

When it is done this way, helpers can easily check the other forum to see whether the issue has already been resolved. Less time wasted.

Multiposting is similar, but not the same. Posting the same question multiple times in the same channel (e.g. in different subforums here) is generally frowned upon. You did not do that (as far as I can tell).

[Hagar Delest]

Was about to confirm it was ok but thanks keme for the detailed reply right on spot!

[FJCC]

The attached file shows two solutions, neither of which uses IF() functions. In column B I used a VLOOKUP() function to scale the random numbers with the lookup table in columns H and I. In column C I used an exponential function to do the scaling. Starting in row 17 I made a graph to show how the two scalings work over the range 0 to 1. The lookup table gives a jumpy result but that may be fine for your purpose.

[MountainMan]

Thank you all for your information and assistance. I am pleased to find out that it wasn't so bad that I posted in both forums. I knew that multiposting on the same board isn't a good idea for making friends and getting help, but thanks for adding that in there keme, as it would be very good to know for someone who does not know this .

FJCC, thanks for your help, especially going to the trouble of doing up a spreadsheet to illustrate what you were talking about. It wasn't quite what I was looking for, but it was interesting and potentially very helpful for someone who sees this and that happens to be what they are looking to do.

Mike Kaganski over on the LibreOffice forums had pointed me in the right direction with a nested IF formula(?). I tried it once and it didn't work, but trying it again it worked, must have been tired and made a typographical error the first time. It was in my post there https://ask.libreoffice.org/en/question ... -id-185789 on that forum. Robleyd, thanks for pointing out the need to get the IF's in the right order in a nested IF formula, very good to know.

I would like to post a short description of what I figured out working off of the formula Mike Kaganski so helpfully provided over on the other forum, in case someone sees it here but not there. Hopefully that is OK. As I said, I was doing this to use random numbers and turn them into numbers that might seem realistic/somewhat realistic when writing. I came up with these formulas :

For the business deals column (Biz deals) I used :

Code: Select all

=IF(M3<0.01,NA(),IF(M3<=0.1,M3*5,IF(M3<=0.3,M3*1,IF(M3<=0.7,M3*0.8,IF(M3<=0.99999,M3*0.6)))))

For the market trades column (Market %) I used :

Code: Select all

=IF(M3<0.01,NA(),IF(M3<=0.1,M3*25,IF(M3<=0.3,M3*9,IF(M3<=0.7,M3*5,IF(M3<=0.99999,M3*2.75)))))

When I ran them, I got something like this :

Code: Select all

Random #'s	Biz deals	Market %
		
0.904034308417288	54.24%	248.61%
0.658807014543739	52.70%	329.40%
0.413419293358763	33.07%	206.71%
0.236204227067275	23.62%	212.58%
0.70662972494707	42.40%	194.32%

That is a little more realistic than the simple calculation I ran to get the numbers I gave in my Original Post, still not quite what I like but I can fine-tune it from there. I am going to mark this solved if I can figure out how, but if anyone has anything to add please do. Thanks so much for your help and assistance, you folks are such a blessing to we novices.