Need help with Microsoft Excel... | Forex Factory

Options

Permalinks
Print Thread

Similar Threads

MT4 to Excel through Excel RTD (Finally!) 612 replies

MicroSoft Excel Forecast Formula 1 reply

Good with MT4, DDE and microsoft excel? Need help! 1 reply

Position Sizing for Microsoft Excel 2007 0 replies

Need a little help of a couple simple function in Excel 13 replies

Attachments: Need help with Microsoft Excel...
Exit Attachments

Need help with Microsoft Excel...

  • #1
  • First Post: Aug 24, 2006 12:05pm Aug 24, 2006 12:05pm
Sorry if this is not the right forum for this, but I really didn't know where to post it.

Anyway, I am wondering if someone here could help me with an Excel formula? It’s probably something pretty simple, but since my Excel skills are limited, I’m having trouble figuring it out. If someone can help, please email me at [email protected] and I’ll send you my info. Thanks.

CA1962
  • #2
  • Aug 24, 2006 12:12pm Aug 24, 2006 12:12pm
Can you not post it here? I'll try to help you out..
  • #3
  • Aug 24, 2006 12:55pm Aug 24, 2006 12:55pm
Thanks, eastmaels.

Here it is:

Let’s say I need a result for Cell D based on conditions that exist in Cells A, B and C. There are a total of nine conditions that I need to program into Cell D, but the result in Cell D will be determined by one, and only one of those conditions. Here are the conditions:

1). If “A” is greater than both “B” and “C”, then “D” will equal 5
2). If “A” is greater than “B” but less than “C”, then “D” will equal 3
3). If “A” is greater than “B” but equal to “C”, then “D” will equal 4
4). If “A” is equal to “B” but greater than “C”, then “D” will equal 1
5). If “A” is equal to “B” but less than “C”, then “D” will equal (-1)
6). If “A” is equal to both “B” and “C”, then “D” will equal 0
7). If “A” is less than “B” but greater than “C”, then “D” will equal (-3)
8). If “A” is less than both “B” and “C”, then “D” will equal (-5)
9). If “A” is less than “B” but equal to “C”, then “D” will equal (-4)

Any help you can provide would be greatly appreciated. Thanks again.

CA1962
  • #4
  • Aug 24, 2006 1:12pm Aug 24, 2006 1:12pm
Basically what you want to do is create an if() statement with your variables and you can make the output whatever you want.

here is a link I found: http://personal-computer-tutor.com/if1.htm

if that is not clear just search on google for "excel if statements"

hope that helps
  • #5
  • Aug 24, 2006 1:26pm Aug 24, 2006 1:26pm
Maybe I'm Old School but XL had a maximum of 7 Nested "IF" statements.

If this is still the case, you will have to break it down to 2 cell results being compared.
  • #6
  • Aug 24, 2006 1:32pm Aug 24, 2006 1:32pm
Quoting CA1962
Thanks, eastmaels.

Here it is:

Let’s say I need a result for Cell D based on conditions that exist in Cells A, B and C. There are a total of nine conditions that I need to program into Cell D, but the result in Cell D will be determined by one, and only one of those conditions. Here are the conditions:

1). If “A” is greater than both “B” and “C”, then “D” will equal 5
2). If “A” is greater than “B” but less than “C”, then “D” will equal 3
3). If “A” is greater than “B” but equal to “C”, then “D” will equal 4
4). If “A” is equal to “B” but greater than “C”, then “D” will equal 1
5). If “A” is equal to “B” but less than “C”, then “D” will equal (-1)
6). If “A” is equal to both “B” and “C”, then “D” will equal 0
7). If “A” is less than “B” but greater than “C”, then “D” will equal (-3)
8). If “A” is less than both “B” and “C”, then “D” will equal (-5)
9). If “A” is less than “B” but equal to “C”, then “D” will equal (-4)

Any help you can provide would be greatly appreciated. Thanks again.

CA1962
Probably you can put it in one large formula, but I think it is better to have first 2 other cells, describing the relation between A and B and betwen A and C. Lets call those 2 cells P and Q.
So P describes the relation between A and B and has the formula:
+if(A gt B,1,0)+if(A lt B,-1,0) gives 1 when A greater then B and -1 when A less then B
And Q describes the relation between A and C and has the formula:
+if(A gt C,1,0)+if(A lt C,-1,0) gives 1 when A greater then C and -1 when A less then C

Now we can get D from P and Q. I think D = 4*P+Q.

P.S. gt should be replaced with >. lt should be replaced with <.
  • #7
  • Edited 6:19pm Aug 24, 2006 6:02pm | Edited 6:19pm
Okay, it can be done with a Customized Worksheet function. Not sure if it's what is called in excel but I have an idea on how to do it.

You may want to look into the suggestions by others above first while I take a shot at tit..

P.S.
Here's a quick reference on what I think of doing:
http://office.microsoft.com/en-us/as...548461033.aspx

You have to make sure you have at least MS Excel 2000 version installed for it to work.
  • #8
  • Aug 24, 2006 6:20pm Aug 24, 2006 6:20pm
I'm not sure if what bemac said is right about the nesed if's and what not, but I made a formula that should do the job (provided there are unlimited if statements). I attached the txt fle because the formula wasn't working in here.

<c;3;4));if(a=b;if(a><c;-1;0));if(a><b;if(a><c;-5;-4));"");><c;3;4));if(a=b;if(a><c;-1;0));if(a><b;if(a><c;-5;-4));"");>
I can't really explain how it works, but I have extensive experience in Excel with formulas like that.<c;3;4))><c;3;4))><c;3;>
Hope that works for ya,
andersenws</c;3;></c;3;4))></c;3;4))></c;-5;-4));"");></b;if(a></c;-1;0));if(a></c;3;4));if(a=b;if(a></c;-5;-4));"");></b;if(a></c;-1;0));if(a></c;3;4));if(a=b;if(a>
Attached File(s)
File Type: txt formula.txt   < 1 KB | 477 downloads
"Youth is the trustee of prosperity." - Benjamin Disraeli
  • #9
  • Aug 24, 2006 6:49pm Aug 24, 2006 6:49pm
Bemac is correct about the nested if limitation in excel. U may need visual basic to do this or as suggested break it down to two snippets.

I think if we could interview God He'd admit that Life and the universe break down to 2 or 3 well written lines of code.
In trading, there is no bullshit. You either make money or you don't.
  • #10
  • Aug 24, 2006 10:27pm Aug 24, 2006 10:27pm
Thanks to everybody.

With all the info here, I was finally able to figure it out. Bemac was right, you cannot have more than 7 Nested "IF" statements in a single cell. I broke everything into two columns and it works great. Thanks again to all.

CA1962
  • #11
  • Aug 24, 2006 11:45pm Aug 24, 2006 11:45pm
No problem,

Good grey cells workout.

I built this that you may want to look at. Up to you.
Attached Image
  • #12
  • Edited Aug 25, 2006 2:24am Aug 24, 2006 11:49pm | Edited Aug 25, 2006 2:24am
The Zip

*EDIT*
ERROR in G8 Should Read

=IF(AND(D4=E4,D4=F4),0,0)
*END EDIT*
Attached File(s)
File Type: zip CA1962.zip   3 KB | 698 downloads
  • #13
  • Aug 26, 2006 8:41am Aug 26, 2006 8:41am
Thanks for the worksheet Bemac - it looks a lot better than the one I put together.

Regards,

CA1962
  • #14
  • Last Post: Apr 28, 2007 7:08pm Apr 28, 2007 7:08pm
Quoting eastmaels
Can you not post it here? I'll try to help you out..
Greetings,

I am wondering if you could help me by developing a very simple Excel spreadsheet to help me keep track of my trades and account balance. Here is what I need:

Place to input my original deposit amount:

Spreadsheet calculates 5% and gives that figure, and let's me enter the date next to when that 5% was reached:

Forecasts the future (account balance and date) based upon the previous 5 dates of when the 5% was reached.

Can anyone help with this?

Thank you in advance for any replies.

Mike
0 traders viewing now