Ask Your Question
0

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

asked 2019-03-06 03:55:39 +0200

SteveH_66 gravatar image

updated 2019-03-07 04:53:41 +0200

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 ... (more)

edit retag flag offensive close merge delete

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-06 06:16:09 +0200 )edit

Same question asked at OpenOffice forum

robleyd gravatar imagerobleyd ( 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.

SteveH_66 gravatar imageSteveH_66 ( 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.

SteveH_66 gravatar imageSteveH_66 ( 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...

Mike Kaganski gravatar imageMike Kaganski ( 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.

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

1 Answer

Sort by » oldest newest most voted
0

answered 2019-03-07 04:52:03 +0200

SteveH_66 gravatar image

updated 2019-03-07 05:34:37 +0200

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

edit flag offensive delete link more

Comments

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

Lupp gravatar imageLupp ( 2019-03-07 21:37:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-06 03:55:39 +0200

Seen: 76 times

Last updated: Mar 07