/Edit
I started with a bunch of formulas etc and lost my way of thinking, after going back to basic, I think I sort of figured my way out of it. Nevertheless I want to post the entire post and still hear about your ideas and opinions about the whole thing. Many thanks!
/end edit
I started with a bunch of formulas etc and lost my way of thinking, after going back to basic, I think I sort of figured my way out of it. Nevertheless I want to post the entire post and still hear about your ideas and opinions about the whole thing. Many thanks!
/end edit
Code:
Excel to me was mostly about using divide, autosum, sorting, filtering, and the likes, until I was given this case with the challenge to create an automated workbook to calculate premiums and so on. Since then I’ve started to get bits and bops of information about verious functions and formulas and Macros/VBAs and I’m well impressed that I managed to split several files from one data base after filtering and autosaving them with help of VBA. Now, I’m nowhere near being a beginner in VBA, I’m still very much a noob as most my VBA codes are still pages long (recorder), but I’m getting there and for that many thanks to MrExcel!
Well, today I´m stuck with a ws function, perhaps it it’s more easier to solve by VBA, but that’s still too far over my head to understand.
Scenario:
[TABLE="width: 347"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Start date[/TD]
[TD]Premium[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1-1-2010[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]10-1-2010[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]15-1-2013[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] D[/TD]
[TD]1-7-2012[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]20-6-2010[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] F[/TD]
[TD]11-7-2012[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]2-11-2011[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] H[/TD]
[TD]1-7-2012[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need to calculate two things but mostly need to calculate the number of months, and that preferably with ONE all encompassing formulae?;
[LIST=1]
[*]The premiums per month;
[*]The premiums per (variable) x months.
[/LIST]
which isn’t a problem if the contract starts per first, but there are contracts that start on different dates, and either I take a standard:
[LIST=1]
[*]if started between 25[SUP]th[/SUP] of previous month – 5[SUP]th[/SUP] of starting month, client pays full month, if 6[SUP]th[/SUP]-15[SUP]th[/SUP], client pays 2/3 of starting month, and if 16[SUP]th[/SUP]-24[SUP]th[/SUP], client pays only 1/3 of premium of the starting month. OR,
[*]Break down the premium per days (but not averaging, so januari 31 days and februari 28 days and so on) but note!
[/LIST]
There are other parameters involved, but they don’t matter for this. In fact, I only played with information given by column B in my worksheet.
Calculations:
[LIST]
[*]Calculate the premium for client A till thus far 2013. (three months)
[/LIST]
Headings for added columns C:F; 2010, 2011, 2012, 2013 (I’ve gotten to the stage that even I can’t remember WHY I had added these columns, but there’s a logical reasoning to it, it’s just lost in my mind…for now…)
C2 contains = 12
=IF(ROUND((DATEDIF(B2;"01/01/2011";"d")/30,42);0)<13;ROUND((DATEDIF(B2;"01/01/2011";"d")/30,42);0);0)
D2 contains = 12
=IF(ROUND((DATEDIF(B2;"01/01/2012";"d")/30,42);0)<1332;ROUND((DATEDIF(B2;"01/01/2012";"d")/30,42-C2);0);0)
E2 contains = 12
=IF(ROUND((DATEDIF(B2;"01/01/2013";"d")/30,42);0)<1332;ROUND((DATEDIF(B2;"01/01/2013";"d")/30,42-C2-D2);0);0)
And F2 = 3 =IF(ROUND((DATEDIF(B2;TODAY();"d")/30,42);0)<1332;ROUND((DATEDIF(B2;TODAY();"d")/30,42-C2-D2-E2);0);0)
As you can see, I’ve tried breaking it down into days AND rounding it (thus trying to tackle the different start dates with option a). For client A it’s not a problem, results are positive and show no errors. For client B I’m stuck already. His contract starts the 10[SUP]th[/SUP], so he should get 10 days of discount, but the rounding function rounded the number to a full month.
Initially I started off with ‘m’ instead of ‘d’, but as I started to fine tune my function….anyway..
[LIST]
[*]Calculate the premium for client C till thus far 2013.
[/LIST]
F4 = #NUM! =IF(ROUND((DATEDIF(B4;TODAY();"d")/30,42);0)<1332;ROUND((DATEDIF(B4;TODAY();"d")/30,42-C4-D4-E4);0);0)
But if I get to run my homebuilt macro that delete the “#NUM!”s like I did for “#ERROR!” in another part of my assignment, cells C4, D4, E4, which also returned with #NUM! dissapear, then cell F4 returns: 2…HOWEVER client C should be paying 2 months AND 15 days.. I took average days in my formula, but that’s cause I don’t …my mind’s a mess
Ok, while drafting this entry I needed to get my thoughts together again so I went back to basic and did this exercise:
Names of cells in top row.
[TABLE="width: 226"]
<tbody>[TR]
[TD]B17[/TD]
[TD]C17[/TD]
[TD]D17[/TD]
[/TR]
[TR]
[TD]10-3-2013[/TD]
[TD]1-4-2013[/TD]
[TD]22[/TD]
[/TR]
</tbody>[/TABLE]
In D17 =DATEDIF(B17;C17;"d")
So for the month march, client has 22 days of premium to pay, then, march has 31 days; so premium/31*22. But april has 30 days, how to solve this into a formula?
I build further on base of this formula:
=DATEDIF(B17;C17;"d")/30,42
[365days/12months=30,42average]
New result: 0,723208
If premium $20, 0,723208*$20=$14,46417 (rounded to $14,46 if cell format to $ #.##,##)
Ok, I guess I could work with this, but I guess I’d have to be careful with the rounding once I hit bigger volumes/usages/premiums. Any suggestions?
