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

Apportioning values of an invoice period across the relevant months

M

mxk

New Member
Hi there,


I need a little help on apportioning values taken from an invoice correctly across the months the invoice covers. Here’s a simple example:


Inputs:

Invoice Start Date = 05/02/2012

Invoice End Date = 20/04/2012

(ie total days = 74 (ie 24 x Feb, 31 x March, 19 x April)

Electricity Consumption for period = 1,000kWh


The outputs I need would be:

Feb kWh = (24/74)*1000=324

March kWh = (31/74)*1000=419

April kWh = (19/74)*1000=257


I am trying to create a table with a layout as per below, which is able to auto-populate the applicable months with the corresponding apportioned kWh values for each invoice (COLUMNS E-I), based on the values input in columns A,B and D. The nested IF statements I am writing are getting way to out of control, hence why I have arrived here, coz I know there’s got to be a smarter way.


Can someone please help me?

Thanks.


A B C D E F G H I

Start End Days kWh Jan-2012 Feb-2012 Mar-2012 Apr-2012 May-2012

5/02/2012 20/04/2012 74 1000 0 324 419 257 0
 
Hi ,


Try this :


=ROUND(IF(AND(E$1>=($A2-DAY($A2)+1),E$1<=$B2),IF(MONTH(E$1)=MONTH($A2),$D$2*(F$1-1-$A2)/$C$2,IF(MONTH(E$1)=MONTH($B2),$D$2*(DAY($B2)-1)/$C$2,$D2*DAY(F$1-1)/$C2)),0),0)

Narayan
 
Hi mxk,


If your data is in Below Format

[pre]
Code: 
Start Date	End Date	kWh	Jan-12	Feb-12	Mar-12	Apr-12
05-02-12	20-04-12	1000	0	324.32	418.92	256.76
[/pre]
then in D2 write below Formula

=ROUND((SUMPRODUCT((MONTH(ROW(INDIRECT($A2+1&":"&$B2-1)))=MONTH(D$1))*1)/($B2-($A2+1)))*$C2,2)


In your current situation, you are excluding Start Date (February is leap in this year) and End Date too.. So please adjust the highlighted area in formula, if above (24 * Feb) is a typo error.


Regards,

Deb
 
Thanks a million for your help Narayank991 and Debraj Roy !


Love your solutions - I’ve had a play around with both but have the following queries..


Debraj Roy:

You’re correct, thanks – (24 * Feb) was a typo.

In actual fact I need a solution which caters to 2 scenarios:

1) includes start date only

2) includes both start and end dates


I was able to modify your formula as per below to address scenario 1) above:


=ROUND((SUMPRODUCT((MONTH(ROW(INDIRECT($A2&":"&$B2-1)))=MONTH(D$1))*1)/($B2-($A2)))*$C2,2)


However, I couldn’t get it to work for scenario 2. I tried modifying the formula as per below, however I haven’t done this correctly, as it results in an overall kWh which is greater than it should be. (ie using example provided: Feb = 333.33, Mar = 413.33, Apr = 266.67, Total = 1,013.33)


=ROUND((SUMPRODUCT((MONTH(ROW(INDIRECT($A2&":"&$B2)))=MONTH(D$1))*1)/($B2-($A2)))*$C2,2)


(Correct Result: Feb = 328.95, Mar = 407.89, Apr = 263.16, Total = 1,000)


Would you please be able to help me to modify the formula to make this work?

(Also, INDIRECT is a new one for me, are you able to help explain how it’s being put to work in this example?)

Thanks.


Narayank991:

For your formula, I modified C2 to =B2-A2 (Scenario 1) and =B2-A2+1 (Scenario 2) and modified your formula slightly as follows in order to make both scenarios work as per below:


Scenario 1)

=ROUND(IF(AND(E$1>=($A2-DAY($A2)),E$1<=$B2),IF(MONTH(E$1)=MONTH($A2),$D2*(F$1-$A2)/$C2,IF(MONTH(E$1)=MONTH($B2),$D2*(DAY($B2)-1)/$C2,$D2*DAY(F$1-1)/$C2)),0),0)


Scenario 2)

=ROUND(IF(AND(E$1>=($A2-DAY($A2)),E$1<=$B2),IF(MONTH(E$1)=MONTH($A2),$D2*(F$1-$A2)/$C2,IF(MONTH(E$1)=MONTH($B2),$D2*(DAY($B2))/$C2,$D2*DAY(F$1-1)/$C2)),0),0)


However, the formulas don’t appear to work when the Start Date and End Dates fall within the same month.

Can you help make the appropriate adjustments?

Thanks again.
 
Re:above


Am thinking my post above may be a little confusing. Think it implies I'm after 1 solution for 2 different scenarios...but not the case, sorry.


I'm after 2 separate formulas to address the 2 different scenarios, sorry for the confusion.
 
Hi ,


I am sorry if I am not able to respond ; when you make a post , I copy the sample data you give , and try to work out a solution. Once I post a solution , if there is no reply within a few hours ( say 4 to 6 hours ) I delete the trial workbook.


Now , I really don't feel like repeating all of the above viz. copying and pasting the data , using Text to Columns , copying and pasting the formula from my earlier post , or from your latest post , and then trying to work out a new solution.


If you can upload your sample workbook with the data and the formulae in it , it will save me the trouble. Otherwise , I'll leave it to Debraj to respond.


Narayan
 
Hi Narayan - apologies for belated response - was the weekend.


I've included 3 versions of what I'm trying to achieve in file at the link below.


1) Manual calcs

2) Narayan solution

3) Debraj solution


Black text = formula required for scenario 1 (including start date/excluding end date)

Blue text = formula required for scenario 2 (including start date and end date)

Red cells/text = unable to get formula to work.


https://www.dropbox.com/s/gwu3iywo0sf6hpo/Invoice%20Apportioning%20Template%20V1.xlsx
 
Hi ,


Thanks for uploading your file ; will save us time.


I have a doubt ; how do you switch between Scenario 1 and Scenario 2 ? When will you use Scenario 1 and when will you use Scenario 2 ? Or should the formula be developed for both scenarios , since it is not possible that one formula will cater to both scenarios ?


Narayan
 
Hi mxk,


to Include both Start date and End Date use below Formula.


=ROUND((SUMPRODUCT((MONTH(ROW(INDIRECT($A19&":"&$B19)))=MONTH(E$16))*1)/(($B19-$A19)+1))*$D19,2)


As you have already decoded the FORMULA correctly..

When you want to Include both Start Date & End Date, Highlighted Area gives you answer as difference between two date, and you want to include End date too.. Please add +1 with the above to get result correctly. :)

i.e. Difference between 1st Jan and 2nd Jan is 1, where you need answer as 2.


Regards,

Deb
 
Hi Narayan and Deb,


No probs - I will upload files in future..much easier.


Narayan - I won't be switching between scenario 1 and 2. You're correct - I'm wanting to develop a 2 separate formulas to cater for the 2 different scenarios I've encountered. They will be used on separate projects, dependent on invoice format of the supplier for each.

Deb has helped me adjust her formula to get scenario 2 functioning correctly now, but if possible, I'd still love to learn how to adjust your formulas to cater for the single month invoice, just for my own learnings.


Deb - thanks so much. Understand and have got your formula working now. Thanks :)
 
You must log in or register to reply here.
Back
Top