Adjustable worksheet range

[Nitha120]

hi,

i have a worksheet data in excel with two column time another is height. I want to take the average for each column and make a chart based on the average value.

My question is how can I make the range adjustable according to the data range because some data will be shorter range other have a longer range. I want my data sheet to delete or insert row as neccessary to accominate the range change while still able to take average and do the graph.

I want my AVRG row to shift and select the right range to take the average when the column get longer or shorter.


Can anybody please help me!

 

[byundt]

The AVERAGE worksheet function will ignore blank cells when computing an average. It will also ignore cells containing text or an empty string =""

If you are using the #N/A error value to avoid plotting certain values, then you can use an array formula for the average like:
=SUM(IF(ISNA(A1:A8),"",A1:A8))/COUNT(A1:A8)
Remember to hold the Control and Shift keys down while pressing Enter. Excel should respond by adding curly braces { } surrounding the formula.

You can create a dynamic named range to chart your data. Let's suppose that time is in column A and height in column B. Open the Insert...Name...Define menu item, put a suitable name in the "Names in workbook" field, then a formula like the following in the "Refers to" field:
=$A$1:INDEX($A$1:$A$100,MAX(COUNTA($A$1:$A$100),COUNTA($B$1:$B$100)))
This formula will expand and contract as data is added to columns A and B. It will return a range in column A equal in length to the longer list of data (column A or column B). The reference to row 100 is arbitrary--just make sure it extends beyond any conceivable amount of data. It is also important to make sure that columns A and B don't include any blanks until the end of the data.

Brad

 

[SkipVought]

How can I rename a table as it changes size faq68-1331

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]