• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to calculate annual commission with data on different lines [Solved by OP]

C

candybg

Member
Attached is a spreadsheet I've been working on. The DATA tab has my data formatted as a table straight from my system, with an added column to calculate Commission. I need to multiply the calculated commission by the ANNUALIZED PREMIUM to get annual commission.

My problem seems to be that the lines with commission % are not on the same lines as the Annualized Premium.

I attempted to use a Pivot Table (on the second tab) to summarize the data by Policy number by Client and then add a calculated field to multiply commission % * annualized premium. I've added some yellow cells to show that my calculated field is not always correct, based on the number of lines per policy.

I'd like either a single formula to use in the original data table to get this calculation, or a Pivot Table or two. I don't want an array or macro at this point as this will be used by staff with virtually no excel skills.

I have a work-around for now using my pivot table, then copy-paste special-values to another sheet, then add my formula for annual commission, then use that as the data for another pivot table.

Thanks for any and all suggestions/help!
 

Attachments

Hmm, part of the issue is that there is no identifier matching paired line items.

For example, Clnt3 has 4 line items (2 pairs). But does not have identifier matching pair of lines (though it can be easily understood by human looking at the data). Is there something that identifies pair (other than Policy number)?
 
It is policy number that matches all up.
I kept working at this. Found that if I add another calculation line to my data table and adjust my calculated field it works perfectly.

I'm uploading my fixed spreadsheet so others might benefit.

Is there a way to mark this SOLVED? I'm not finding that option yet : (
 

Attachments

FIY - You can do it all in Pivot using calculated field. No need to use Max

I think there's error in data for Clnt3. Check line 10 Annualized Premium vs line 12 Billed Premium.
 

Attachments

The different figures for Annualized Premium vs Billed Premium represent what is OWED and what has been PAID by the client. Not really an issue for this. Some clients might pay monthly, so the figures won't equal until the end of their policy year.

I'm not yet able to see how your calculated field for annual commission worked when my initial calculation sometimes showed double the actual figure. I appreciate your PivotTable
 
I did not use calc Comm% column in my pivot. Instead I used calculated field to do the calculation.
 
You must log in or register to reply here.
Back
Top