Need a little help of a couple simple function in Excel | Forex Factory

Options

Permalinks
Print Thread

Similar Threads

required to function call function 2 replies

Time-dependence of Excel Rand function 13 replies

stop loss function and edit time function for Breakout EA 1 reply

A simple indicator, need just a very little help 3 replies

I need help with a particular Excel Function and Formula 16 replies

Attachments: Need a little help of a couple simple function in Excel
Exit Attachments

Need a little help of a couple simple function in Excel

  • #1
  • First Post: Jun 7, 2007 11:53pm Jun 7, 2007 11:53pm
Hi,

I want to do some Forex data entry into Excel and just need a little help on a certain simple Excel functions as follows and your help is appreciated.

1) When I enter a number in Column A, Row1, I want the same number (data) to appear at the same time in Column D, Row 1 as well. When I enter in A2, same number appears in D2, in A3 the same data also appear in D3, and so on and so on. So, date in A1 = D1, A2=D2.........AZ=D26, etc. This saves me time from entering the same data twice in 2 different locations on the same row but in different columns (as they are the same).

2) - Same requirement as 1), only that when I enter a data in A1, same data appears in both B1 and E1. When I enter a data in A2, same data appears in both B2 and E2, So on and on. So, entry in A1=B1=E1, A2=B2=E2, AZ=B26=E26 and I only need to enter A1, A2 ... AZ and don't have to retype the entries for all other redundant cells.

3) Similar to 1 and 2, only that when I enter a data in A1, it will appear also in E1. But when I enter a data in C1, it will appear in E1 also. Whichever data got entered in A1 or C1 first got prority to appear in E1 (as A1 and C1 will not be entered at the same time. If one is filled the other will be blank). So, either A1 or C1 will have an entry and this entry will appear in E1 as well. Same for A2, C2, E2, and so on and on.

Thanks.
  • #2
  • Edited 4:39am Jun 8, 2007 4:26am | Edited 4:39am
Just copy the row as many time as is necessary.

Just use =if(a1<>0,a1,c1) in Cell E1

And if you want only one entry in E1 a choice of a1 or b1 or c1, depending on which one has data in it, just nest the if statement like this.

=if(a1<>0,a1,if(b1<>0,b1,c1)) this fromula goes in cell e1
Attached File(s)
File Type: zip Entry Sheet.zip   1 KB | 477 downloads
  • #3
  • Edited 5:14pm Jun 10, 2007 9:59am | Edited 5:14pm
Quoting smjones
Just copy the row as many time as is necessary.

Just use =if(a1<>0,a1,c1) in Cell E1

And if you want only one entry in E1 a choice of a1 or b1 or c1, depending on which one has data in it, just nest the if statement like this.

=if(a1<>0,a1,if(b1<>0,b1,c1)) this fromula goes in cell e1
HI smjones,

Thanks for your reply.Appreciate it. I opened your attached excel sheet. I experienced 2 problems.

1st problem is that your suggestion, =if(a1<>0,a1,c1) in Cell E1, I interpret it as follows: if a number is entered in A1 which is bigger or smaller than 0, then same data also appears in cells C1 and E1. The problem is that when I enter a data in cell A1 in your provided Excel sheet, the same data appears in cells B1, D1 and E1.
(so if A1 = 10, then B1 = 10, D1 = 10, E1 = 10). Nothing appears in cell c1, the intented location as appears in the equation. And somehow the data appears in D1, which I don't want it to be there.(equation has nothing to do with D1 and so now sure why a data appears in D1 in the end result) Not sure if I interpret your equation wrongly. If it's the case please point it out.


The 2nd problem is that I wanted a general equation for the whole sheet (to at least up to 50 rows or more). The equation that you provided, for E1 I have to entered =if(a1<>0,a1,c1) in Cell E1, and for E2 I have to enter "=if(a2<>0,a2,c2)" and so on and on for up to E26, for example. So, I have to enter an equation for cell E1, for cell E2, E3 and on and on, up to 100 if this is what I needed (1 equation for each row). This seems to be more work, not my original intention.

I mean, can I form a general equation that applies for all rows such that if(ai<>0, ai, ci) in cell Ei, where letter i can be 1 or 2 or 3 ....10 etc. So, when I entered a data in A1, it will also appear in C1 and E1, and if I enter in A7, it will also appear in C7 and E7 but with a general equation like that for ALL ROWS (the letter "i" where i =1, or 2, or 3...100 etc). So, I don't have to enter a seperate equation for each cell, E1, E2 ...E7 because the 1 general equation if (ai<>0, ai, ci) = Ei is solves the problem and it should take car of ALL ROWS. (if you get the idea it's easy, it's only a simple function but I don't know how to do it in excel, but it's same idea to "if (ai<>0, ai, ci) = Ei" where i represents row 1, or 2 or 3 .....up to your desired row.

Haven't tried your second equation yet as I want to have the 1st one sorted out first.

If you know please provide your information. Thanks.
  • #4
  • Jun 10, 2007 11:24am Jun 10, 2007 11:24am
Quoting forex778
HI smjones,

Thanks for your reply.Appreciate it. I opened your attached excel sheet. I experienced 2 problems.

1st problem is that your suggestion, =if(a1<>0,a1,c1) in Cell E1, I interpret it as follows: if a number is entered in A1 which is bigger or smaller than 0, then same data also appears in cells C1 and E1. The problem is that when I enter a data in cell A1 in your provided Excel sheet, the same data appears in cells B1, D1 and E1.
(so if A1 = 10, then B1 = 10, D1 = 10, E1 = 10). Nothing appears in cell c1, the intented location as appears in the equation. And somehow the data appears in D1, which I don't want it to be there.(equation has nothing to do with D1 and so now sure why a data appears in D1 in the end result) Not sure if I interpret your equation wrongly. If it's the case please point it out.


The 2nd problem is that I wanted a general equation for the whole sheet (to at least up to 50 rows or more). The equation that you provided, for E1 I have to entered =if(a1<>0,a1,c1) in Cell E1, and for E2 I have to enter "=if(a2<>0,a2,c2)" and so on and on for up to E26, for example. So, I have to enter an equation for cell E1, for cell E2, E3 and on and on, up to 100 if this is what I needed (1 equation for each row). This seems to be more work, not my original intention.

I mean, can I form a general equation that applies for all rows such that if(ai<>0, ai, ci) in cell Ei, where letter i can be 1 or 2 or 3 ....10 etc. So, when I entered a data in A1, it will also appear in C1 and E1, and if I enter in A7, it will also appear in C7 and C7 but with a general equation like that for ALL ROWS (the letter "i" where i =1, or 2, or 3...100 etc). So, I don't have to enter a seperate equation for each cell, E1, E2 ...E7 because the 1 general equation if (ai<>0, ai, ci) = Ei is solves the problem and it should take car of ALL ROWS. (if you get the idea it's easy, it's only a simple function but I don't know how to do it in excel, but it's same idea to "if (ai<>0, ai, ci) = Ei" where i represents row 1, or 2 or 3 .....up to your desired row.

Haven't tried your second equation yet as I want to have the 1st one sorted out first.

If you know please provide your information. Thanks.
Well first off, <> does not mean greater than or less than, in excel. It means Not equal. like the math operator |= ot the C++ operator != and the empty set is represneted in excel by a 0 (zero). So, not eaual to 0 is the same as the empty set. which is no entry in that cell.

Also for it to copy to b1 and c1 you must have placed the fromula in those cells as well. The if formula only gets placed in cell e1, and no other cell on that row.

the second part of you statement is now different than your first explanation of what you were trying to do. I will have to think about what you are asking here and get back to you.
  • #5
  • Jun 10, 2007 3:20pm Jun 10, 2007 3:20pm
Quoting smjones
Well first off, <> does not mean greater than or less than, in excel. It means Not equal. like the math operator |= ot the C++ operator != and the empty set is represneted in excel by a 0 (zero). So, not eaual to 0 is the same as the empty set. which is no entry in that cell.

Also for it to copy to b1 and c1 you must have placed the fromula in those cells as well. The if formula only gets placed in cell e1, and no other cell on that row.

the second part of you statement is now different than your first explanation of what you were trying to do. I will have to think about what you are asking here and get back to you.
FYI <>0 does not mean an empty cell it means not equal to 0. While it still may achieve the desired result the proper way to identify an empty cell is <>""
Stubbornly persistent
  • #6
  • Jun 10, 2007 3:28pm Jun 10, 2007 3:28pm
Quoting sergiu
FYI <>0 does not mean an empty cell it means not equal to 0. While it still may achieve the desired result the proper way to identify an empty cell is <>""
Thanks for the info.
  • #7
  • Jun 10, 2007 3:41pm Jun 10, 2007 3:41pm
First off in excel you can not create a single general formula that would automatically work in all the necessary cells of a sheet. For the formulas to work they need to be in a cell. Luckily you do not have to manually enter all of them. You create your formula in the first cell and then by "dragging" it down you populate the rest of the cells.
Here is a spreadsheet that does what you want except for your third request. In regard to your first request: if you enter your value in C1 first it will appear in E1. But then if you go and also enter a value in A1 afterworlds that value will override the original C1 value. But since i am assuming that those 2 values will be the same and it is only a matter of precedence it should work fine. The spreadsheet has no values in it only the formulas. Enter your values in either Ax or Cx and let me know if it is what you want.
Attached File(s)
File Type: zip Test.zip   7 KB | 433 downloads
Stubbornly persistent
  • #8
  • Edited 6:12pm Jun 10, 2007 5:58pm | Edited 6:12pm
Quoting sergiu
Luckily you do not have to manually enter all of them. You create your formula in the first cell and then by "dragging" it down you populate the rest of the cells.
Hi Smjones and Seigu,

Thanks for your help. Seigu, yes, from what I see in your sheet, that's what I wanted, and the fuctions work the way I like them to. Thanks. Actually I know what I wanted are some pretty easy functions, only that I don't know how they should be formulated (the format) in Excel and now I got the basic idea and can move onto to similar functions pretty easily.

The only question I have left is about the "dragging" part you referred to that apply the equation to all the rows. For example, for the equation =IF(A1<>"",A1,""), when I applied to cell E1, it works for E1, same for E2 such that E2= A2, E3=E4...etc etc if I enter the equation individually for each cell. When trying to use your suggestd method (dragging), say, I want E2=A2, E3=A3, up to E50 = A50, I first started from cell E2, and then drag (highlight) the cursor down to E50, and so now for column E, row 2 to 50 is highlighted. I then entered the equation =IF (A1<>"", A1,"") in the"fx" space in the menu bar (the space that the equation should be entered in), supposedly I think it should work the way that you referred to, but I found that the function still only applied to cell E2, the first cell of the highlighted column (and not all the way from E2 to E50 though all these cells are highlighted when I entered the function).

I tried to do it in a different way, like to first enter the function in cell E2, and then highlighted cells E2 to E50 afterwards, click on the function space (the equation is already entered in that space, and then I press "enter" again hoping to have it work for all cells and it didn't work either.

If I first highlighted a single cell E2, the text "E2" appears in a space that appeared in top left hand corner of the menu bar. If I drag and move the cursor now down to E50 and hold the left mouse cursor, it appeared as 49R X 1C (49 rows and 1 column), but when I released the left mouse button, now E2 to E50 are highlighted, but the space in the menu bar returned from "49R X 1C" to "E2" only (so only 1 cell), and not sure if this is why the function do not apply for all cells from E2 - E50 (but the sheet showed that cell E2 to E50 are highlighted)

I knew I should be doing something a little bit different here for it to work,(may be something wrong with my dragging sequence?) and if you read the above steps please point out which steps went wrong as the way I dragged it doesn't make the function applies to all the rows.

Thanks.
  • #9
  • Jun 10, 2007 7:06pm Jun 10, 2007 7:06pm
If you highlight cells B1, D1 or E1 in Seigu's spreadsheet above you'll notice a little black box in the bottom right hand corner of the highlighted cell. Left click on this box and hold it, then drag the mouse down the required number of cells and let go. Now if you look in the calls that you've "dragged" the first cell over, you'll notice they all have the formula from the top cell with updated cell references according to the row they are in.
  • #10
  • Jun 10, 2007 7:22pm Jun 10, 2007 7:22pm
Quoting Blackinc
If you highlight cells B1, D1 or E1 in Seigu's spreadsheet above you'll notice a little black box in the bottom right hand corner of the highlighted cell. Left click on this box and hold it, then drag the mouse down the required number of cells and let go. Now if you look in the calls that you've "dragged" the first cell over, you'll notice they all have the formula from the top cell with updated cell references according to the row they are in.
Exactly. If you have a formula in one cell drag that cell down to populate the rest of the cells. I already populated 100 cells in the attached sheet. If you do that you will notice that the formula automatically changes for the correct rows and columns. FYI if you wanted to prevent that (in case you wanted to use a constant value to do some calc.) you would use a $ sign: $A$1. Hope i did not confuse you with this little bit of info. The best way to learn excel is to use it an experiment. It is a valuable tool for a trader IMO.
Good Luck!
Stubbornly persistent
  • #11
  • Jun 10, 2007 7:34pm Jun 10, 2007 7:34pm
Quoting sergiu
The best way to learn excel is to use it an experiment. It is a valuable tool for a trader IMO.
Good Luck!
100% Agree, its such a powerful program, with such a wide variety of applications.

In addition to the above method, you can also copy and paste cell formula's from cell to cell/cells. Whatever you find is quickest for you.

As Sergiu mentioned above, absolute & relative referencing of cells can be beneficial too, i use them in a couple of my trading spreadsheets. Googling this will quickly improve your knowledge
  • #12
  • Edited 10:12pm Jun 10, 2007 9:22pm | Edited 10:12pm
GOT IT. Now I am ready to go and build up my database. Thanks to all who has helped here. Just one last little thing and it's done. For formatting cells, I noticed that for number, it has 4 choices for displaying negative numbers, either -10.2, (10.2), 10.2, (10.2), but I am getting used to -10.2. (a negative sign for a negative number, plus the color is red) but it has NO SUCH OPTION for me to choose in the formatting. There are are even no such 4 choices for "percentage". I want the percentage data to be displayed as -5% whenever I enter a negative % and black for a positive percentage.

The other problem is that if I select the cell format as number or percentage, other than having the negative value to be displayed like above, for positive number, I want to display it as +10.2, + 5% (the "+" sign is also shown), but unfortunately I found that when the format is in number or percentage it won't have an option to display the plus sign. (I can do this if the format is "text" but not when it's "number" or percentage)

Is there a way for me to overide or solve the above 2 problems for ALL CELLS? I looked in the help menu but couldn't quite find the solutions.

Thanks.
  • #13
  • Jun 10, 2007 10:10pm Jun 10, 2007 10:10pm
Quoting forex778
Is there a way for me to overide or solve the above 2 problems for ALL CELLS? I looked in the help menu but couldn't quite find the solutions.

Thanks.
This is a little more advanced

Short answer: Yes it can be done, i just did it.

Long answer: In order to do this you need to highlight the cell Format -> Cells -> Number Tab. Go down and highlight custom, and scroll down the bottom of the list, where you'll find what looks like a load of gibberish. Its here that you need to create your custom format for the cells, once you've done it once you can apply it to whatever cell you want.
A good way to start is by working out what the logic is. If you put in a negative number what result do you get, if you put in a positive, what do you get, and then looking over the "gibberish" and working out the logic behind it.

Hope this helps
  • #14
  • Last Post: Jun 11, 2007 11:56am Jun 11, 2007 11:56am
Quoting forex778
GOT IT. Now I am ready to go and build up my database. Thanks to all who has helped here. Just one last little thing and it's done. For formatting cells, I noticed that for number, it has 4 choices for displaying negative numbers, either -10.2, (10.2), 10.2, (10.2), but I am getting used to -10.2. (a negative sign for a negative number, plus the color is red) but it has NO SUCH OPTION for me to choose in the formatting. There are are even no such 4 choices for "percentage". I want the percentage data to be displayed as -5% whenever I enter a negative % and black for a positive percentage.

The other problem is that if I select the cell format as number or percentage, other than having the negative value to be displayed like above, for positive number, I want to display it as +10.2, + 5% (the "+" sign is also shown), but unfortunately I found that when the format is in number or percentage it won't have an option to display the plus sign. (I can do this if the format is "text" but not when it's "number" or percentage)

Is there a way for me to overide or solve the above 2 problems for ALL CELLS? I looked in the help menu but couldn't quite find the solutions.

Thanks.
1.Highlight the cell (or all the cells) for which you would want this kind of formatting.
2.Right-Click ->Format Cells
3. Under the Number tab go to Custom
4.In the box underneath "Type" copy and paste this:
Inserted Code
+#,##0.00_);[Red]-#,##0.00

The numbers will be of double type (ie 56.98) To have them as integers use this:
Inserted Code
+#,##0_);[Red]-#,##0
If you analyze the difference between the 2 you should know what to do to have more/less numbers after " . "
HTH
Stubbornly persistent
0 traders viewing now