Conditional Entries

[rushthecourt]

Hi, I'm trying to evaluate the last 8 entries of a particular column of data that I regularly update (K4:K5000). This data is filled with numbers ranging from about -50 to 50 and will occassionally have a blank entry.

I'd like to evaluate these last 8 entries using a couple of different conditions.

#1 - In column D4:D5000, I have three entry values - X, Y, or Z. It will never be blank. I would like for the Excel to automatically review the last 8 entries of columns K and D and do two things...

a. provide an output of a count of how many X, Y and Z there are for the last 8 entries (format: 4-3-1)

b. provide an output of an average of the values of K for each of X, Y, and Z... (i.e., avg of X = 4.4; avg of Y = 2.1; avg of Z = 10.7)

#2 - Next, I would like to add a third column to the mix (Column C4:C5000 with entries >0 and <0) so that the last 8 entries of columns C and D are the variables considered...

a. provide an output of a count of how many X>0, Y>0, Z>0 there are for the last 8 entries (format: 4-3-1)

b. provide an output of an average of the values of K for each of X>0, Y>0, and Z>0 (i.e., avg of X>0 = 9.4, etc.)


Can anyone help here? Thanks!

 

[Domenic]

First question - For #1 and #3, there are two equations. Are these two equations supposed to relate back somehow? A little confused about that part.

Sorry, I should have been clearer. The first formula will only return the count for "AA". I provided that one so that you could see what the formula would be for "AA" only. Since you're looking for 3-4-1, I provided the second formula for you to use instead of the first one.

I'm getting "0" in both cases.

For the second part of #1 and #3, I'm getting 0-0-0 for each also.


For #2, it's giving me a #DIV/0! error.

Do the values in Column E match all three criteria (AA, BB, and CC) exactly? Or do they have extra spaces?

 

[rushthecourt]

Thanks for the clarification.

Nope, there are no extra spaces in Column E - the values match all three criteria exactly, and I'm still getting 0-0-0. Thoughts?


RTC

 

[Domenic]

Let's make sure we're on the same wavelength. This is my understanding...

1. Last 8 X entries (using E): 3 (aa) - 4 (bb) - 1 (cc) - Cell should read "3-4-1"

For the last 8 entries where Column D equals X and the corresponding value in Column K is not blank, count the number of times the corresponding value in Column E equals AA. Same thing for BB and CC.

2. Avg. of Last 8 X entries (using K): -54/8 = -6.75 - cell should read "-6.75"

For the last 8 entries where Column D equals X and the corresponding value in Column K is not blank, average the corresponding values in Column K.

3. Last 8 X entries (using C>0 & E): in this case there are only 6 instances where x (c>0) , therefore, X = 3 (aa) - 2 (bb) - 1 (cc) - cell should read "3-2-1"

For the last 8 entries where Column D equals X and the corresponding value in Column K is not blank, count the number of times the corresponding value in Column C is greater than 0 and the corresponding value in Column E equals AA. Same thing for BB and CC.

Is my understanding correct? If so, the formulas I offered should return the desired result. If you'd like I can email you a sample file. If so, you can send me your email address via Private Message. However, if my understanding is not correct, please clarify.

 

[rushthecourt]

Yes, your understanding is correct on all three counts. I'm not sure what the problem is. I will try to piddle around with it here for a little longer.

If you'd like to email me a sample file, I'd appreciate it. You can do so at:

rushthecourtATyahooDOTcom


Thanks again.

RTC

 

[Domenic]

Sample file sent...

 

[rushthecourt]

Domenic - thanks!

I was able to get them all to work, but I forgot that there was another formula that I was trying to get also. I tried to work it myself but my understanding is just so rudimentary at this point....

4. For the last 8 entries where Column D equals X and the corresponding value in Column K is not blank, count the number of times the corresponding value in Column C is greater than 0. Same thing for less than zero.

5. For the last 8 entries where Column D equals X and the corresponding value in Column K is not blank, count the number of times Column E equals AA column. Same thing for BB and CC.


Thanks again!

RTC

 

[Domenic]

Domenic - thanks!

You're very welcome!

4. For the last 8 entries where Column D equals X and the corresponding value in Column K is not blank, count the number of times the corresponding value in Column C is greater than 0.

=SUM(IF(ROW(D4:D5000)>=LARGE(IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000))),8),IF(C4:C5000>0,IF(D4:D5000="X",IF(K4:K5000<>"",1)))))

Same thing for less than zero.

=SUM(IF(ROW(D4:D5000)>=LARGE(IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000))),8),IF(C4:C5000<0,IF(D4:D5000="X",IF(K4:K5000<>"",1)))))

To combine them and return the result in the format 6-2, based on your sample data, try...

=SUM(IF(ROW(D4:D5000)>=LARGE(IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000))),8),IF(C4:C5000>0,IF(D4:D5000="X",IF(K4:K5000<>"",1)))))&"-"&SUM(IF(ROW(D4:D5000)>=LARGE(IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000))),8),IF(C4:C5000<0,IF(D4:D5000="X",IF(K4:K5000<>"",1)))))

5. For the last 8 entries where Column D equals X and the corresponding value in Column K is not blank, count the number of times Column E equals AA column. Same thing for BB and CC.

This one, I believe, you already have...

 

[rushthecourt]

Ok, here's where I'm still stuck...

=SUM(IF(ROW(D4:D5000)>=LARGE(IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000))),8),IF(C4:C5000>0,IF(D4:D5000="X",IF(E4:E5000="AA",IF(K4:K5000<>"",1))))))&"-"&SUM(IF(ROW(D4:D5000)>=LARGE(IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000))),8),IF(C4:C5000>0,IF(D4:D5000="X",IF(E4:E5000="BB",IF(K4:K5000<>"",1))))))&"-"&SUM(IF(ROW(D4:D5000)>=LARGE(IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000))),8),IF(C4:C5000>0,IF(D4:D5000="X",IF(E4:E5000="CC",IF(K4:K5000<>"",1))))))


The formula above is capturing the last 8 times where X exists in column D, then evaluating the # of times column C>0. In the sample you sent, that occurred 5 times in the last 8 X entries. Then it provided a breakdown of AA, BB and CC, which equalled 2-2-1.


What I actually want it to do is to capture the last 8 times where X exists in column D AND C>0 in Column C. Therefore, I should always get a result of 8 entries evaluating AA, BB, and CC. (ex: 4-3-1) The only time I would not get 8 entries is if there simply weren't 8 entries that fit the criteria of column D and C.


Thanks again!

RTC

 

[Domenic]

What I actually want it to do is to capture the last 8 times where X exists in column D AND C>0 in Column C. Therefore, I should always get a result of 8 entries evaluating AA, BB, and CC. (ex: 4-3-1) The only time I would not get 8 entries is if there simply weren't 8 entries that fit the criteria of column D and C.

In that case, try...

=SUM(IF(ROW(D4:D5000)>=LARGE(IF(C4:C5000>0,IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000)))),8),IF(C4:C5000>0,IF(D4:D5000="X",IF(E4:E5000="AA",IF(K4:K5000<>"",1))))))&"-"&SUM(IF(ROW(D4:D5000)>=LARGE(IF(C4:C5000>0,IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000)))),8),IF(C4:C5000>0,IF(D4:D5000="X",IF(E4:E5000="BB",IF(K4:K5000<>"",1))))))&"-"&SUM(IF(ROW(D4:D5000)>=LARGE(IF(C4:C5000>0,IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000)))),8),IF(C4:C5000>0,IF(D4:D5000="X",IF(E4:E5000="CC",IF(K4:K5000<>"",1))))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[rushthecourt]

Domenic:

Sorry, but I'm getting a #NUM! error when I try that last formula.

Could we try sending another sample? I really do appreciate all your help but I don't want to overburden you.

Thanks.

RTC