Conditional Entries

rushthecourt

New Member
Joined
Jul 8, 2007
Messages
35
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top