Group IDs, calculate

macropod · #5 03-29-2017, 07:47 PM

Quote:

Originally Posted by OCM

To clarify further about the 24 unit (24hrs. or 1day): it is to be deducted from each provider whom there is a unit. In fact, all of them have a unit value.

The goal in the end is to identify providers who are over the 24 units.

In that case, I suspect you don't want -ve values, either, which is what you'd get for a provider whose unit total was, say, 20. You can set those to display as 0 via:
=MAX((SUMIF(A$2:A$20,A2,J$2:J$20)/SUMIF(A$2:A$20,A2,I$2:I$20))*(SUMIF(A$2:A$20,A2,I$ 2:I$20)-24),0)
and copied to each 'total recovery amount' row.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calculate the progress per day	pm1110	Excel	2	09-16-2015 02:34 AM
Group records - multiple IF stmts - Text After Group	baum12	Mail Merge	1	05-25-2015 12:57 AM
Group Table Rows/Column (like Excel Group)	eoinymc	Word	1	03-11-2014 04:51 AM
calculate age	userman	Excel	8	06-02-2012 10:59 PM
How to calculate percentages???	fabthi	Excel	3	04-13-2012 12:08 PM