Active Member
Dashboards
April 25, 2015
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.
VIP
Trusted Members
June 25, 2016
VIP
April 21, 2015
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.....
Active Member
Dashboards
April 25, 2015
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....
VIP
April 21, 2015
VIP
April 21, 2015
VIP
Trusted Members
June 25, 2016
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
Active Member
Dashboards
April 25, 2015
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!
1 Guest(s)