part month calculation

[slamanager]

Hi,

Seems im really behind the eight ball this month, i thought i had my maths wizard hat on but it got knocked off, I voluntered to try and fix some sales spreadsheet now it belongs to me, go figure.

They need to calc total contract values per month including if a contract starts part way through a month.

example
A1 startday 1/1/2011
B1 term of contract 12 months
C1 Calculated end date 1/1/2012
D1 Contract value 12,000

so the answer would be 1,000 per month

but......

A1 startday 15/1/2011
B1 term of contract 3 months
C1 Calculated end date 15/4/2011
D1 Contract value 12,000

So thats 4000 per month but its not there are 16 days in JAN, full month of FEB, Full Month of MAR, and the 15 days of APR how can i show in a monthly matrix these part month values. Would a SUMPRODUCT type formula be the way. Or should i start counting days and working it out based on the days ........

I did go through the fincial formulas but there didnt seem to one that dealt with this type of combination.

 

[slamanager]

ok,

i have 50,000 rows of data spanning 2000 to 2025 each row has a start date possibly at the start of a month but can be anywhere in the month im trying to pivot the data or summarise it with formulas but need to make sure those part months of the contract value are allocated to the corrcet month both at the start and end of each contract.

if you use your fingers there are only 17 days from the 15th to the 31st

no holidays or weekends required to be computed

 

[Roli001]

ok,

i have 50,000 rows of data spanning 2000 to 2025 each row has a start date possibly at the start of a month but can be anywhere in the month im trying to pivot the data or summarise it with formulas but need to make sure those part months of the contract value are allocated to the corrcet month both at the start and end of each contract.

if you use your fingers there are only 17 days from the 15th to the 31st

If I use Excel there are only 16 days left not 17. 05/31/11 - 05/15/11 = 16

Can you give a data sample of 10-15 rows and a sample of how you want the data displayed on an Excel spreadsheet with the exact values at least for one row.

 

[slamanager]

I Think have this under control using the formula from sanrv1f nested in a if statement

=$C$2*(MIN(EOMONTH(F2,0),$B$2)-MAX(F2,$A$2)+1)/($B$2-$A$2+1)

many thnaks to all that have helped

 

[slamanager]

Try this,

Excel Workbook
	A	B	C	D	E	F	G
1	Start	End	Value
2	15-Jan	15-Apr	12000			Jan-11	2242
3						Feb-11	3692
4						Mar-11	4088
5						Apr-11	1978
Sheet1

Excel 2003

Cell Formulas
Range	Formula
G2	=$C$2*(MIN(EOMONTH(F2,0),$B$2)-MAX(F2,$A$2)+1)/($B$2-$A$2+1)

You need to include the Analysis tool pak addin for EOMONTH function to work

Hello again,

Ive been using this formula but now have an issue where there is a negative value and the dates are one day apart

Example
Start day 29/12/2010
End day 30/12/2010
Value -1356327
Term 0
Answer should be -1356327
but im getting 39333476

ive gone through and broken down the formula and it seems not to either deal with the min and max parts correctly could you please review and advise.


Thanks

 

[slamanager]

Hello again,

Ive been using this formula but now have an issue where there is a negative value and the dates are one day apart

Example
Start day 29/12/2010
End day 30/12/2010
Value -1356327
Term 0
Answer should be -1356327
but im getting 39333476

ive gone through and broken down the formula and it seems not to either deal with the min and max parts correctly could you please review and advise.


Thanks

All ok now went back and delt with my issues