Formula?

[omocron]

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.

 

[JLatham]

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,F3>F2),4,IF(AND(E3>E2,F3<F2),2,IF(AND(E3>E2,F3>F2),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]

JLatham, THANKS...that was fast. I'm not where I can get my head around this
right now, however later this evening I'll try my best. Now, for a couple of
quick and necessary questions....(by the way, the latest date will be at the
top, I'll be importing data, and some of it will be lengthy). I put the
formula at the top of column H...correct? I mean not in the cells but in the
column entitled "H"...??
I'm really green on this.....but thanks and I may be on my way to getting
this done at last.

 

[JLatham]

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,F2>F3),4,IF(AND(E2>E3,F2<=F3),2,IF(AND(E2>E3,F2>F3),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.

 

[JLatham]

See my 2nd post - it assumes latest/current date at the top, and also makes a
couple of other assumptions about the comparisons to be made.

The 2nd post tells where to put the formula initially (into cell H2). I'll
check back later this evening to see how you're doing.

 

[Joe User]

=IF(AND(E3<=E2,F3<=F2),3,IF(AND(E3<=E2,F3>F2),4,IF(AND(E3>E2,F3<F2),2,IF(AND(E3>E2,F3>F2),1,0))))

You might consider the following form:

=IF(E3<=E2, IF(F3<=F2,3,4),IF(F3<F2,2,IF(F3>F2,1,0)))

The last term, IF(F3>F2,1,0), could be replaced with --(F3>F2). 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 E3>E2 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 -----

 

[omocron]

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.

 

[Joe User]

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]

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]

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.

 

[Joe User]

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 -----

 

[JLatham]

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.

 

[JLatham]

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.

 

[JLatham]

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.

 

[JLatham]

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.