# Conditional Entries

#### rushthecourt

##### New Member
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### rushthecourt

##### New Member
Can anyone assist with this problem? Much appreciated!

RTC

#### Domenic

##### MrExcel MVP
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

##### New Member
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

##### Well-known Member
Did you try Domenic's suggestion? What was the outcome?

#### rushthecourt

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

#### Domenic

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

Replies
11
Views
364
Replies
7
Views
201
Replies
1
Views
550
Replies
2
Views
504
Replies
0
Views
155

### Forum statistics

1,191,273
Messages
5,985,691
Members
439,974
Latest member
sjoerdbosch ### 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.

### Which adblocker are you using?    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

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