Can I include functions in a CSV file?

[kk]

Hello everybody!
I am able to create csv files that contain functions that are readable
from excel.
For example the following .csv file:
10,20,30
20,30,40
=AVERAGE(A1:A2),=AVERAGE(B1:B2),=AVERAGE(C1:C2)
when opened from excel prints the following table:
A B C
1 10 20 30
2 20 30 40
3 15 25 35

Instead of defining the specific cells that the Average function
should use as an input, i want to say something lile:
"Calculate the average of the two cells above this cell".
Something like saying
=Average(column[this]line[-2]:column[this]line[-1])

Sound familiar to anyone?
Thanks in advance!

 

[Harlan Grove]

I am able to create csv files that contain functions that are
readable from excel.
For example the following .csv file:
 10,20,30
 20,30,40
 =AVERAGE(A1:A2),=AVERAGE(B1:B2),=AVERAGE(C1:C2)
when opened from excel prints the following table:
   A  B  C
1  10  20  30
2  20  30  40
3  15  25  35

Instead of defining the specific cells that the Average function
should use as an input, i want to say something lile:
"Calculate the average of the two cells above this cell".
Something like saying
=Average(column[this]line[-2]:column[this]line[-1])

If you change your settings to use R1C1 notation, you could use the
following in place of your CSV file above

10,20,30
20,30,40
=AVERAGE(R[-2]C:R[-1]C),=AVERAGE(R[-2]C:R[-1]C),=AVERAGE(R[-2]C:R[-1]C)

 

[Earl Kiosterud]

kk,

You could use this little nested mess:

=AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,0,2,1))"

There has to be a nicer way, but I'm out of coffee.

Be sure to include the quotes in your csv. I suspect you'll get a mess without them if your
system uses commas as field delimiters (unless your regional Settings are other than US, and
even then, I think CSV expects commas (it's namesake). Not sure.

 

[Harlan Grove]

You could use this little nested mess:

=AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,0,2,1))"

There has to be a nicer way, but I'm out of coffee.

....

You mean like

=AVERAGE(INDIRECT("R[-2]C:R[-1]C",0))

?

 

[Earl Kiosterud]

-----------------------------------------------------------------------

You could use this little nested mess:

=AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,0,2,1))"

There has to be a nicer way, but I'm out of coffee.

...

You mean like

=AVERAGE(INDIRECT("R[-2]C:R[-1]C",0))

?

Yeah, like that. It'll probably have to be written in a CSV as:

"=AVERAGE(INDIRECT(""R[-2]C:R[-1]C"",0))","=AVERAGE(INDIRECT(""R[-2]C:R[-1]C"",0))"

 

[kk]

Thank you very much for your answers!
I used the R1C1 notation and the formulas works like a charm!
About the quotations, i used the ";" character as a delimiter and I
used quotations to define a formula in the following way:

10;20;30;"=AVERAGE(RC[-3]:RC[-1])";"=IF(RC[-1]<R1C[-1];1;0)"

Thank you again for your answers!