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 |
#11
|
|||
|
|||
Formula?
Google Documents (search for it in Google!) allows you to upload your s/s and
then sned or display a link so that you or others don't have to give away email addresses. Off to work now. I'll pick up later. Who'd be a deckchair attendant in England in winter! Cheers -- Russell Dawson Excel Student "omocron" wrote: 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 |
#12
|
|||
|
|||
Formula?
I'll try to read up on Google docs and see if I can do that.....if
not...you'll have to walk me thru.....It's not a lack of desire to have you learn from my little project...just a lack of knowlege about the way to do it........keep warm! I think you are about 6 or 7 hours ahead of me....I'm CST..in Louisiana. I'll check you tomorrow afternoon, my time. -- Omocron "Russell Dawson" wrote: Google Documents (search for it in Google!) allows you to upload your s/s and then sned or display a link so that you or others don't have to give away email addresses. Off to work now. I'll pick up later. Who'd be a deckchair attendant in England in winter! Cheers -- Russell Dawson Excel Student "omocron" wrote: 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 |
#13
|
|||
|
|||
Formula?
"Joe User" joeu2004 wrote:
Can you send your Excel file to me? He did, and I helped him as much as I am willing to do. All I did was apply the suggestions of JLatham's and mine exactly as we had instructed. This is m.p.e.newusers, but I draw the line at holding the OP's hand and pressing each keystroke. Based on our exchange of email, it sounds like "Omocron" is looking for continued assistance. If anyone is interested, please step up. ----- original message ----- "Joe User" joeu2004 wrote in message ... "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 |
#14
|
|||
|
|||
Formula?
You can reach me via email at (remove spaces)
Help From @JLatham Site.com Send me file as attachment and we'll see what we can do. Just remind me that you are 'omocron from the New User's forum' in the email so I can refresh my memory about what is happening/needed. As far as protocol goes here; anyone that can help is welcome to try. That's what it's all about. I certainly don't get my feelings hurt if someone else 'takes over' if they can help, and no sense in someone having to wait hours while someone else gets back to a discussion, as happened to me in this case. Only thing that most people like to see, which you've adhered to, is to keep the discussion in a single thread. Splitting it up, cross-posting and such as that only adds to the confusion for everyone involved. "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 |
#15
|
|||
|
|||
Formula?
I also assumed the symmetry, but didn't catch the lack of it until a little
later (in my second posting). Thanks for reducing the formula, I figured it could be, but was kind of in a rush to get something back to him that worked. Then it also dawned on me that the sequence might be Today Yesterday instead of the Yesterday Today sequence I assumed when I did the first formula. I actually developed each piece of it as a separate formula and then started 'blending/merging' them to come up with the final one, and that lends to the complication of each piece of it. "Joe User" wrote: "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 . |
#16
|
|||
|
|||
Formula?
Joe,
Would you mind sending me the file you sent to him so I can look it over? I'd ask him to, but wouldn't want to invade your privacy (i.e., having your email addy) without your consent. If you don't mind, send it to (no spaces of course) Help From @JLatham Site. com Thanks - I'll do a little hand holding for a while. "Joe User" wrote: "Joe User" joeu2004 wrote: Can you send your Excel file to me? He did, and I helped him as much as I am willing to do. All I did was apply the suggestions of JLatham's and mine exactly as we had instructed. This is m.p.e.newusers, but I draw the line at holding the OP's hand and pressing each keystroke. Based on our exchange of email, it sounds like "Omocron" is looking for continued assistance. If anyone is interested, please step up. ----- original message ----- "Joe User" joeu2004 wrote in message ... "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 . |
#17
|
|||
|
|||
Formula?
Joe,
Just a follow-up. I was able to fix him up off line, your condensed formula was major help. The formula we ended up using for row 2 was: =IF(E2=E3,IF(F2=F3,1,2),IF(F2=F3,4,3)) and with a little VBA, a new row 2 with that formula set up along with 5 & 9 day averages are provided for him at the click of a button on the sheet. Much to his credit, he has gone out and obtained a copy of Excel 2003 for Dummies (the one by Walkenbach), and that should be a help to him. I think one thing he hadn't discovered at the point of this thread was just how much Help Excel's help can actually be in assisting in understanding the syntax of worksheet functions. "Joe User" wrote: "Joe User" joeu2004 wrote: Can you send your Excel file to me? He did, and I helped him as much as I am willing to do. All I did was apply the suggestions of JLatham's and mine exactly as we had instructed. This is m.p.e.newusers, but I draw the line at holding the OP's hand and pressing each keystroke. Based on our exchange of email, it sounds like "Omocron" is looking for continued assistance. If anyone is interested, please step up. ----- original message ----- "Joe User" joeu2004 wrote in message ... "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 | |
|
|