Formula to perform calculation using parameter associated with most recent date?

sanktjohan

I'm trying to generate a column formula that will calculate 'Dose' using 'Concentration' and 'Purity' for each row, but only using the 'Weight' associated with the most recent 'Collection Date' grouped by 'Subject ID'.

For the example below, the Dose for Subject A (Test #2) should be:

Dose (A, Test #2) = (1.3 * 0.95)/(26)

I have tried using the MAX() and COLLECT() functions to pull the overall most recent Weight for a Subject ID, but am having difficulties with syntax and integrating it into the Dose formula. Bonus points if you can help generate a formula for Total Dose by summing the (Concentration * Purity) values for each row and dividing by most recent Weight by Subject ID. Appreciate the help!

Paul Newcome

Try this:

=(Concentration@row * Purity@row) / INDEX(COLLECT(Weight:Weight, [Subject ID]:[Subject ID], @cell = [Subject ID]@row), COUNTIFS([Subject ID]:[Subject ID], @cell = [Subject ID]@row))

sanktjohan

https://community.smartsheet.com/discussion/comment/442591#Comment_442591

This gives me an '#INCORRECT ARGUMENT' error. Is there a reason you use INDEX() instead of MAX() if I need the Weight from the most recent Collection Date? I've also tried using MAX() on Test Number instead of date so I could get a helper column with a non-Date data type but not having any luck. Thanks for the help!

Paul Newcome

You will need an INDEX function unless you can guarantee that the most recent weight will be greater than or equal to all previous entries. The above is under the assumption that the most recent will always be the last one on the list for that particular Subject ID.

If you need something more variable, you would need a helper date type column with this in it:

=MAX(COLLECT([Collection Date]:[Collection Date], [Subject ID]:[Subject ID], @cell = [Subject ID]@row))

Then to run your actual calculation, you would still need an INDEX/COLLECT, but it would look more like this:

=(Concentration@row * Purity@row) / INDEX(COLLECT(Weight:Weight, [Subject ID]:[Subject ID], @cell = [Subject ID]@row, [Collection Date]:[Collection Date], @cell = [Helper Column]@row), 1)