A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula?



 
 
Thread Tools Display Modes
  #11  
Old February 12th, 2010, 06:58 AM posted to microsoft.public.excel.newusers
Russell Dawson
external usenet poster
 
Posts: 49
Default 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  
Old February 12th, 2010, 07:11 AM posted to microsoft.public.excel.newusers
omocron
external usenet poster
 
Posts: 5
Default 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  
Old February 12th, 2010, 09:26 AM posted to microsoft.public.excel.newusers
Joe User[_2_]
external usenet poster
 
Posts: 757
Default 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  
Old February 12th, 2010, 01:59 PM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 1,896
Default 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  
Old February 12th, 2010, 02:05 PM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 1,896
Default 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  
Old February 13th, 2010, 01:39 AM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 1,896
Default 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  
Old February 18th, 2010, 04:57 AM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 1,896
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.