If Statement Overwhelm Part Ii

[RalphSE]

Hi,

I have a sinking feeling this one may be impossible but let's see.
have a ROW of data (actually about 240 rows) that I want an IF THE
function to
test for various things. There are so many tests and signals that I'
not sure if an IF THEN is the right kung fu, but I'm sure Pete o
another Excel
master will know. Here is the project:

Here are the tests and corresponding signals that I want Excel t
"say".
The values in the row of data will be referred to as "the ratio" in th
following, the data is organized from left to right by date, i.e. cel
B2's data is from January 23, cell C2's data is for January 24th
etc...

1. If the ratio falls below 25% and then rises by 10 units, i.e. fall
to 13% and rises to 23% say "25% 10 UNIT BUY"
2. If the ratio falls below 30% and then rises above 30% say "30% BUY"
3. If the ratio rises from below to above 70% say "70% BUY"
4. If the ratio rises from below to above 80% say "80% BUY"
5. If the ratio rises from below to above 90% say "90% BUY"
6. If the ratio falls from above to below 70% say "70% SELL"
7. If the ratio falls from above to below 75% say "75% SELL"
8. If the ratio falls from above to below 80% say "80% SELL"
9. If the ratio falls from above to below 85% say "85% SELL"
10. If the ratio falls from above to below 90% say "90% SELL"

I Appreciate any help that can be offered with this, THANKS!!!

Ralp

 

[Pete_UK]

Hello Ralph (exasperated sigh ...)

You keep coming back for more, I see.

Your description is a bit confusing here (better when you paste an
example) - I think you are mixing up rows and columns, but what you
seem to be saying is that you have two columns of data, one for Jan
23rd and another for Jan 24th (examples, I presume). You have some
comparison between these which is what I think you refer to as the
"ratio" (is it a strict ratio of 24th to 23rd data, or the other way
round, or is it a %age increase, and if so which way round? It may be
better to think of this ratio in another column - let's say D.

Then in E we can have some formula which will return some text - this
may be a lookup formula getting the text from a table, as you have too
many IFs for a composite IF statement.

It's time for food here, so while I'm away can you give some further
details of how you would derive the "ratio", or is the ratio already in
B and C? Will these columns contain the %ages you refer to? I'm
confused - any chance of posting an example containing your B and C
columns, so I can work directly on that?

Back soon (well, in about an hour)

Pete

 

[Pete_UK]

Have you noticed that everyone else seems to steer clear of your
postings!

Pete

 

[RalphSE]

yikes, looks like i'm wearing out my welcome, sorry Pete, maybe I'l
wait a week, let you rest up and continue with this then, LOL, but onc
I get this one done I should be scarce for quite a while, just trying t
get some things dialed in over here...:

 

[RalphSE]

will come back with an example in about a week, thanks

 

[Pete_UK]

Hey, I didn't mean to scare you off Ralph.

Post away, and I'll try to help if I can.

I found this posting a bit confusing, but having had something to eat I
think what you are trying to describe above is that you have already
compared, say, 22nd Jan raw data with 23rd Jan raw data and got the
ratios which are now in column B for 23rd Jan, and then compared 23rd
Jan with 24th Jan and these are the ratios in column C. Is this
correct?

Pete

 

[RalphSE]

oh good, dont want to wear out my welcome, your help has been priceless
to me, would hate to blow that, will be right back with an example

 

[RalphSE]

http://www.flypicture.com?display=updone&id=rtvxkqrb

thanks again Pete!!! take your time PLEASE

 

[Pete_UK]

Hi Ralph,

this seems to be what you are after, although I've only been able to
test it on your sample data. If you want these "signals" to be robust,
I suggest you test them thoroughly on some real data, and get back to
me if they don't quite do what you want.

To reduce the length of the formula, I renamed your "PASTE DATA" sheet
to "copy" and you will have to do the same. Then copy this formula into
cell D2 of the SIGNALS sheet:

=IF(copy!D2="","",IF(AND(copy!C2<copy!D2,copy!C2>0.6,copy!D2>0.7),TEXT(INT(copy!D2*10)/10,"0%
")&"BUY",IF(AND(copy!C2>copy!D2,copy!C2>0.7),TEXT(INT(copy!C2*20)/20,"0%
")&"SELL",IF(AND(copy!C2<0.3,copy!D2>0.3),"30%
BUY",IF(AND(copy!C2<0.25,copy!D2-copy!C2>=0.1),"25% 10 UNIT
BUY","")))))

Note that it must go into D2 - it won't work in C2. This is all one
formula, but you will probably get awkward line-breaks where there are
spaces once I have posted it - hope you can work it out. Just to be on
the safe side, here it is again, but this time I have put line breaks
in before every IF:

=IF(copy!D2="","",
IF(AND(copy!C2<copy!D2,copy!C2>0.6,copy!D2>0.7),TEXT(INT(copy!D2*10)/10,"0%
")&"BUY",
IF(AND(copy!C2>copy!D2,copy!C2>0.7),TEXT(INT(copy!C2*20)/20,"0%
")&"SELL",
IF(AND(copy!C2<0.3,copy!D2>0.3),"30% BUY",
IF(AND(copy!C2<0.25,copy!D2-copy!C2>=0.1),"25% 10 UNIT BUY","")))))

You can probably work out better from this what it is doing. The final
"" before the five closed brackets could be changed to "keep" or "save"
or "no action" or some-such, as you didn't specify what to return if
none of the conditions are met. Also, the message "25% 10 UNIT BUY" is
too wide to show in your column width, so you might want to re-phrase
it. For emphasis, you might want to apply some conditional formatting
to highlight the actions, but you are limited to only 3 conditions with
this (eg"* Buy" could be one colour, "* Sell" could be another, "30%
Buy" could be another, and other entries would be normal).

In your sample file the formulae can be copied across on row 2, but
normally you would copy the formula down. I would suggest that you just
do, say, 5 columns at a time for a week's worth of data, to keep your
file a meaningful size. Once you have built up some data, you could
have last week's data as active, but use copy and paste special to fix
the values of earlier weeks, and have next week's formulae ready to
take the data as you paste it in. Oh, by the way, you can rename the
sheet from "copy" to something else after you have entered the formula.

Well, I hope you can get this working, and I hope you carry on posting
to the news group - I've enjoyed our friendly banter and can't wait
till you send me a picture of your first-born named after me! (Some
years in the future, no doubt!)

Cheers for now,

Pete

 

[Pete_UK]

Ralph,

slight amendment to the formula, as follows:

=IF(copy!D2="","",
IF(AND(copy!C2<copy!D2,copy!D2>0.7,INT(copy!C2*10)
/10<INT(copy!D2*10)/10),TEXT(INT(copy!D2*10)/10,"0% ")
&"BUY",IF(AND(copy!C2>copy!D2,copy!C2>0.7,INT(copy!C2*20)
/20>INT(copy!D2*20)/20),TEXT(INT(copy!C2*20)/20,"0% ")
&"SELL",IF(AND(copy!C2<0.3,copy!D2>0.3),"30% BUY",
IF(AND(copy!C2<0.25,copy!D2-copy!C2>=0.1),"25% 10 UNIT BUY","")))))

This time I've put line breaks after closed brackets, so there
shouldn't be any awkward splits or missed spaces. This one better
reflects what you were asking for, in that it takes better account of
the 5% and 10% increments. It also gives you "95% SELL" and "100% SELL"
which you didn't ask for, but which is consistent with the other values
- it's more difficult to take this out!

Test this out and see what you think.

Goodnight.

Pete

 

[RalphSE]

Pete, THANKS again SO much for your help!!!!!

The formula works great, I can live with the 100% and 90% SELL stuff,
I'll just disregard it when I see that, everything worked perfectly on
the data I created except for one minor detail which I have a feeling
may be too complicated to program. Cell S2 should say "25% 10 UNIT
BUY" because the ratio falls below 25% at cell N2 and at S2 rises by
10% (from 2% at Q2). Let me know if this is doable, if not, I'll work
on coming up with some kind of way to find those manually, no
problemo.

Thanks my friend, I'll send you pictures if that day comes, LOL!!!

 

[Pete_UK]

Ralph,

I've just read your first condition again - I thought it had said if it
rises above 25% by at least 10% from a position below 25% (I said it
was confusing!) and this is what it models. Have a look in V2, where it
goes from 17% to 28%.

I think I can fix this by adjusting the appropriate IF condition, but
it will have to be later on tonight (must work for my living!).

Speak to you later.

Pete

 

[Pete_UK]

Actually, Ralph, I think it *does* do what you were asking for. If you
look at the last IF function:

IF(AND(copy!C2<0.25,copy!D2-copy!C2>=0.1),"25% 10 UNIT BUY","")))))

this says if c2 is less than 25% (i.e. the first value of the two to be
compared) AND there is a rise of at least 10% (by comparing the current
value with the one before it) then output "25% 10 UNIT BUY".

The comparison of the rises is only between adjacent columns of data -
in your example above you are going back several columns to find the 2%
value and the individual rises from that are all less than 10% until
you get to column V. Change the value in S2 to 7% and you will see the
output in T2 of the SIGNALS sheet.

I'm not sure about going back an unknown number of columns to find the
minimum and then using a comparison between that and the current,
because then it will always find a 10% rise eventually.

Please advise what you want me to do.

Pete

 

[RalphSE]

thanks Pete, I apologize for the confusion, your formula does work for
an adjacent 10% rise, this is the only criteria that is not based
necessarily on an adjacent rise condition, the signal only requires
that the ratio has been below 25% and that since that moment when it
drops below 25% if it ever rises 10%from the minimum level that is the
signal, kind of complicated on this one, if it cant be done, no
problemo my friend

 

[Pete_UK]

I think this is very difficult, if not impossible! Consider the
following sequence of percentages:

A B C D E F G H I J K L
26 12 3 12 5 12 18 15 18 20 24 27 etc

Then B is where it drops below 25%. At G (18%) this would trigger the
signal, but then so would H and I and J and K using your logic - is
this what you are after, or would it reset when the message is output?

How would it know when next to start counting?

Is the minimum C (3%) or E (5%) ?

Too much confusion !!

Pete

 

[RalphSE]

sounds waay to complicated Pete, lets scrap it, I'll try to come up with
some way to track it manually

but to answer your questions, yes, would want it to reset once the
message is output, in other words, wouldnt need it to fire at H or I or
J or K

if the ratio were to rise again ABOVE 25% then drop below 25% and rise
from a low after that point by 10 units, then it would fire again...

OR

if the ratio were to never get above 25% and start dropping, make a
low, then rise by 10units that would also be the signal...

bottom line - waaaaay too complicated for excel i think

didnt understand your question "Is the minimum C (3%) or E (5%) ?", but
if you are asking what is the lowest value the ratio can get, it's zero
%

youve helped me quite a bit here Pete, I can live without this one,
will track it myself, dont want to get spoiled rotten....yet

thanks

 

[Pete_UK]

What I was trying to get across was if it went down to a minimum (3%)
then rose (but not enough to trigger the message) then went down again
(but not as low as the first minimum), would it be the first (lowest)
minimum or the latest (closest to current position with higher values
either side). Maybe I'm thinking too much of waves here - is it nearly
time for holidays?

Anyway, I await your next challenge. Maybe one day you'll show me the
big picture, rather than just snippets.

Cheers,

Pete

 

[RalphSE]

no, the signal would be based on the lowest low made since the ratio
dropped below 25%, so in your example it would still be based on the
first low since the 2nd low was higher, and i'd be happy to give you
the big picture someday, but not on a public message board, LOL!

have an email?

 

[RalphSE]

by the way, in lieu of some magic formula to do this 10unit signal, can
you add to the formula one last signal that just says "<25%" whenever
the ratio is below 25%, that will help pinpoint the ones to watch, if i
may be so bold as to ask for yet another favor!!

 

[Pete_UK]

At the end of the formula you have now, you can see the bit:

"25% 10 UNIT BUY", "")))))

I've put extra spaces in because here you will need to add an extra IF
to look like:

"25% 10 UNIT BUY",IF(copy!D2<0.25,"<25%",""))))))

Note there is an extra bracket at the end as well. I think it is easier
for me to give it to you like this rather than the wole formula again.
Don't forget this goes into D2 and can then be copied across or down.
You could apply conditional format to this to bring it to your
attention more easily. I've used D2 rather than C2 so that you are
aware of the drop below 25% as soon as it happens.

Hope this is what you wanted.

BTW, click on my options then you'll be able to send email.

Pete