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.
Hi Mark
There is no attachment.
Oops, see if I got it added this time.
Thanks!
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.....
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....
Proven: you're not insane! I see an attachment 🙂
We'll be back on the issue this is all about. Thanks for the file!
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.
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.
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
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
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!
Hi,
thanks for sharing kind feedback.
Regards
