|
Formula based on condition
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 : |
Formula based on condition
Tend not to open up attachments here, so try explaining the problem here.
-- __________________________________ HTH Bob "claude jerry" wrote in message ... 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 : |
Formula based on condition
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 "Bob Phillips" wrote: Tend not to open up attachments here, so try explaining the problem here. -- __________________________________ HTH Bob "claude jerry" wrote in message ... 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 : |
Formula based on condition
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.. "claude jerry" wrote: 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 "Bob Phillips" wrote: Tend not to open up attachments here, so try explaining the problem here. -- __________________________________ HTH Bob "claude jerry" wrote in message ... 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 : |
Formula based on condition
okay, send it to
bob dot phillips at freeuk dot com do the obvious with that lot -- __________________________________ HTH Bob "claude jerry" wrote in message ... 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.. "claude jerry" wrote: 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 "Bob Phillips" wrote: Tend not to open up attachments here, so try explaining the problem here. -- __________________________________ HTH Bob "claude jerry" wrote in message ... 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 : |
Formula based on condition
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 "Bob Phillips" wrote in message ... okay, send it to bob dot phillips at freeuk dot com do the obvious with that lot -- __________________________________ HTH Bob "claude jerry" wrote in message ... 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.. "claude jerry" wrote: 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 "Bob Phillips" wrote: Tend not to open up attachments here, so try explaining the problem here. -- __________________________________ HTH Bob "claude jerry" wrote in message ... 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 : |
Formula based on condition
Thanks Bob
Works great "Bob Phillips" wrote: 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 "Bob Phillips" wrote in message ... okay, send it to bob dot phillips at freeuk dot com do the obvious with that lot -- __________________________________ HTH Bob "claude jerry" wrote in message ... 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.. "claude jerry" wrote: 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 "Bob Phillips" wrote: Tend not to open up attachments here, so try explaining the problem here. -- __________________________________ HTH Bob "claude jerry" wrote in message ... 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 : |
| All times are GMT +1. The time now is 09:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com