Excel formula

[bcr123]

Hello all.

If someone could please help me with the following formula.
I'm using MS Excel, Office 2003.

Formula is in Q23:

If M22 is smaller than S22, than have value of D23, otherwise have empty cell.

(This part now I know how to make.)

However, if M22 is smaller than S22, than have value of D23 and keep on doing this (down Q column) for as long as it takes for value in M to become bigger than value in L, despite M may become bigger than S. Once value in M becomes bigger than value in L (after M dropping below S first) have empty cells until condition is met again.

All in all – I am trying to identify (in Q column) what are the values in D column when value (in M) drops more than X standard deviations (which is in S) from the mean until it recovers above mean (which is in L).

Help would be truly appreciated.

Thank you.

[NeoPa]

Let's start with the basics:
A cell can only be empty if it has no formula in it. A formula can return a 0 or an empty string, but empty cells cannot result from a formula.

That said, I will assume you are after cells which look empty. If not, then let me know & I'll go through a technique I use for producing empty cells dependent on a formula.

=IF($M22<$S22,$ D23,"")

At this stage I have to stop as your explanation of the problem starts to tie itself in knots. What you say cannot be right as it contradicts itself. Please try to define what you want in a consistent manner. If you want to explain this on a per line basis, that's fine. If so, then do it all that way.

I wouldn't be surprised if this is why you haven't managed to solve it yourself. A solution always starts with a clearly defined & understood problem.

[bcr123]

Hello NeoPa,

Thank you for your time.

=IF($M22<$S22,$ D23,"") is part that I knew how to sort out and empty string is what I had in mind, but lacked proper terminology.

When it comes to full explanation:

I am trying to present in Q column what are the values in D column when value (in M) drops more than X standard deviations (which is defined in S) from the mean until it recovers above mean (which is in L).

So, if we are for first 99 cells below mean (L) that is not of importance to me, but if we drop (M) in cell 100 below S, I would like to have presented in column Q (from cell 101, as drop happened in cell 100) values of column D, for as long as it takes M to turn bigger than S and L.

Basically, I am trying to say to Excel:

Give in column Q corresponding values of column D only during periods when M is smaller than S or L, but only after M falls below S (for at least 1 event). When M recovers above L, wait for it to fall below S again and than again give me in column Q corresponding values of column D until M recovers above L again.


Thank you, once again, for your time and consideration.

[NeoPa]

Give in column Q corresponding values of column D only during periods when M is smaller than S or L, but only after M falls below S (for at least 1 event). When M recovers above L, wait for it to fall below S again and than again give me in column Q corresponding values of column D until M recovers above L again.

As I cannot imagine M ever being smaller than S or L (I assume you mean smaller than either of S or L) at any point before it falls below S, I find this question still hard to pinpoint. From your description elsewhere, I'm not sure what I can assume about your data. You seem to be implying that column L is a repetition of the same (mean) value in each row, yet as you don't state that, I can only assume it couldn't be the case. After all, who would miss out such an important part of the question if it were the case. I'm still fundamentally unsure of what you're trying to do.

I expect it's in there somewhere, but unfortunately as it's still a little self-contradictory, I cannot tell which bits I can rely on and which are the red-herrings. I do appreciate how hard people can find this, so I'll have a stab at an answer anyway. Just bear in mind that without a proper understanding of the issue the answer may be entirely inappropriate.

=IF(AND($M22<$S22,$M22<$L22),$D23,"")

[bcr123]

Thank you for the effort NeoPa and I am sorry for the confusion.

Red line = S column
Black line = L column
Blue line = M column

I am sorry for posting graph, but as I am extremely visual I’ve decided to try to explain myself using a bit of graphics.

I would like to say to Excel to do this in every cell of Q column:

If Blue line (M) dropped (for first time ever) below Red line (S) return value that is D column.
If it has not dropped below – return empty string.

After Blue Line drops (for first time ever) below Red line (S) keep on giving corresponding values from D column (i.e. D123 value to be displayed in Q123, etc.) for as long as it takes for Blue line (M) to get above Black Line (L).

After this event (M>L) keep on returning empty strings for as long it takes to get to regime M<S again (second time ever) and then keep on giving corresponding values from D column (i.e. D123 value to be displayed in Q123, etc.) for as long as it takes for Blue line (M) to get above Black Line (L).

“First time ever” is marked with yellow dot on graph, while “Second time ever” is marked with purple dot.

Your time is truly appreciated.

Thank you.

[bcr123]

Hello.

Just a follow-up, as result of some help from another forum:

I’ve introduced another column U with:

=IF(M30<S30,1,I F(M30>L30,0,U29 ))

with Q in same row

=IF(U30=0,"",D3 0)

Thank you NeoPa for your time and kind consideration.

[NeoPa]

Column U can be helpful, but only in as far as it simplifies the formula in Q.

Can I first get you to confirm that referring to cells in both rows 22 & 23 in your 1st post was simply a typo. I cannot match this logic with wat you've explained most recently, and this makes for a shifting sand scenario that's hard to work with.

Visual examples are all well and good, but they have the disadvantage of displaying an example rather than the fundamental logic. Certainly the explanation is further forward, but I still see parts of the text explaining that only the first time it crosses a line is important, as well as other parts which imply that this needs to be repeated when the line is crossed again. These cannot both be true. Put another way, I can do either scenario, but not both.

Your example data has M going below S before it goes above L. What if the data doesn't come in this order? A formula would need to handle the data definitively, not as a series of examples.

It can be helpful to explain both from an overall perspective as well as from a line-by-line one. Unfortunately, in this case it is so critical that they match exactly. Unfortunately they don't in this case. It may be only a minor oversight from your end, but I have no way of knowing which bits to rely on in these circumstances.

Lastly, this is doable I suspect. Nothing I've seen is too much for Excel to handle, though depending on exactly what is required the formula may involve some conditional sums of the preceeding data in Q (which can be a little fiddly). Nothing beyond us though. It really is just a matter of getting the specification clarified (and progress has truly been made on that score. It's just a little rough edges that still need trimming) and we should be good to go. Please don't think I'm just trying to put off the effort of resolving this for you. It's actually a lot more time and effort to explain all this clearly, than just doing the formula when it's ready.

[bcr123]

Hello NeoPa.

If, as an example, M drops below S in row 100, I will need Q101 = D101, as from that cell I have new regime, because cell 100 was the one that triggered the event of new regime and observation starts from row 101. Hence, I shall have in U101 the following:

=IF(M100<S100,1 ,IF(M100>L100,0 ,U100))

and in Q101:

=IF(U101=0,"",D 101)

If M100 is less than S100 than return 1
elseif
If M100 is greater than L100 return 0
else
return value from U100

(My first set of data for U column is in row 23, while U22 has manually entered 0 as value. This means that 0 is going to be returned until, in this example, row 101 where I am going to get 1 that is going to be returned for as long as M doesn’t become greater than L again and that’s when 0 is going to be returned again, until M doesn’t become less than S again.)

This, as it is, is working well with my problem and I’ve visually inspected graph, just to make sure proper periods are recorded and all is well.

As I was working with some friends on another forum on this, I’ve learned that this is Nested IF function and all of this would be much easier if I had knowledge of Nested IF.

I am really sorry if the way I communicate is not clear – From the very beginning I was trying to explain that I was interested to display in column Q corresponding values from column D only during period(s) during which M has dropped below S until it recovered above L. All other events would trigger empty string.

I appreciate your time and seniority on this forum, especially because you are very helpful to numerous users of bytes.com (including myself).

Thank you NeoPa.

[NeoPa]

I appreciate that you have always made every effort to be as clear as you can. The problem is that describing logic is actually very difficult to do well (regardless of appearances) and very few can describe your situation very clearly without falling over the issues you have.

I have another problem atm. I get blocked out from being able to download pages from Bytes for long periods for some reason. That is why (as well as general busy-ness elsewhere) I haven't replied as quickly as otherwise I might have. Never mind. I'm about to leave for another office, so I cannot get into this too deeply just now, but let me see if I can help with the nested IF idea :

1. Excel formulas are just that. The IF() function returns one of two values depending on the result of the first parameter.
2. Each of those values may be a simple value, or it could itself be the result of a function call.

3. =IF(A>2, IF(A<5, B, C), D)

   In this example the result for the values of A would be :
   1. A=1 D
   2. A=2 D
   3. A=3 B
   4. A=4 B
   5. A=5 C
4. Another very useful pair of functions are AND() and OR().
   

   =IF(AND(M23<S23,M23<L23),"",D23)

   If M23 is less than the smaller of S23 & L23 then empty string, otherwise D23.

When I get a chance later I'll see if I can put all you say together to make a consistent set of logic and suggest a formula. BTW Is what you have already with the extra column working for you? You seem to feel it is, yet it doesn't match my understanding of what you're saying. Knowing this would help me put the pieces together correctly.

[bcr123]

Thank you for your time and kind consideration.

What I have is working well – double and triple-checked (manually) on graph and in Excel’s sheet (row-by-row checked) and I am exactly getting in column Q corresponding values from column D, during regime when M falls below S until it recovers above L, as that is only regime that is of interest to me in this exercise, while for all other regimes I am getting empty string.

Thank you, once again, for your time.

[NeoPa]

Ah. That seems like a pretty top solution. From that, I can go over all you said and see that it did mainly make sense (albeit a little hard for me to follow).

I do like the solution though. Really quite neat.

[bcr123]

Thank you very much NeoPa.