Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with OffSet Function!
Hi, Let me start with a diagram.... A B C D E Aug-06 Sep-06 Oct-06 Sales 300 400 500 Receipts 400 Credit Mnth 1 I am using the offset function to forecast receipts from sales. Thus in the above example, in cell E3 (receipts row under october) is the formula '=OFFSET(E2,0,-$B5)' The receipts in the month will therefore be determined according to the number of months credit as entered in cell B5 (next to Credit months). If the forumla entered in E3 is copied to D3, the incorrect result is produced as it is picking up the value from cell C2 and not B2, as it should be. How can i adapt the formula so that breaks in the date range (eg column C above) are automatically taken into account. Or is there a better way of achieving the same result, other than using the offset function?? Thanks again, Jag -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=545433 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with OffSet Function!
If you can only have one blank column at a time, this variation to your
formula will take it into account: =OFFSET(E2,0,-$B5-ISBLANK(D$1)) For more than one blank column, this will not work properly. Also, it checks the header (D$1), you may want to check contents instead (D2) Hope this helps, Miguel. "jagbabbra" wrote: Hi, Let me start with a diagram.... A B C D E Aug-06 Sep-06 Oct-06 Sales 300 400 500 Receipts 400 Credit Mnth 1 I am using the offset function to forecast receipts from sales. Thus in the above example, in cell E3 (receipts row under october) is the formula '=OFFSET(E2,0,-$B5)' The receipts in the month will therefore be determined according to the number of months credit as entered in cell B5 (next to Credit months). If the forumla entered in E3 is copied to D3, the incorrect result is produced as it is picking up the value from cell C2 and not B2, as it should be. How can i adapt the formula so that breaks in the date range (eg column C above) are automatically taken into account. Or is there a better way of achieving the same result, other than using the offset function?? Thanks again, Jag -- jagbabbra ------------------------------------------------------------------------ jagbabbra's Profile: http://www.excelforum.com/member.php...o&userid=32525 View this thread: http://www.excelforum.com/showthread...hreadid=545433 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numerical integration | Excel Discussion (Misc queries) | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
offset function | About this forum | |||
clock | Excel Worksheet Functions | |||
offset function | Excel Worksheet Functions |