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!

 

[rushthecourt]

Can anyone assist with this problem? Much appreciated!

RTC

 

[Domenic]

To count the number of X's for the last 8 entries, try...

=COUNTIF(INDEX(D4:D5000,LARGE(IF(K4:K5000<>"",ROW(K4:K5000)-ROW(K4)+1),8)):D5000,"X")

...confirmed with CONTROL+SHIFT+ENTER. However, this will include in the count situations where the corresponding value in Column K is blank. To exclude these from the count try the following instead...

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

...confirmed with CONTROL+SHIFT+ENTER. To average the last 8 entries in Column K where the corresponding value in Column D is X, try...

=AVERAGE(IF(ROW(K4:K5000)>=LARGE(IF(K4:K5000<>"",ROW(K4:K5000)),8),IF(D4:D5000="X",IF(K4:K5000<>"",K4:K5000))))

...confirmed with CONTROL+SHIFT+ENTER. Unfortunately, I don't have time to look at your other questions right now. If no one else replies, I'll try to get to them either later today or tomorrow morning.

Hope this helps!

 

[rushthecourt]

Ok, here's an example of what I'm trying to do - consider the following example below:

C D E K
1.5 X AA -3.5
2.7 Y BB -18.5
-3.3 X BB -9.5
-10.5 Y AA 30.5
0.5 X CC -11.0
4 X AA -3.0
6.8 Y BB -14.0
-3.6 Z CC -6.5
1.5 X AA -3.5
2.7 Y AA -18.5
-3.3 X BB -9.5
-10.5 Y CC 30.5
0.5 X BB -11.0
4 X BB -3.0
6.8 Y BB -14.0
-3.6 Z AA -6.5



So the calculations I want are the following:

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

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

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"

Hope that's clearer. Thanks!

RTC

 

[ExcelChampion]

Did you try Domenic's suggestion? What was the outcome?

 

[rushthecourt]

Something isn't calculating right using those formulas. When I do them manually the average isn't right.

 

[Domenic]

Something isn't calculating right using those formulas. When I do them manually the average isn't right.

That's because the requirements according to your last post differ from your original ones. And, unfortunately, your last post still leaves some uncertainty. For example, you mentioned in your original post that Column K can contain blanks. So when you're looking for the last 8 X entries are you looking for the last 8 X entries where the corresponding value in Column K is not blank or are you looking for the last 8 X entries regardless of whether the corresponding value in Column K is blank?

 

[rushthecourt]

Something isn't calculating right using those formulas. When I do them manually the average isn't right.

That's because the requirements according to your last post differ from your original ones. And, unfortunately, your last post still leaves some uncertainty. For example, you mentioned in your original post that Column K can contain blanks. So when you're looking for the last 8 X entries are you looking for the last 8 X entries where the corresponding value in Column K is not blank or are you looking for the last 8 X entries regardless of whether the corresponding value in Column K is blank?

I'm sorry... it's gotten so complex that I'm confusing even myself! Thanks for your patience.

To answer the question, column D (containing X, Y or Z) is the only column in my poorly formatted example above that will NEVER have blanks. Put more directly, columns C, E and K will occasionally have blank entries.

So what I want is the last 8 X entries where the corresponding value in column K is NOT BLANK.

Thanks again!

RTC

 

[Domenic]

Do these formulas return the desired results?

1)

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

To return 3-4-1, try...

=SUM(IF(ROW(D4:D5000)>=LARGE(IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000))),8),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(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(D4:D5000="X",IF(E4:E5000="CC",IF(K4:K5000<>"",1)))))

2)

=AVERAGE(IF(ROW(D4:D5000)>=LARGE(IF(D4:D5000="X",IF(K4:K5000<>"",ROW(D4:D5000))),8),IF(D4:D5000="X",IF(K4:K5000<>"",K4:K5000))))

3)

=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))))))

To return 3-2-1, 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(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))))))

Note that these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[rushthecourt]

Hmm... not quite getting there yet...

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. 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.


Feel like we're close, but something minor isn't working... thanks!

RTC