Array formula to count maximum values only | General Excel Questions & Answers

Array formula to count maximum values only

Mark Meyer

Member

Members

Power Query

Power Pivot

Forum Posts: 5

Member Since:
April 25, 2015

Offline

February 11, 2017 - 2:26 pm

Hi,

This is my first post requesting help - I hope my question is clear.

I would like to count subjects whose maximum adverse event (AE) severity is either Grade 1, 2, or 3 based on data in the attached sheet. The source data is from a 3-column data table with column headers: Subject, AE, Grade. The second 2-row Grade table counts of all subjects with nausea of Grades 1, 2, or 3 severity in Row 1.

Importantly, for the second row in the table, I'd like to insert a function that calculates the numbers that are currently 'hard-coded' into the yellow-highlighted cells - probably an array formula. The numbers entered represent the counts of subjects with maximum AE severities of Grades 1, 2, 3, or 4. For example, if a patient has 3 episodes of nausea of Grades 1, 2, and 3 severity, I want to count only the maximum severity for that patient, ie, Grade 3.

Also included in the attached sheet is a small table showing the maximum AE (nausea) severity for each patient.

Thank you in advance for help with this function.

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

February 11, 2017 - 3:03 pm

Hi Mark

There is no attachment.

Mark Meyer

Member

Members

Power Query

Power Pivot

Forum Posts: 5

Member Since:
April 25, 2015

Offline

February 11, 2017 - 9:15 pm

Oops, see if I got it added this time.

Thanks!

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 227

Member Since:
April 21, 2015

Offline

February 11, 2017 - 10:18 pm

Still no attachment Mark. Sometimes this works not logical or properly. Last time here it was best to drag and drop the file in the given area and then click on 'upload' (or something similar in the screen). (and not to browse for the file!). After that you can see 100% uploaded or something like that. After that click on 'Submit Reply'. Hope this helps, you're not the only one having troubles with uploading files.....

Mark Meyer

Member

Members

Power Query

Power Pivot

Forum Posts: 5

Member Since:
April 25, 2015

Offline

February 12, 2017 - 7:10 am

Thanks for the tips Frans.

I've attempted to attach the file using both the drag and drop method and also the browse/open method. Both times it appeared to load. Now with this third attempt to again drag and drop, I'm (re)testing Einstein's definition of insanity. Call me crazy, but here goes....

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 227

Member Since:
April 21, 2015

Offline

February 12, 2017 - 8:03 am

Proven: you're not insane! I see an attachment 🙂

We'll be back on the issue this is all about. Thanks for the file!

Frans Visser

Duivendrecht (near Amsterdam) - The Netherlands

Member

Members

Dashboards

Power BI

Power Query

Forum Posts: 227

Member Since:
April 21, 2015

Offline

February 13, 2017 - 3:10 am

Sorry Mark but I don't understand your question even though you explained it expanded. Maybe somebody else does understand it?

The file is working fine and you want something in the yellow cells in stead of the formula you have now. But the outcome isn't clear to me.

Mark Meyer

Member

Members

Power Query

Power Pivot

Forum Posts: 5

Member Since:
April 25, 2015

Offline

February 13, 2017 - 11:15 am

Hi Frans, I appreciate your sticking with this!

I reworked the attached file and hope that my request is clearer now.

Essentially, I want to achieve with a single step and 1 formula the counts that currently 2 steps to achieve.

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

February 13, 2017 - 4:31 pm

Hi Mark

Instead of a single formula, you may want to consider using a Pivot Table to achieve step 1 of extracting the maximum grade for each unique item (refer attachment).

Using formulas to extract the maximum grade for each subject is going to be laborious as you will first need to extract the unique subjects before you can use the MAXIF array formula. I believe this is where you are encountering difficulty.

Hope this helps.

Sunny

canapone

Firenze - Italia

Member

Members

Forum Posts: 15

Member Since:
July 3, 2016

Offline

February 13, 2017 - 6:53 pm

Hi All

using an helper colum in D2 to be copied down in D2:D19

=(COUNTIF($A$2:A2;A2)=1)*MAX(INDEX(($A$2:$A$19=A2)*$C$2:$C$19,0))

In N16 ( I'm working on your second file) to be copied to N16:Q16

=COUNTIF($D2:$D19,N15)

Hope it could be clear

Regards

Mark Meyer

Member

Members

Power Query

Power Pivot

Forum Posts: 5

Member Since:
April 25, 2015

Offline

February 13, 2017 - 9:47 pm

Hi SunnyKow and Canapone,

Thanks to both of you for the excellent solutions. I had tried the pivot table solution first but for some reason it wasn't yielding the maximum grades - now that I see that if should have, I'll go back and figure out why mine didn't.

And your helper column approach is excellent, canapone. It works beautifully - I'm studying the formula now to understand what it's doing.

I appreciate the help guys!

canapone

Firenze - Italia

Member

Members

Forum Posts: 15

Member Since:
July 3, 2016

Offline

February 14, 2017 - 4:34 pm

Hi,

thanks for sharing kind feedback.