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]

Then we can remove the condition for Column E...

=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(K4:K5000<>"",K4:K5000)))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!