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

English
, ,
#1

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

Edit: My apologies for posting this question over on the OpenOffice forum as well, I should have given people more time to respond here. Quite embarrassing. I tried the method suggested in the comment by Mike Kaganski, I did not get any errors but the answer came out 0.00% – so I must be doing something wrong.

#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

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.

#3

Same question asked at OpenOffice forum

#4

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.

#5

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.

#6

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

#7

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

#8

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

#9

@SteveH_66: Click the checkmark next to the answer that solved your problem to mark the question solved.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.