[Solved] Use of variables in the AVERAGE function in Calc

[randy mcdermott]

Greetings group. First post here.

In Row1 Column I, I have declared 2 variables: DIM Variable1 as integer=4017, Variable2 as integer=4047

I want to replace the Row values with the 2 variables in the following but I can't get the syntax.

=AVERAGE(D4017:D4047)

The nearest I can get is this but I get "#NAME?"

=AVERAGE(dvariable1:dvariable2)

Can you point me in the right direction?

Thanks in advance, Randy

[RusselB]

Welcome to the Forums.
Please attach a sample of your spreadsheet as I don't understand how you have defined variables as you have stated in Calc. You can find help for attaching your spreadsheet in this tutorial

I think what you are looking for would be similar to

Code: Select all

=Average("D"&variable1:"D"&variable2)

Please note that I have not used a construct like this in Calc, but I have used it in other spreadsheets, so it may not work in Calc.

[FJCC]

I think the closest you can come to this is placing the value 4017, or the text "4017" in I1 and putting 4047 in I2. You can then use the INDIRECT() function within the AVERAGE() function

Code: Select all

=AVERAGE(INDIRECT("D" & I1 & ":D" & I2))

You cannot define variables in Calc in the way you seem to have tried. You can define a name which points at a cell but values are stored in cells.

[randy mcdermott]

Thanks for the quick reply Russel.

I tried =AVERAGE("D"&variable1:"D"&variable2). It didn't work. There must be something else.

What I'm trying to do is get the average, min and max of a 1 month range of my systolic, diastolic, beats per minute and glucose numbers I just want to be able to change the two variable row numbers and the calculations will be done automatically instead of having to insert the row numbers in each of the functions. See the attached.

The file is a bit too big. I'll pare it down a little.

Okay, it's uploaded.

[randy mcdermott]

Thanks for the quick reply FJCC. That seems to make sense but things are starting to get blurry. I give that a try in the morning.

[Zizi64]

In Row1 Column I, I have declared 2 variables: DIM Variable1 as integer=4017, Variable2 as integer=4047

The bolded part of your post shows me that the variables are in a StarBasic macro code.

Do you want to use them
- in the macro code together with the Cell function AVERAGE(), or
- in the cells of a Calc sheet?


If you want to use them in the Cells, then why not put the values into some cells directly?

If you want to use the Cell function "AVERAGE()" (or any other Cell functions) in a macro code, then you must study, how to do it...
Or just use the simple

Code: Select all

SomeResultVariable = (Variable1 + Variable2)/2

[FJCC]

The data layout in the spreadsheet is making the desired calculation far more difficult than it needs to be. In the attached files I have shown two alternative data layouts. In Randybac.ods, I have removed all of the blank rows and shown the date in every row. There are two date columns because I kept the original column with the blank cells and used formulas to fill in the blanks in the new Date2 column. This process was tedious, so I only did 13 months. I then made a Pivot Table that shows the average, maximum and minimum of the desired quantities calculated for each month. The Years and Date2 columns of the table have filters so you can view any Year - Month combination.
The file RandybacLong.ods has the data reshaped so that one column labels the quantity and another shows the value. (The reshaping was done with another program for my convenience. It could be done with Calc.) The pivot table on that sheet again shows the average, maximum and minimum for each month in a more compact manner than the other file. You can still filter for the month and year and you can also filter for which quantities are shown.
In either data layout new data can be added by inserting cells in the data array and refreshing the pivot table. The data need not be entered in a particular order. The Pivot Table takes care of grouping the values and calculating the statistics.
If you are interested in using either of these layouts, I can explain the details of setting this up. Your problem in calculating with the original data layout is an illustration of how layouts that are convenient for humans are often not convenient for computer calculations. With the right tools, the data can be arranged for easy calculations and the results can be displayed in a human-friendly way.

[randy mcdermott]

Thanks to all that gave me help but FJCC gets the cookie.

I think the closest you can come to this is placing the value 4017, or the text "4017" in I1 and putting 4047 in I2. You can then use the INDIRECT() function within the AVERAGE() function

Code: Select all

=AVERAGE(INDIRECT("D" & I1 & ":D" & I2))

You cannot define variables in Calc in the way you seem to have tried. You can define a name which points at a cell but values are stored in cells.

When my family doctor calls she asks for my blood pressure, heart rate and glucose monitor readings. Thats 12 calculations that have to be done on one month's readings taken morning and evening.

I shortened the file to just 100 rows for illustrative purposes. I2 is the first row number and I3 is the last row number. Cells D, E, F, and G - 2 through 4 (12 cells in total) contain the functions to calculate the output given the values in I2 and I3. Of course the advantage of using I2 and I3 is that I only have to input those values once as opposed to inserting them in each of the 12 function cells.

See uploaded Randybac2.ods

Thanks again all. This was bugging me for quite a time.

Regards, Randy

[randy mcdermott]

FJCC, you must REALLY like this stuff! I appreciate the effort you've put into this for me. I'd like to take the time to analyze and understand your take on this little problem, but being a lazy 70 year old it would cut too much into my tv/facebook time. I have what I need and no one else is going to see it so good enough. Thanks muchly bub.

Regards, Randy

[randy mcdermott]

Thanks for the input Zizi but FJCC seemed to have given me the easiest route for my limited edumication.

Regards, Randy

[RusselB]

My suggestion was close to FJCC's.
Main difference is that FJCC's includes the INDIRECT statement and mine didn't.

[randy mcdermott]

Thanks just the same Russel.

Regards, Randy