Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset question .. better format!
Hi, I noticed that my table got reformatted on posting .. here is a revised model to explain the spreadsheet structure (I have also attached a small .xls file to make things clear. ____A__ __B__ __C__ __D__ __E__ 1. ______ Aug-06 ____ Sep-06 Oct-06 2. Sales__ £300 _______£400 ___£500 3. Receipts ____________£0 ____£400 4. 5. Credit__ 1 (Months) **From previous post 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=545449 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset question .. better format!
If the biggest the gap will ever be is one column you could do this: =OFFSET(E2,0,IF(D2="",-($B5+1),-$B5)) -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=545449 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset question .. better format!
Or this if you prefer: =IF(E2="","",OFFSET(E2,0,IF(D2="",-($B5+1),-$B5))) -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=545449 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cond. Format & Absolute Cell Reference Question | Excel Discussion (Misc queries) | |||
Conditional Format - Formula to Colour Every 3rd Cell in Offset Range | Excel Discussion (Misc queries) | |||
Question for use of offset and range | Excel Worksheet Functions | |||
Date Format Question | Excel Discussion (Misc queries) | |||
OFFSET function question | Excel Worksheet Functions |