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]

Sample file sent...

 

[rushthecourt]

Domenic -

Did you send to the rushthcourtATyahooDOTcom address? If so, I haven't received anything yet. Thanks.

RTC

 

[Domenic]

Yes, I sent it to rushthecourtATyahooDOTcom. Would you like me to try sending it again?

 

[rushthecourt]

No thanks - I got it later last night. Thanks again.

I am getting the equation as you sent it to work, (with C>0), but when all I do is simply switch the "greater than" sign to "less than" and keep everything else exactly the same, I'm getting the #NUM! error again. I can't figure that out. Is there a reason that would be happening with that equation that I'm missing - some change I also need to make?

Thanks.

RTC

 

[Domenic]

The reason you're getting #NUM! is probably because there are less than 8 X's where Column C is less than 0. That's certainly the case in the sample file I sent you.

 

[rushthecourt]

Ahhh, you are right, as usual.

So I looked through it and there are only 4 Xs where C<0. Is there a way to account for that as well? So that it would show 2-2-0 currently, but it would eventually get to the last 8 as the database continues to grow?


Thanks.

RTC

 

[Domenic]

Try replacing...

8

with

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

Since the formulas are becoming quite large, consider entering the three different formulas (one for AA, one BB, and the other for CC) in three separate cells and then concatenate them in another cell. For example, if A2 contained the first formula, B2 contained the second, and C2 contained the third, then D2 would have the following formula...

=A2&"-"&B2&"-"&C2

Hope this helps!

 

[rushthecourt]

Domenic:

Thanks for that tip about separating out the formulas...

It worked! I'm getting very close to the end here, but I see one more problem upcoming.

If I want to get an average of the last 8 entries where C>0 and D="X", I feel like I should know how to do this, but I'm having trouble putting the formula together. Thoughts?


RTC

 

[Domenic]

If I want to get an average of the last 8 entries where C>0 and D="X", I feel like I should know how to do this, but I'm having trouble putting the formula together. Thoughts?

Based on the conditions above, which column do you want to average, Column C or Column K? Assuming you want to average Column K, try the following for AA...

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

...confirmed with CONTROL+SHIFT+ENTER. The same thing applies for BB and CC. To average Column C instead, change the reference for Column K in red to Column C.

Hope this helps!

 

[rushthecourt]

Yes, I should have been clearer. I want to average column K.

Unfortunatley, I'm not getting the result I hoped for here.

First, I'm a little confused as to the part of the formula where EE4:EE5000 is considered. Is that part supposed to be in there?

My average should include all of AA, BB and CC with no distinction.

The only conditions are:

Last 8 entries where: 1) C>0, and 2) d="x"


Thanks!

RTC

My #s aren't adding up yet.