Formula based on condition

[claude jerry]

I have saved my Working excel file at teh below link

http://www.savefile.com/files/1904903

My Question is inserted in the file iteself

my email : (e-mail address removed)

 

[Bob Phillips]

Tend not to open up attachments here, so try explaining the problem here.

 

[claude jerry]

A B C D E F
G H I J K L
Month jan09 Feb09 Mar09 Apr09 May09 Jun09 Jul09
Aug09 Sep09 Oct09 Nov09 Dec09
01-Sep-09 1000 1000 1000 1000 1000 1000
1000 1000 1000 1000 1000 1000
01-mar-09 500 500 500 500 500 500
500 500 500 500 500 500
01-Feb-09 800 800 800 800 800 800
800 800 800 800 800 800
01-mar-09 200 200 200 200 200 200
200 200 200 200 200 200
01-Apr-09 600 600 600 600 600 600
600 600 600 600 600 600
01-Dec-09 700 700 700 700 700 700
700 700 700 700 700 700
01-jan-09 300 300 300 300 300 300
300 300 300 300 300 300

Formula output 300 1100 1800 2400 2400 2400 2400
2400 3400 3400 3400 3400

Col A = Shows the Months
Row 2 Shows Months Also

Formula should do the following in Cell B10, = add all values in Col B whose
months shown in Col A is greater or equal to Cel B1

 

[claude jerry]

A small error in my post

Formula should do the following in Cell B10, = add all values in Col B whose
months shown in Col A is greater or equal to Cel B1

It should Be = add all values shown in col b whose months shown in col A is
Less or equal to B1


Ooops .. ths copy > paste from Text file looks wiered and messy

Bob I can Foward the Excel file to you on uor email if you give it to me..

 

[Bob Phillips]

okay, send it to

bob dot phillips at freeuk dot com

do the obvious with that lot

 

[Bob Phillips]

Claude,

Based upon your data, try these formulae.

In the Period Rent Forecast table, enter this formula in G7

=ROUND(IF($D7<=G$6,$F7,$E7)/(DATE(YEAR($G$6)+1,MONTH($G$6),1)-$G$6)*(DATE(YEAR(G$6),MONTH(G$6)+1,1)-G$6),2)

and copy down and across. Note that this formula determines the number of
days in the month, you do not need to change the formula for each month.

For the new month totals, in G20 enter

=ROUND(SUMIF($D$7:$D$16,"<="&G$6,G$7:G$16),2)

and copy across.

 

[claude jerry]

Thanks Bob

Works great

Claude,

Based upon your data, try these formulae.

In the Period Rent Forecast table, enter this formula in G7

=ROUND(IF($D7<=G$6,$F7,$E7)/(DATE(YEAR($G$6)+1,MONTH($G$6),1)-$G$6)*(DATE(YEAR(G$6),MONTH(G$6)+1,1)-G$6),2)

and copy down and across. Note that this formula determines the number of
days in the month, you do not need to change the formula for each month.

For the new month totals, in G20 enter

=ROUND(SUMIF($D$7:$D$16,"<="&G$6,G$7:G$16),2)

and copy across.


--
__________________________________
HTH

Bob