• 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.

Calculating monthly average when there are not monthly sales

G

gtothek

Member
Hello all,

First time poster, I think your site in invaluable! I am a business analyst in the insurance industry and I need to calculate the average monthly premium of multiple clients. The problem I am facing is that there might be only 1 or 2 new premiums spread across time frames. There is NOT a new account that is bound every month. Is there a formula that can handle this, to take an average with concatenated dates? Please take a look at my attached s/s and if you have questions, please let me know.

Part of my analysis also has to measure the performance of customers before and after (date is concatenated 201207 yyyy/mm) they have participated in an incentive program. What is the best way to represent the data?

I really appreciate your help!
 

Attachments

Hi ,

I have a few questions.

1. I assume the data in column F is to be averaged , but averaged over what ? Can you clarify , possibly with an example ?

2. What are the criteria before you average ? I assume that since the data in columns A and D do not show any variation , they will not play any part in the averaging formula ?

3. What is the purpose of concatenating the year and month ?

4. Will it happen that every client /customer will have a Before and an After ? In case there is no After , what is to be output ?

Narayan
 
NARAYANK991 said:
Hi ,

I have a few questions.

1. I assume the data in column F is to be averaged , but averaged over what ? Can you clarify , possibly with an example ?

2. What are the criteria before you average ? I assume that since the data in columns A and D do not show any variation , they will not play any part in the averaging formula ?

3. What is the purpose of concatenating the year and month ?

4. Will it happen that every client /customer will have a Before and an After ? In case there is no After , what is to be output ?

Narayan
Click to expand...

Thanks for the quick response! See my replies, I appreciate your help!

1. Column F (Sum of which is insurance premium) needs to be averaged monthly by the dates in column G (which is concatenated yyyy/mm from columns B and C. For example the broker in E2:E14 has 13 different premium dates, but they are not monthly. They are spread over random dates. I need excel to calculate the average monthly premium across those dates.

2. Please ignore columns A and D, I should have hidden them.

3. I have concatenated the yyyy/mm so that I can measure the performance of the customer before and after the incentive program start date, which is 07/2012. So those premium before 07/2012 vs after. The performance measure being avg monthly premium. The formula is =IF(M8310>="201207","After","Before")

4. All customers will have an after 07/2012 date, as they are active customers with premium in 2014.
 
Hi ,

Thanks for the clarifications ; if the broker in E2:E14 has 13 different premium dates , what would be the calculation logic for the average monthly premium ?

Is it the total premium ( sum of the 13 premiums ) divided by the total period ( maximum date within the period - minimum date within the period , converted to number of months ) ?

Narayan
 
Yes, I believe that is what I'm trying to calculate. The first date in which premium was bound was in 01/2012 and the last on 01/2014. That is 3 years, so 36 months.

But is there a way to automate that calculation so that I don't have to manually do it for each broker? The dates will not be the same across the different brokers. For example, E15:E41, how would the monthly average be calculated for all those dates? Does this make sense? If not, please let me know.
 
Yes the premiums need to be calculated separately. Can you take me through the logic of the formulas, for my better understanding. I just want to make sure it is calculating what I'm looking for and I explained it thoroughly. I really appreciate your help on this!
 
Hi ,

The formula in column J is : =IF($E2<>$E1,SUMIFS($F$2:$F$41,$E$2:$E$41,$E2,$H$2:$H$41,J$1)/(L2-M2),"")

What this is doing is summing all those premium amounts for a given customer , and where the status in column H is Before. Then it divides this amount by the difference between the value in column L and the value in column M.

The formula in column L is : =MAX(IF($E$2:$E$41=$E2,IF($H$2:$H$41=J$1,$B$2:$B$41*12+$C$2:$C$41)))

What this does is for a given customer , where the status in column H is Before , it multiplies the year by 12 and adds the month , and then takes the maximum of this calculation ; similarly , the formula in column M takes the minimum ; the difference will give the time period over which the premiums were paid. For instance , for the customer ADCO GENERAL CORPORATION-DENVER, CO , the minimum yyyymm value is 201001 , while the maximum value is 201206 ; according to the formula , the time period will be 2012*12 + 6 - ( 2010 * 12 + 1 ) + 1 , which equals 24150 - 24121 + 1 = 30 months.

I have just noticed that I forgot to add the 1 to my formula in the file ; please make the correction.

A similar calculation is done for the After payments.

Narayan
 
Do I need to select all the rows in the field with this data? Or can I do it per producer?

I will look into this more in more depth, and hope it's ok if I have more questions.

What does E2<>E1 mean in the equation? Is it not equal to each other?

Thank you so much! I aspire to be this in excel one day!
 
Hi ,

You can select all the rows in the field ; since there is an IF check , there will not be any problem.

If you do it per producer , it will still work , but it's more work for you.

The <> symbol means not equal to each other , as you have said ; it works out this way since together the symbols mean either less than or greater than , which is the same as not equal to.

Just keep learning something each day , and suddenly you'll find you know a lot , and probably a lot more than most others !

Narayan
 
You must log in or register to reply here.
Back
Top