SOLVED

growing table

%3CLINGO-SUB%20id%3D%22lingo-sub-1753359%22%20slang%3D%22en-US%22%3Egrowing%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1753359%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20Excel%20to%20keep%20track%20of%20my%20weight%2C%20e.%20g.%3C%2FP%3E%3CP%3EJan%201%2090%3CBR%20%2F%3EJan%202%2091%3CBR%20%2F%3EJan%203%2092%3C%2FP%3E%3CP%3EThe%20table%20grows%20continually%20on%20a%20daily%20basis%2C%20the%20full-blown%20table%20containing%20several%20thousand%20rows%3CBR%20%2F%3Eto%20cover%20the%20last%20decade%20or%20so.%3C%2FP%3E%3CP%3EI%20generated%20a%20diagram%20for%20the%20data%20by%20marking%20it%20and%20selecting%20a%20line%20plot.%3C%2FP%3E%3CP%3EI%20would%20like%20the%20diagram%20to%20automatically%20include%20ALL%20the%20data%20in%20particular%20any%20new%20data%20which%20I%20append%2C%20of%20course%2C%20ignoring%20any%20empty%20cells%20at%20the%20end%20of%20the%20table.%3C%2FP%3E%3CP%3EHow%20do%20I%20do%20this%3F%3C%2FP%3E%3CP%3EAlso%20consider%20a%20function%2C%20e.%20g.%20%3DMAX(B3%3AB8000)%20assumming%20that%203%20is%20first%20row%20or%20the%20table%20and%20the%20weight%20is%20in%20column%20B.%20Is%20there%20more%20elegant%20formula%20than%20this%2Ci.e.%2C%20not%20using%20the%20arbitrary%20and%20perhaps%20cryptic%20B8000.%3CBR%20%2F%3E%3DMAX(B3%3AB)%20doesn't%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1753359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1753530%22%20slang%3D%22en-US%22%3ERe%3A%20growing%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1753530%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823266%22%20target%3D%22_blank%22%3E%40Maxwellq%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20allows%20you%20to%20define%20dynamic%20named%20ranges%20-%20see%20for%20example%20%3CA%20href%3D%22https%3A%2F%2Fwww.excel-easy.com%2Fexamples%2Fdynamic-named-range.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EDynamic%20Named%20Range%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3EIn%20your%20example%2C%20you%20could%20create%20a%20range%20named%20XValues%20defined%20as%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DOFFSET(Sheet1!%24A%241%2C2%2C0%2CCOUNTA(Sheet1!%24A%3A%24A)-2%2C1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eassuming%20that%20A1%20and%20A2%20are%20filled%2C%20but%20the%20dates%20begin%20in%20A3.%20Sheet1%20is%20the%20name%20of%20the%20sheet%20with%20data.%3C%2FP%3E%0A%3CP%3ESimilarly%2C%20create%20a%20range%20named%20YValues%20defined%20as%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DOFFSET(Sheet1!%24B%241%2C2%2C0%2CCOUNTA(Sheet1!%24B%3A%24B)-2%2C1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20now%20specify%20%3DSheet1!XValues%20as%20the%20range%20for%20the%20category%20axis%20values%20of%20your%20chart%2C%20and%20%3DSheet1!YValues%20as%20the%20range%20for%20the%20series.%3C%2FP%3E%0A%3CP%3EThe%20chart%20will%20dynamically%20adapt%20itself%20as%20you%20add%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20you%20can%20use%20%3DMAX(YValues)%20to%20return%20the%20maximum%20weight.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1754408%22%20slang%3D%22en-US%22%3ERe%3A%20growing%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1754408%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F823266%22%20target%3D%22_blank%22%3E%40Maxwellq%3C%2FA%3E%26nbsp%3BWhy%20not%20simply%20convert%20your%20data%20into%20a%20structured%20table%20(Ctrl-T)%20and%20run%20your%20graph%20off%20that%20table.%20Such%20tables%20expand%20automatically%20and%20anything%20referring%20to%20the%20table%20as%20a%20whole%20or%20a%20column%20that%20is%20part%20of%20the%20table%20will%20expand%20with%20it%20as%20demonstrated%20in%20the%20attached%20file.%20Just%20add%20a%20few%20lines%20of%20dates%20and%20weights%20at%20the%20bottom%20and%20see%20what%20happens.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1763528%22%20slang%3D%22en-US%22%3ERe%3A%20growing%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1763528%22%20slang%3D%22en-US%22%3EThis%20is%20very%20useful%20to%20know.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1763549%22%20slang%3D%22en-US%22%3ERe%3A%20growing%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1763549%22%20slang%3D%22en-US%22%3EYes!%20This%20is%20the%20stress-free%20%E2%80%9Cno%20sweat%E2%80%9D%20solution.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20design%20of%20structured%20tables%20heeds%20the%20caveat%20not%20to%20use%20constants%20and%20absolute%20addresses%2C%20which%20is%20essentially%20the%20issue%20in%20the%20maintenance%20of%20maxwellq.xlsx.%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20address%20my%20second%20question%20in%20maxwellq.xlsx%20you%20can%20add%20the%20function%20%3DMAX(WeightTable%5BWeight%5D)%20(although%20the%20usage%20doesn%E2%80%99t%20appear%20to%20work%20in%20the%20older%20.xls%20version).%3CBR%20%2F%3E%3CBR%20%2F%3EI%20removed%20the%20ostentatious%20blue%20stripes%20by%20reformatting%20the%20cells%20with%20data%20using%20the%20fill-color%20%E2%80%9Cwhite%E2%80%9D.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'm using Excel to keep track of my weight, e. g.

Jan 1 90
Jan 2 91
Jan 3 92

The table grows continually on a daily basis, the full-blown table containing several thousand rows
to cover the last decade or so.

I generated a diagram for the data by marking it and selecting a line plot.

I would like the diagram to automatically include ALL the data in particular any new data which I append, of course, ignoring any empty cells at the end of the table.

How do I do this?

Also consider a function, e. g. =MAX(B3:B8000) assumming that 3 is first row or the table and the weight is in column B. Is there more elegant formula than this,i.e., not using the arbitrary and perhaps cryptic B8000.
=MAX(B3:B) doesn't work.

5 Replies
Highlighted

@Maxwellq 

Excel allows you to define dynamic named ranges - see for example Dynamic Named Range.

In your example, you could create a range named XValues defined as

 

=OFFSET(Sheet1!$A$1,2,0,COUNTA(Sheet1!$A:$A)-2,1)

 

assuming that A1 and A2 are filled, but the dates begin in A3. Sheet1 is the name of the sheet with data.

Similarly, create a range named YValues defined as

 

=OFFSET(Sheet1!$B$1,2,0,COUNTA(Sheet1!$B:$B)-2,1)

 

You can now specify =Sheet1!XValues as the range for the category axis values of your chart, and =Sheet1!YValues as the range for the series.

The chart will dynamically adapt itself as you add data.

 

And you can use =MAX(YValues) to return the maximum weight.

Highlighted
Best Response confirmed by Maxwellq (New Contributor)
Solution

@Maxwellq Why not simply convert your data into a structured table (Ctrl-T) and run your graph off that table. Such tables expand automatically and anything referring to the table as a whole or a column that is part of the table will expand with it as demonstrated in the attached file. Just add a few lines of dates and weights at the bottom and see what happens.

 

Highlighted
This is very useful to know.

Thank you very much.
Highlighted
Yes! This is the stress-free “no sweat” solution.

The design of structured tables heeds the caveat not to use constants and absolute addresses, which is essentially the issue in the maintenance of maxwellq.xlsx.

To address my second question in maxwellq.xlsx you can add the function =MAX(WeightTable[Weight]) (although the usage doesn’t appear to work in the older .xls version).

I removed the ostentatious blue stripes by reformatting the cells with data using the fill-color “white”.

Thank you very much.
Highlighted

@Maxwellq Uploading my original file again, with several formulae in column N, that pick the highest weight from column B. See which one suits you best.