Solved Excel formula to calculate based on IDs

Microsoft Excel 2013 32/64-bit - license...
March 29, 2017 at 16:51:17
Specs: Windows 7
Greetings,

Given the following columns:

Column A: Column I Column J Column P
Provider ID Units Amounts Total recovery amount

I wold like to enter a formula on Column P to calculate the following:

1. Group Provider IDs and sum ‘Amounts’ (column J)

2. Group Provider IDs and sum ‘Units’ (column I)

3. Sum of ‘Amounts’/Sum of ‘Units’

4. Subtract 24 from total units (step #2 above)

5. Finally, multiply (step #3) by (step # 4) above

Once I obtained totals for each provider, I would like to display the grand total for all.

Any ideas?

Also, can you please advise how I can attach a sample?

TIA,


See More: Excel formula to calculate based on IDs

Report •

✔ Best Answer
March 30, 2017 at 11:40:28
You can check for "Total Units" that are less than 24 by adding an IF function. This will eliminate negative Recovery values.

=IF(SUM(I2:I4)<24,"",(SUM(I2:I4)-24)*(SUM(J2:J4)/SUM(I2:I4)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 29, 2017 at 17:59:45
You can't attach a file in this forum, but you can upload a generic file to zippyshare.com and then post a link back here in the forum.

By "generic" I mean no personally identifiable information, confidential information, etc. Since the file will public, you should be careful about what it contains.

Steps 3, 4 and 5 I get. Steps 1 and 2 are confusing, at least to me.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 29, 2017 at 18:31:58
Thank you.

In case the link below doesn't work, below is a sample to clarify #1 & #2

Column A ……….Column I Column J
Provider ID……… Units Amounts
001 15.00 $2946.74
001 24.00 $2032.43
001 20.00 $3286.66

1. $2946.74 + $2032.43 + $3286.66 = $8,265.83
2. 15+24+20 = 59

here are links to the my sample:

http://www45.zippyshare.com/v/b3XiQ...


[url=http://www45.zippyshare.com/v/b3XiQuon/file.html][img=http://www45.zippyshare.com/thumb/b3XiQuon/file.html][/img][/url]

TIA,

message edited by ocm1


Report •

#3
March 29, 2017 at 19:42:12
You have a couple of options...

I notice that you do not have the same number of Provider Id's for each ID. i.e. 3 each 001, 2 each 002, 1 each 003, etc. That makes it hard to write a single formula that can be copied into each "Recovery" field because you are dealing with different sized ranges.

You could write an individual formula in each of your merged cells that look something like this:

(You didn't provide Row numbers, so I am assuming that your data starts is Row 2)

For ID 001: =(SUM(I2:I4)-24)*(SUM(J2:J4)/SUM(I2:I4))

That will give you the $4,903.46 that you have in your sample.

Now you need to write a similar formula for ID 002 and reference the ranges in Rows(5:6).

Then another one for ID 003 referring to only Row(7).

As you can see, you need to edit each formula to reference a specific range based on the number of ID Rows for each ID.

Another option is to create a list of Unique ID's in another column, e.g. O and then put this formula in P2 and drag it down to bottom of your unique list. (The Advanced Filter feature on the Data ribbon can help you create that list of Unique ID's)

=(SUMIF(A:A,O2,I:I)-24)*(SUMIF(A:A,O2,J:J)/SUMIF(A:A,O2,I:I))

I do have a question though. You said:

4. Subtract 24 from total units

What happens if the total units is less than 24? You'll be multiplying by a negative number, which will give you a negative Recovery value. Is that an issue?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 30, 2017 at 10:36:40
Thank you, this works for what I’m trying to do.

To answer your question regarding what happens if the total unit is less than 24….

I’m only interested values that are over 24 units (I day) that the provider charges. We are responsible only for 24 units. Does this make sense?

I appreciate all your help!


Report •

#5
March 30, 2017 at 11:40:28
✔ Best Answer
You can check for "Total Units" that are less than 24 by adding an IF function. This will eliminate negative Recovery values.

=IF(SUM(I2:I4)<24,"",(SUM(I2:I4)-24)*(SUM(J2:J4)/SUM(I2:I4)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 2, 2017 at 17:34:39
Great, this works beautifully.

Many thanks!


Report •

Ask Question