# Need help with Fuctions or a formula, don't know which [solved]

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 :

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 ...

edit retag close merge delete

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

You need to simply use else clauses to make that work:

IF(A1<0.01;NA();IF(A1<=0.1;DoThis;IF(A1<=0.2;DoThat;IF(...))))


So each second option ("else") would only execute if the previous condition is not met.

( 2019-03-06 06:16:09 +0200 )edit

Same question asked at OpenOffice forum

( 2019-03-06 06:59:30 +0200 )edit

My apologies for posting on both forums looking for the answer, I should have given them more time here to respond to the question. I was not aware the same people checked both boards, which is nice of everyone to do helping on both boards.. Thought this board was not very active since I had seen a number of questions posted for some time with no answers. Quite embarrassing, again my apologies.

( 2019-03-06 08:02:27 +0200 )edit

I tried that example, and I did not get any error messages from doing it that way. But the answer was given as 0.00% so I must have done something wrong.

( 2019-03-06 08:06:41 +0200 )edit

That only means that you need to provide a sample spreadsheet with formulas, and comments where and what is expected...

( 2019-03-06 08:08:03 +0200 )edit

Just FYI, functions are e.g. SUM, AVERAGE, IF, VLOOKUP and a formula is a combination of one or more of functions.

( 2019-03-06 10:24:03 +0200 )edit

Sort by » oldest newest most voted

Thank you all for your help and your useful information. It turns out that the nested IF example Mike Kaganski gave me works out well, I don't know why it did not work out for me last night. Perhaps I made an typographical error entering it last night? I was very tired when I tried it. Anyway, as I said, I was trying to do this to use for writing purposes, to get believable/somewhat believable numbers for business & financial deals in stories.

I just need to tweak it and fine tune it, as I am not quite satisfied with the results just yet, but now I (and anyone who reads this) should be able to figure out how to get a good basic start and fine tune it. To help anyone who might see this and want to use it, I am going to show the nested IF formulas(?) I came up with as a basic starting point

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

=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 Percentages (Market %) column I used :

=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)))))


I got something like this as results :

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%


As you can see, the numbers in column 2 are pretty good, could use some fine-tuning. The numbers in column 3 could use even more tuning, but It gets me on the basic right track so to speak. I will mark this question solved if I can figure out how to do that, but anyone that has more to add please feel free. Again, thank you all for your help.

Edit : I also posted this question over on OpenOffice forum, and I was told it was a good idea to put in a link showing where that post is, in case someone wants or needs to look at it to help someone or when looking for help. That post is link text

more