Jump to content


Photo

Range vs Table

Indirect vs Counta and Offset

  • Please log in to reply
1 reply to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 101 posts
  • LocationHilton Head SC

Posted 20 January 2018 - 03:30 PM

I went back to the post and answer that you gave me on my Nov 8th post.

I was trying to use a cell that had a game counter in it using Countif.

I wanted to be able to average any desired number of the last (n) cells in a column

 

You supplied the following formula:

     =AVERAGE(INDIRECT("J" & A4-B5 & ":J" & A4+C5))

 

In a another post you suggested changing my WS's to tables and then be able to use Counta and Offset to achieve the same result which would be to average the last (n) cells in a column. That formula was:

 

 

=AVERAGE(OFFSET(B4,COUNTA(Table1[Column1])-3,0,3))

 

 

Am I reading this correctly that the first formula would be for use on a range and the second would be on a table and both would provide the same result...average the last (n) cells in a column? If so is there any virtue to using one format vs another?

Does changing all the WS's in a WB to tables from ranges change anything else. Will all the cross WS formulas still work?

 

Respectfully,

 

JPK1066

 

 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 22 January 2018 - 03:33 PM

Hi JPK,

 

The two formulas are different in that INDIRECT is needed in the first formula because the range that will be averaged depends upon a specific calculation, whereas the second formula uses COUNTA to count the number of records in a column and return the average of them all.

 

Both approaches could be used with or without tables. The only reason the second formula requires a table is because the structured table reference Table1[Column1] is used to extract the entire table column. If not using a table, you could replace this with an ordinary range such as B4:B100, but you would potentially need to adjust the formula if the number of records in the table changed. The big advantage of the structured table reference is that it will automatically adjust if records are added or removed.

 

Changing your ranges into tables will not automatically adjust your formulas. Formulas using ordinary ranges will continue to work, as will cross-worksheet formulas.

 

You can see a lot more about using Tables in Expert Skills Session 1: Tables and Ranges.

 

Please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users