Formula based on condition

C

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
 
C

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
Click to expand...

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

Bob Phillips

okay, send it to

bob dot phillips at freeuk dot com

do the obvious with that lot
 
B

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

claude jerry

Thanks Bob

Works great

Bob Phillips said:
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
Click to expand...
 
Top