If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula?
First, let me apologise for my lack of Excel knowlege. I have tried to teach
myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
#2
|
|||
|
|||
Formula?
Is the latest (current) date going to be at the top of the list, or at the
bottom of the list? I wrote this formula assuming that the latest date would be at the BOTTOM of the list: =IF(AND(E3=E2,F3=F2),3,IF(AND(E3=E2,F3F2),4,IF (AND(E3E2,F3F2),2,IF(AND(E3E2,F3F2),1,0)))) Assuming that the top row (latest date) is at row 2, that formula would go into cell H3, and you can fill it down from there. Having the latest date at the top makes it difficult to put a 5 day running total on top row (as in my example, H2 would be empty), but in I2 you could put a formula such as =AVERAGE(H3:H7) to give you a 5-day average for the 5 days prior to the current date. "omocron" wrote: First, let me apologise for my lack of Excel knowlege. I have tried to teach myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
#3
|
|||
|
|||
Formula?
JLatham, THANKS...that was fast. I'm not where I can get my head around this
right now, however later this evening I'll try my best. Now, for a couple of quick and necessary questions....(by the way, the latest date will be at the top, I'll be importing data, and some of it will be lengthy). I put the formula at the top of column H...correct? I mean not in the cells but in the column entitled "H"...?? I'm really green on this.....but thanks and I may be on my way to getting this done at last. -- Omocron "JLatham" wrote: Is the latest (current) date going to be at the top of the list, or at the bottom of the list? I wrote this formula assuming that the latest date would be at the BOTTOM of the list: =IF(AND(E3=E2,F3=F2),3,IF(AND(E3=E2,F3F2),4,IF (AND(E3E2,F3F2),2,IF(AND(E3E2,F3F2),1,0)))) Assuming that the top row (latest date) is at row 2, that formula would go into cell H3, and you can fill it down from there. Having the latest date at the top makes it difficult to put a 5 day running total on top row (as in my example, H2 would be empty), but in I2 you could put a formula such as =AVERAGE(H3:H7) to give you a 5-day average for the 5 days prior to the current date. "omocron" wrote: First, let me apologise for my lack of Excel knowlege. I have tried to teach myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
#4
|
|||
|
|||
Formula?
Second try. This time assuming that the latest/current date is on top of the
list (at row 2). Also correcting what I think are a couple of minor errors in your description of things: First test, 2nd half you said to compare F to E, but I think you meant to compare E to E and F to F. This formula does that. In the 3rd part you said "the value of column F is less than the value of column F from the day before". I think you meant "..is less than OR EQUAL to the value..." and again, this formula makes that assumption. NOTE that my previous formula did not make this assumption. Put this formula in H2: =IF(AND(E2=E3,F2=F3),3,IF(AND(E2=E3,F2F3),4,IF (AND(E2E3,F2=F3),2,IF(AND(E2E3,F2F3),1,0)))) remember that is all in a continuous entry/line. You can fill that down the sheet as far as you need to. Again for the 5 day average, in I2 you can put =AVERAGE(H2:H6) and fill that down to the 5th row from the bottom of the entries in column H. Hope this helps. "omocron" wrote: First, let me apologise for my lack of Excel knowlege. I have tried to teach myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
#5
|
|||
|
|||
Formula?
See my 2nd post - it assumes latest/current date at the top, and also makes a
couple of other assumptions about the comparisons to be made. The 2nd post tells where to put the formula initially (into cell H2). I'll check back later this evening to see how you're doing. "omocron" wrote: JLatham, THANKS...that was fast. I'm not where I can get my head around this right now, however later this evening I'll try my best. Now, for a couple of quick and necessary questions....(by the way, the latest date will be at the top, I'll be importing data, and some of it will be lengthy). I put the formula at the top of column H...correct? I mean not in the cells but in the column entitled "H"...?? I'm really green on this.....but thanks and I may be on my way to getting this done at last. -- Omocron "JLatham" wrote: Is the latest (current) date going to be at the top of the list, or at the bottom of the list? I wrote this formula assuming that the latest date would be at the BOTTOM of the list: =IF(AND(E3=E2,F3=F2),3,IF(AND(E3=E2,F3F2),4,IF (AND(E3E2,F3F2),2,IF(AND(E3E2,F3F2),1,0)))) Assuming that the top row (latest date) is at row 2, that formula would go into cell H3, and you can fill it down from there. Having the latest date at the top makes it difficult to put a 5 day running total on top row (as in my example, H2 would be empty), but in I2 you could put a formula such as =AVERAGE(H3:H7) to give you a 5-day average for the 5 days prior to the current date. "omocron" wrote: First, let me apologise for my lack of Excel knowlege. I have tried to teach myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
#6
|
|||
|
|||
Formula?
"JLatham" wrote:
=IF(AND(E3=E2,F3=F2),3,IF(AND(E3=E2,F3F2),4,IF (AND(E3E2,F3F2),2,IF(AND(E3E2,F3F2),1,0)))) You might consider the following form: =IF(E3=E2, IF(F3=F2,3,4),IF(F3F2,2,IF(F3F2,1,0))) The last term, IF(F3F2,1,0), could be replaced with --(F3F2). But that takes advantage of the special case where the desired result is 1 or 0. PS: But in reading the OP's English description, I see that the OP did not really the cover the case of E3E2 and F3=F2. You assumed that the OP simply omitted the case. Not a bad assumption. But I would have assumed symmetry in the requirements, and "less than" should be "equal to or less than" in the 3rd case, as it is in the 1st case. If the OP agrees, the suggested formula can be simplified further, to wit: =IF(E3=E2, IF(F3=F2,3,4),IF(F3=F2,2,1)) ----- original message ----- "JLatham" wrote in message ... Is the latest (current) date going to be at the top of the list, or at the bottom of the list? I wrote this formula assuming that the latest date would be at the BOTTOM of the list: =IF(AND(E3=E2,F3=F2),3,IF(AND(E3=E2,F3F2),4,IF (AND(E3E2,F3F2),2,IF(AND(E3E2,F3F2),1,0)))) Assuming that the top row (latest date) is at row 2, that formula would go into cell H3, and you can fill it down from there. Having the latest date at the top makes it difficult to put a 5 day running total on top row (as in my example, H2 would be empty), but in I2 you could put a formula such as =AVERAGE(H3:H7) to give you a 5-day average for the 5 days prior to the current date. "omocron" wrote: First, let me apologise for my lack of Excel knowlege. I have tried to teach myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
#7
|
|||
|
|||
Formula?
JLatham, Let me just ask bluntly, Is there a way or email address where I
could go into further detail about this. I frankly don't mind inviting Joe User into the conversation as well, however I don't know protocol or what is acceptable here in this forum. Without sounding too mysterious, I would like to use a close scenario to what I am trying to accomplish. I think this would be beneficial to me and would also help you understand what I am trying to make these figures do. If you don't care to send a way to contact you, I understand. No offense taken. Same for you Joe User. I plugged in the formula into a trial database and it doesn't appear to be working. Could be a prob on my part, but it could also be in the formula as I don't think the column is updating each day. It appears that when a new value is placed in the H column, it changes all info in that column to the same value. This should be a new value each day and then as the data is brought in each day, it should remain static for each day and the new data value be placed at the top of the column under THAT days row. (H) The top row should always be Today's Data and tomorrow it should move to the second line and Data from tomorrow becomes the top row. I'm not trying to make this complicated however I'm not wanting to put ALL of my business out on the forum either. So......I'll wait to hear from you .....and Joe User and see if there is a way I can further go into detail about this. By the way....Column H is a preset value.....think of it like this....E and F are two differing amounts gained from the days data. If E is higher the value assigned to H will be either 1 or 2 depending what F did. If E is lower, the value assigned to H will be either 3 or 4 depending on what F did (higher or lower). The values for H are assigned depending on what E and F in combination have done. (higher,higher....higher,lower......lower, higher....and lower, lower. Those are the only values that will be assigned. 1,2,3, or 4...in that order. And then, what I want to do is keep a running average (simple) in column' I ' so I can see what the last 5 days value average is. If need be, let me know what time you will be here so I can open my profile and let you see my email...(briefly)....so we might be able to communicate under less glaring light...LOL If not, I'll try my best to do as you have recommended and see if this can come to pass. Thanks...much appreciated. -- Omocron "JLatham" wrote: Second try. This time assuming that the latest/current date is on top of the list (at row 2). Also correcting what I think are a couple of minor errors in your description of things: First test, 2nd half you said to compare F to E, but I think you meant to compare E to E and F to F. This formula does that. In the 3rd part you said "the value of column F is less than the value of column F from the day before". I think you meant "..is less than OR EQUAL to the value..." and again, this formula makes that assumption. NOTE that my previous formula did not make this assumption. Put this formula in H2: =IF(AND(E2=E3,F2=F3),3,IF(AND(E2=E3,F2F3),4,IF (AND(E2E3,F2=F3),2,IF(AND(E2E3,F2F3),1,0)))) remember that is all in a continuous entry/line. You can fill that down the sheet as far as you need to. Again for the 5 day average, in I2 you can put =AVERAGE(H2:H6) and fill that down to the 5th row from the bottom of the entries in column H. Hope this helps. "omocron" wrote: First, let me apologise for my lack of Excel knowlege. I have tried to teach myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
#8
|
|||
|
|||
Formula?
"omocron" wrote:
If need be, let me know what time you will be here so I can open my profile and let you see my email...(briefly). Not clear on how that would work. Can you send your Excel file to me? It needs to be Excel 2003 or earlier (or XL2007 saved in compatibility mode). And it should not depend on data in external files. If you're amenable to that, send the Excel file to joeu2004 "at" hotmail.com. The Subject line should refer to this thread. And it might be help to include some explanation or pointers in the body of the email. ----- original message ----- "omocron" wrote: JLatham, Let me just ask bluntly, Is there a way or email address where I could go into further detail about this. I frankly don't mind inviting Joe User into the conversation as well, however I don't know protocol or what is acceptable here in this forum. Without sounding too mysterious, I would like to use a close scenario to what I am trying to accomplish. I think this would be beneficial to me and would also help you understand what I am trying to make these figures do. If you don't care to send a way to contact you, I understand. No offense taken. Same for you Joe User. I plugged in the formula into a trial database and it doesn't appear to be working. Could be a prob on my part, but it could also be in the formula as I don't think the column is updating each day. It appears that when a new value is placed in the H column, it changes all info in that column to the same value. This should be a new value each day and then as the data is brought in each day, it should remain static for each day and the new data value be placed at the top of the column under THAT days row. (H) The top row should always be Today's Data and tomorrow it should move to the second line and Data from tomorrow becomes the top row. I'm not trying to make this complicated however I'm not wanting to put ALL of my business out on the forum either. So......I'll wait to hear from you ....and Joe User and see if there is a way I can further go into detail about this. By the way....Column H is a preset value.....think of it like this....E and F are two differing amounts gained from the days data. If E is higher the value assigned to H will be either 1 or 2 depending what F did. If E is lower, the value assigned to H will be either 3 or 4 depending on what F did (higher or lower). The values for H are assigned depending on what E and F in combination have done. (higher,higher....higher,lower......lower, higher....and lower, lower. Those are the only values that will be assigned. 1,2,3, or 4...in that order. And then, what I want to do is keep a running average (simple) in column' I ' so I can see what the last 5 days value average is. If need be, let me know what time you will be here so I can open my profile and let you see my email...(briefly)....so we might be able to communicate under less glaring light...LOL If not, I'll try my best to do as you have recommended and see if this can come to pass. Thanks...much appreciated. -- Omocron "JLatham" wrote: Second try. This time assuming that the latest/current date is on top of the list (at row 2). Also correcting what I think are a couple of minor errors in your description of things: First test, 2nd half you said to compare F to E, but I think you meant to compare E to E and F to F. This formula does that. In the 3rd part you said "the value of column F is less than the value of column F from the day before". I think you meant "..is less than OR EQUAL to the value..." and again, this formula makes that assumption. NOTE that my previous formula did not make this assumption. Put this formula in H2: =IF(AND(E2=E3,F2=F3),3,IF(AND(E2=E3,F2F3),4,IF (AND(E2E3,F2=F3),2,IF(AND(E2E3,F2F3),1,0)))) remember that is all in a continuous entry/line. You can fill that down the sheet as far as you need to. Again for the 5 day average, in I2 you can put =AVERAGE(H2:H6) and fill that down to the 5th row from the bottom of the entries in column H. Hope this helps. "omocron" wrote: First, let me apologise for my lack of Excel knowlege. I have tried to teach myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
#9
|
|||
|
|||
Formula?
I assume that you have sorted this between yourselves now. Couldn't do it
myself anyway but would it have been possible to download the file to Google Docs and give access via the link provided without compromising security? Omocron - there are some of us, and I think you would include yourself, that are learning and benefit from reading discussions such as this. Please don't underestimate yourself. You explained the problem very well compared to some others we have seen. I try to offer solutions myself whenever possible but I'm learning and enjoying it every day. -- Russell Dawson Excel Student "Joe User" wrote: "omocron" wrote: If need be, let me know what time you will be here so I can open my profile and let you see my email...(briefly). Not clear on how that would work. Can you send your Excel file to me? It needs to be Excel 2003 or earlier (or XL2007 saved in compatibility mode). And it should not depend on data in external files. If you're amenable to that, send the Excel file to joeu2004 "at" hotmail.com. The Subject line should refer to this thread. And it might be help to include some explanation or pointers in the body of the email. ----- original message ----- "omocron" wrote: JLatham, Let me just ask bluntly, Is there a way or email address where I could go into further detail about this. I frankly don't mind inviting Joe User into the conversation as well, however I don't know protocol or what is acceptable here in this forum. Without sounding too mysterious, I would like to use a close scenario to what I am trying to accomplish. I think this would be beneficial to me and would also help you understand what I am trying to make these figures do. If you don't care to send a way to contact you, I understand. No offense taken. Same for you Joe User. I plugged in the formula into a trial database and it doesn't appear to be working. Could be a prob on my part, but it could also be in the formula as I don't think the column is updating each day. It appears that when a new value is placed in the H column, it changes all info in that column to the same value. This should be a new value each day and then as the data is brought in each day, it should remain static for each day and the new data value be placed at the top of the column under THAT days row. (H) The top row should always be Today's Data and tomorrow it should move to the second line and Data from tomorrow becomes the top row. I'm not trying to make this complicated however I'm not wanting to put ALL of my business out on the forum either. So......I'll wait to hear from you ....and Joe User and see if there is a way I can further go into detail about this. By the way....Column H is a preset value.....think of it like this....E and F are two differing amounts gained from the days data. If E is higher the value assigned to H will be either 1 or 2 depending what F did. If E is lower, the value assigned to H will be either 3 or 4 depending on what F did (higher or lower). The values for H are assigned depending on what E and F in combination have done. (higher,higher....higher,lower......lower, higher....and lower, lower. Those are the only values that will be assigned. 1,2,3, or 4...in that order. And then, what I want to do is keep a running average (simple) in column' I ' so I can see what the last 5 days value average is. If need be, let me know what time you will be here so I can open my profile and let you see my email...(briefly)....so we might be able to communicate under less glaring light...LOL If not, I'll try my best to do as you have recommended and see if this can come to pass. Thanks...much appreciated. -- Omocron "JLatham" wrote: Second try. This time assuming that the latest/current date is on top of the list (at row 2). Also correcting what I think are a couple of minor errors in your description of things: First test, 2nd half you said to compare F to E, but I think you meant to compare E to E and F to F. This formula does that. In the 3rd part you said "the value of column F is less than the value of column F from the day before". I think you meant "..is less than OR EQUAL to the value..." and again, this formula makes that assumption. NOTE that my previous formula did not make this assumption. Put this formula in H2: =IF(AND(E2=E3,F2=F3),3,IF(AND(E2=E3,F2F3),4,IF (AND(E2E3,F2=F3),2,IF(AND(E2E3,F2F3),1,0)))) remember that is all in a continuous entry/line. You can fill that down the sheet as far as you need to. Again for the 5 day average, in I2 you can put =AVERAGE(H2:H6) and fill that down to the 5th row from the bottom of the entries in column H. Hope this helps. "omocron" wrote: First, let me apologise for my lack of Excel knowlege. I have tried to teach myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
#10
|
|||
|
|||
Formula?
Russell, You have a point and I agree that this would have been an excellent
solution to having the problem addressed. IF ONLY I knew anything about Google docs and knew how to download the file and do what you recommended. I'm just learning how to open and setup an Excel database. Really, I'm not even sure where to put the formula's that the guys were talking about....in row 2? or...column H....is that H1, H2 or under the column label H itself? I really wanted to move to email discussion so as not to look too dumb and so I could be spoon fed and ask those type questions that really make me feel about half an inch tall...LOL. I'm still waiting for a reply from Joe User and haven't heard back from JLatham yet at all.....give me an email and I'll put you in the loop if you think there is anything here to learn.....just promise me not to laugh too hard when you discover how really green I am when it comes to Excel. I know just enough to be dangerous. -- Omocron "Russell Dawson" wrote: I assume that you have sorted this between yourselves now. Couldn't do it myself anyway but would it have been possible to download the file to Google Docs and give access via the link provided without compromising security? Omocron - there are some of us, and I think you would include yourself, that are learning and benefit from reading discussions such as this. Please don't underestimate yourself. You explained the problem very well compared to some others we have seen. I try to offer solutions myself whenever possible but I'm learning and enjoying it every day. -- Russell Dawson Excel Student "Joe User" wrote: "omocron" wrote: If need be, let me know what time you will be here so I can open my profile and let you see my email...(briefly). Not clear on how that would work. Can you send your Excel file to me? It needs to be Excel 2003 or earlier (or XL2007 saved in compatibility mode). And it should not depend on data in external files. If you're amenable to that, send the Excel file to joeu2004 "at" hotmail.com. The Subject line should refer to this thread. And it might be help to include some explanation or pointers in the body of the email. ----- original message ----- "omocron" wrote: JLatham, Let me just ask bluntly, Is there a way or email address where I could go into further detail about this. I frankly don't mind inviting Joe User into the conversation as well, however I don't know protocol or what is acceptable here in this forum. Without sounding too mysterious, I would like to use a close scenario to what I am trying to accomplish. I think this would be beneficial to me and would also help you understand what I am trying to make these figures do. If you don't care to send a way to contact you, I understand. No offense taken. Same for you Joe User. I plugged in the formula into a trial database and it doesn't appear to be working. Could be a prob on my part, but it could also be in the formula as I don't think the column is updating each day. It appears that when a new value is placed in the H column, it changes all info in that column to the same value. This should be a new value each day and then as the data is brought in each day, it should remain static for each day and the new data value be placed at the top of the column under THAT days row. (H) The top row should always be Today's Data and tomorrow it should move to the second line and Data from tomorrow becomes the top row. I'm not trying to make this complicated however I'm not wanting to put ALL of my business out on the forum either. So......I'll wait to hear from you ....and Joe User and see if there is a way I can further go into detail about this. By the way....Column H is a preset value.....think of it like this....E and F are two differing amounts gained from the days data. If E is higher the value assigned to H will be either 1 or 2 depending what F did. If E is lower, the value assigned to H will be either 3 or 4 depending on what F did (higher or lower). The values for H are assigned depending on what E and F in combination have done. (higher,higher....higher,lower......lower, higher....and lower, lower. Those are the only values that will be assigned. 1,2,3, or 4...in that order. And then, what I want to do is keep a running average (simple) in column' I ' so I can see what the last 5 days value average is. If need be, let me know what time you will be here so I can open my profile and let you see my email...(briefly)....so we might be able to communicate under less glaring light...LOL If not, I'll try my best to do as you have recommended and see if this can come to pass. Thanks...much appreciated. -- Omocron "JLatham" wrote: Second try. This time assuming that the latest/current date is on top of the list (at row 2). Also correcting what I think are a couple of minor errors in your description of things: First test, 2nd half you said to compare F to E, but I think you meant to compare E to E and F to F. This formula does that. In the 3rd part you said "the value of column F is less than the value of column F from the day before". I think you meant "..is less than OR EQUAL to the value..." and again, this formula makes that assumption. NOTE that my previous formula did not make this assumption. Put this formula in H2: =IF(AND(E2=E3,F2=F3),3,IF(AND(E2=E3,F2F3),4,IF (AND(E2E3,F2=F3),2,IF(AND(E2E3,F2F3),1,0)))) remember that is all in a continuous entry/line. You can fill that down the sheet as far as you need to. Again for the 5 day average, in I2 you can put =AVERAGE(H2:H6) and fill that down to the 5th row from the bottom of the entries in column H. Hope this helps. "omocron" wrote: First, let me apologise for my lack of Excel knowlege. I have tried to teach myself to create this formula on my Excel 2002, however....I give. I humbly ask for someone's help. Column A will be the current date...then there will be 6 columns of data. I need to have a formula which will do the following.... If the value of column E is equal to or less than column E's value from the day before, and the value of Column F is equal to or less than Column E's value from the day before, assign a value of 3 to Column H. If the value of column E is equal to or less than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 4 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is less than the value of Column F from the day before, assign a value of 2 to Column H. If the value of column E is greater than column E's value from the day before (the row under), and the value of Column F is greater than the value of Column F from the day before, assign a value of 1 to Column H. Once these are in place I will want Column H to run a 5 day running simple average in Column I. There will be more later, however these are the most pressing needs I have at the current time and rather than spend another month trying to teach myself how to do this, I feel my efforts can be better used in other areas if I can find the help to have these formula's put into Excel 2002. Any assistance would be greatly appreciated. I have used Excel as a Spreadsheet, however never as a database (and I'm no expert at using it as a spreadsheet by a long way) Thanks in advance, and remember I'm pretty green so take nothing for granted. -- Omocron |
|
Thread Tools | |
Display Modes | |
|
|