Subtracting min from max from a certain week number

Grace Tidwell

Hello,

I am trying to calculate the number of vaccine doses used in a week based on a daily on-hand inventory. I have numbered the rows with WEEKNUMBER.

So far, I've tried this: =SUM(MAX([# of vaccine doses on hand today (#s only)]1:[# of vaccine doses on hand today (#s only)]69, [Week Number]@row, [Week Number]1:[Week Number]69)- (MIN(# of vaccine doses on hand today (#s only)]2:[# of vaccine doses on hand today (#s only)]69, [Week Number]@row, [Week Number]2:[Week Number]69)))

But I am clearly missing something. Any tips? I currently have summary data in the top row.

Kelly Moore

Hey @Grace Tidwell

Does this work for you?

=SUM(MAX(COLLECT([# of vaccine doses on hand today (#s only)]1:[# of vaccine doses on hand today (#s only)]69, [Week Number]1:[Week Number]69, [Week Number]@row))- (MIN(COLLECT(# of vaccine doses on hand today (#s only)]2:[# of vaccine doses on hand today (#s only)]69, [Week Number]2:[Week Number]69, [Week Number]@row))))

The Collect function has the syntax (Range (of what you want collected), range1, criteria1, range2, criteria2, range3, criteria3, etc)

Kelly

Grace Tidwell

Thank you so much! I am still getting #unparseble with that, not sure what is off.

Kelly Moore

Hey Grace

Would you mind taking a screenshot of the formula so I can check parentheses colors and column names? I had checked it on my sheet and it appeared to work.

Grace Tidwell

Sure- thank you so much!

Grace Tidwell

Let me know if that is too small to see

Kelly Moore

Hey Grace

Thanks for the screenshot. The formula I proposed and the screenshot you supplied do not have the same number of ending parentheses. Add one more to your formula so that the ending parenthesis is blue. Also, I could tell that a column name was wrong because the column names are not colored. I see I forgot an opening square bracket following the MIN(COLLECT(

=SUM(MAX(COLLECT([# of vaccine doses on hand today (#s only)]1:[# of vaccine doses on hand today (#s only)]69, [Week Number]1:[Week Number]69, [Week Number]@row))- (MIN(COLLECT([# of vaccine doses on hand today (#s only)]2:[# of vaccine doses on hand today (#s only)]69, [Week Number]2:[Week Number]69, [Week Number]@row)))