Hi Priyanka.. Offset Function
The OFFSET function is used to return the address of a cell or a range of cell through the use of a reference cell. If we know the cell or range beforehand, we are unlikely to use it at all. It is very useful in our spreadsheets when we do not know the cell or the range of cell in advance, especially in situations where you allow the spreadsheet to interact with the users such as preparing a *business dashboard*<http://www.advanced-excel.com/insurance.html>that reports on the performance of a product from a list. [image: Offset Function Diagram 1] In the above diagram, cell A6 contains the character "a". If you look at the formula bar, you will notice that the character "a" was not typed into the cell. Instead, it was a result returned by the offset function. We have basically instructed excel to return the value that is found in cell A3. How was this done? 1. First, we identify a cell as a starting point. This is our base cell. In the formula above, the base cell is A1 in "=OFFSET(*A1*,2,0,1,1)". 2. We have indicated in the offset formula that we want the result that is 2 rows below the base cell A1 "=OFFSET(A1,*2*,0,1,1)". 3. Next, we also tell the offset formula that the result should be 0 column from the base cell. By now, the offset formula would have identified that we are looking at the cell A3, which is 2 rows below A1 and zero column to the right of A1. 4. Finally, we told excel that size of the range we want is 1 row tall and 1 column high, i.e. a single cell "=OFFSET(A1,*2*,0,*1,1*)". 5. As a result of this instruction, the character "a" was displayed in cell A6. This result is the same as entering without quotes "=A3" into cell A6. *Note: * a) If you want the offset function to move above the base cell, then enter it as a negative number. In the above example, the offset will return an error because it is unable to find anything above the cell A1 (outside the boundary of the excel worksheet). However, if you have entered the base cell as B3, then you could instruct the offset formula to move 1 row above the base cell. The offset function will return the value "65" as its result. The formula to do this is "=OFFSET(B3,-1,0,1,1)" b) The smallest size of the result "=OFFSET(A1,2,0,*1,1*)" is 1 row tall and 1 column wide. In this example, only 1 row and 1 column are used. The size of the range can be increased but the result will show #VALUE! in the spreadsheet. This is because the cell cannot accept a range reference such as this: =C1:C2. To understand this better, you can use it together with another formula such as SUM. Using OFFSET function within a SUM function When we increase either the row height or column width in the offset function "=OFFSET(A1,2,0,*1,1*)" to more than 1, the reference is converted to a range. In this section, we will explain how we make use of the offset function to find out sum of the range C1:C2 1. Set up the offset function such that it is referenced to the cell C1. The formula will start with the base cell A1 "=OFFSET(*A1*,0,2,1,1)". To identify C1 as the starting poin (top left hand corner of a range), enter the number 0 to instruct the offset function to stay in the same row 1 "=OFFSET(A1,*0*,2,1,1)". Enter the number 2 to indicated that we want to move 2 columns to the right of the base cell A1 "=OFFSET(A1,0,*2*,1,1)" (Column A is numbered 0). That is Column C. Keep the last two numbers as 1 to indicate that we want the offset function to point to one single cell C1 "=OFFSET(A1,0,2,*1,1*)". The result will show the value in cell C1, i.e. the number 43. 2. Enclose the offset function with a sum function "*=sum(* OFFSET(A1,0,2,1,1)*)*". The result continues to show 43. 3. As our plan is to sum the range C1:C2, we need to increase the row height to 2 "=sum(OFFSET(A1,0,2,*2*,1))". The number now changes to *75*. It has included the number 32 in cell C2. The number 75 is the result of summing cell C1 (43) and C2 (32). We have included a diagram below to help you understand better. [image: Offset function example 2] Other than using it together with the SUM formula, you can also use the OFFSET function <http://www.advanced-excel.com/resizeable_range.html> to determine a range automatically. The INDIRECT Function The *INDIRECT* worksheet function is a useful tool for creating cell or range references "on the fly", as a formula is evaluated, rather than "hard coding" them into the formula. Microsoft's on-line Help documentation of this function only hints at is usefulness. In its simplest usage, the *INDIRECT* function allows you to put the address of one cell in another, and get data from the the first cell by referencing the second. For example, if cell A1 has the value "C3", then * =INDIRECT(A1)* will return the value in C3. The real power of the *INDIRECT* function is that it can turn any string into a reference. This includes any string that you build up using string constants and the values of other cells in the formula, strung together with the & concatenation operator. For example, the simple formula *=SUM(A5:A10)* will sum the values in the range A5:A10. However, suppose you want to be able to specify which range of rows to sum "on the fly", without having to change the formula. The INDIRECT function allows you to do this. Suppose you put your starting row cell B1, and your ending row in C1. Then, you can use the formula *=SUM(INDIRECT("A"&B1&":A"&C1))* The argument to the INDIRECT function is *"A"&B1&":A"&C1* If B1 contains 5 and C1 contains 10, this evaluates to the string "A5:A10". The INDIRECT function converts this string to an actual range reference, which is passed to the SUM function. For VBA formulas to return relative sheet names (e.g., the name of the next or previous worksheet), see the Referencing Worksheets From Formulas<http://www.cpearson.com/excel/sheetref.htm>page. Another useful feature of the INDIRECT function is that since it takes string argument, you can use it to work with cell references that you don't want Excel to automatically change when you insert or delete rows. Normally, Excel will change cell references when you insert or delete rows or columns, even when you use absolute referencing<http://www.cpearson.com/excel/relative.htm>. If you have the formula =SUM($A$1:$A$10), and then insert a row at row 5, Excel will convert the formula to =SUM($A$1:$A$11). If you don't want this to happen, use the INDIRECT function to change a text string to a reference: *=SUM(INDIRECT("A1:A10"))* Since Excel sees "A1:A10" as a text string rather than a range reference, it will not change it when rows or columns are deleted or inserted. This feature is important when working with some array formulas<http://www.cpearson.com/excel/array.htm>. Frequently, an array formula will use the ROW() function to return an array of numbers. For example, the following formula will return the average of the 10 largest numbers in the range A1:A60 : *=AVERAGE(LARGE(A1:A60,ROW(1:10)))* However, if you insert a row between rows 1 and 10, Excel will change the formula to *=AVERAGE(LARGE(A1:A60,ROW(1:11)))* which will return the average of the 11 largest numbers. If we use the function with a string, Excel won't change the reference, so the formula will remain correct, regardless of whether and where rows are inserted or deleted. *=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))* You can use the *INDIRECT* function in conjunction with the *ADDRESS*function. The *ADDRESS* function uses row and column numbers to create a string address. For example, the formula *=ADDRESS(5,6)* returns the string *$F$5*, since *$F$5 *is the 5th row of column 6. You can use then pass this to *INDIRECT *to get the value in cell *F5*. For example, *=INDIRECT(ADDRESS(5,6))* . While this example may seem trivial, it illustrates a technique that you can use to build more complicated formulas. On Fri, Feb 10, 2012 at 12:35 PM, PrIyAnKa <[email protected]> wrote: > Kindly let know the usage of Offcet and indirect formual > > For which purpose we are using the same > > And how we can use large formula with sum (to get sum from sheet2 to > sheet1 for highest 5 amounts ) > > > On Thu, Feb 9, 2012 at 11:06 PM, NOORAIN ANSARI > <[email protected]>wrote: > >> Hi Maansi, >> >> you are welcome in Excel-Macro Group. >> >> See attached example of >> >> 1. Offset >> 2. Indirect >> 4. Index >> >> >> >> On Thu, Feb 9, 2012 at 10:26 AM, Manasa Yes <[email protected]> wrote: >> >>> Dear Team, >>> >>> Good Morning... >>> >>> I would like to thank one and all for excellent tips, advise & timely >>> help on excell.... really proud to be part of this learning team!! >>> >>> I had joined for MIS Team recently and would like to learn much on excel >>> so joined this group recently - special thanks to Mr. Noorain on helping me >>> out to join the group... >>> >>> I had learnt about Sumproduct - which is provided by the team and would >>> like to know some more on advanced excell formulas which will smoothen >>> work : >>> >>> some of our company's summary reports contains the below formula's but i >>> do not have any idea on how to use and when to use... please help : >>> >>> a) Off set >>> b) Index (I had got one or two example from the recent mails) >>> c) Indirect >>> >>> thank you all. >>> >>> have a great day!! >>> >>> Maansi. >>> >>> >>> -- >>> FORUM RULES (986+ members already BANNED for violation) >>> >>> 1) Use concise, accurate thread titles. Poor thread titles, like Please >>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >>> will not get quick attention or may not be answered. >>> >>> 2) Don't post a question in the thread of another member. >>> >>> 3) Don't post questions regarding breaking or bypassing any security >>> measure. >>> >>> 4) Acknowledge the responses you receive, good or bad. >>> >>> 5) Cross-promotion of, or links to, forums competitive to this forum in >>> signatures are prohibited. >>> >>> NOTE : Don't ever post personal or confidential data in a workbook. >>> Forum owners and members are not responsible for any loss. >>> >>> >>> ------------------------------------------------------------------------------------------------------ >>> To post to this group, send email to [email protected] >>> >> >> >> >> -- >> Thanks & regards, >> Noorain Ansari >> ** <http://excelmacroworld.blogspot.com/>*http://noorainansari.com/* >> *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> >> >> >> -- >> FORUM RULES (986+ members already BANNED for violation) >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> >> 2) Don't post a question in the thread of another member. >> >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> >> 4) Acknowledge the responses you receive, good or bad. >> >> 5) Cross-promotion of, or links to, forums competitive to this forum in >> signatures are prohibited. >> >> NOTE : Don't ever post personal or confidential data in a workbook. Forum >> owners and members are not responsible for any loss. >> >> >> ------------------------------------------------------------------------------------------------------ >> To post to this group, send email to [email protected] >> > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to [email protected] > -- Thanks & regards, Noorain Ansari *http://noorainansari.com/* <http://excelmacroworld.blogspot.com/> *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to [email protected]
