[Solved] Adding a loop function to "spreadsheets"

Talk about anything at all....
Post Reply
Mexicanpete
Posts: 12
Joined: Sun Oct 04, 2020 10:32 am

[Solved] Adding a loop function to "spreadsheets"

Post by Mexicanpete »

 Edit: Split from [Solved] Conditional IF query 
Thanks once again.

It is a very long (and I mean very long) time ago that I wrote a couple of programs where a line had an index variable (say I) and the statement began something like Step (I=1 to 25) followed by a function containing I and some conditional statements.

I'm sure this can be implemented as a macro but I was hoping that a single cell function might be available that does something similar.

What I'm trying to to is to take some action in the current row that is based on data contained in rows ahead of the current row but behind the current column, where the target row is an unknown amount ahead but the range is limited. Hope that makes sense. It is a project I've been meaning to do for quite a while but have only just decided to sort it out. I now have 8 years of data that needs correcting in order to be useful!

Peter
Last edited by MrProgrammer on Wed Jun 08, 2022 3:45 am, edited 2 times in total.
Reason: Tagged ✓ [Solved] since Mexicanpete says "problem solved" -- MrProgrammer, forum moderator
Open Office version 4.1.7 on Windows 10 pro 64
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Conditional IF query

Post by Villeroy »

A spreadsheet is a simplified visual programming language for non-programmers, therefore it must not loop. Otherwise too many spreadsheets would be caught in frustrating endless loops. Spreadsheets are popular because it is very easy to get some result (even a wrong one).
Each formula refers to one set of referenced values but you can copy formulas with relative references so you get a finite set of calculation results

C1: =A1+B1
...
copied down
...
C1000: =A1000+B1000

there are many ways to copy/drag/fill down/up/right/left some formula or a sequence of numbers.

In order to show numerc values 1/0 as booleans I would format them as booleans or define a user-defined number format showing Yes/No, Y/N or whatever.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
keme
Volunteer
Posts: 3692
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Conditional IF query

Post by keme »

What I'm trying to to is to take some action in the current row that is based on data contained in rows ahead of the current row but behind the current column, where the target row is an unknown amount ahead but the range is limited.
What you have in mind can most likely be achieved, perhaps using MATCH(), SUMIF(), COUNTIF() or some other lookup or conditional function. Without more specific info about your data and how it needs to be organized, it is impossible to provide more specific advice.

In some cases consolidation, a filter or a pivot table can also be of use for structuring a dataset. Not sure that it will be useful in your setting, but I thought it worth mentioning anyway.

Look into those tools, and don't hesitate to ask again if you are stuck.
Mexicanpete
Posts: 12
Joined: Sun Oct 04, 2020 10:32 am

Re: Conditional IF query

Post by Mexicanpete »

Thanks to the respondents for their comments.

Having been involved with programming both professionally and privately for just under 55 years I understand fully that the primary aim of a spreadsheet is to provide relatively simple formulae to process tables of data. I have recently embarked on a project to eliminate the need for a Microsoft's Office suite as every time I upgrade my computers I have problems with getting them going again, not to mention unrequested updates that change the way a program works or the obsolescence of a particular version . Once I'm fully familiar with finding my way around Open Office with the help of contributors to this forum, I'm going to look at macros.

There is intrinsically no reason why spreadsheet cell functions cannot include loops for technically more advanced users, although I fully take your point that such a function might end up in an endless loop. Endless loops can occur anywhere not just in spreadsheets.

I have the statement I require written for a lookup of four rows ahead and from this I have estimated that the statement I require will be in excess of 300 characters. Such a length makes proof reading very difficult and hence my thought that because of its structure, it could be compressed into a relatively short indexed loop. Clearly this function is not currently directly available.

Once again thanks to everybody.

Peter
Open Office version 4.1.7 on Windows 10 pro 64
User avatar
keme
Volunteer
Posts: 3692
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Conditional IF query

Post by keme »

Can you not share your data structure?

Would you care to show us your formula as you have it working for 4 rows? That may be the clue we need to provide advice with some substance.

For now, you are keeping us guessing (providing nothing of your data layout and only rudimentary formula), which is not very motivating.
Mexicanpete
Posts: 12
Joined: Sun Oct 04, 2020 10:32 am

Re: Adding a loop function to "spreadsheets"

Post by Mexicanpete »

Hi Keme and thanks for your response.

Basically I didn't want to overburden people who provide free help and guidance out of a sense of Community. I'm still learning much about the details of of using open office.

However I'd welcome suggestions as to how to approach the problem I'm trying to solve. I have 8 years of data of my electricity and gas usage and I want to plot these as 8 graphs on a comparative year by year monthly usage basis. The graphing has been sorted.

The source of the problem is that there are quite a few gaps in the data ranging from a couple of days to several weeks depending on holidays, visits to relatives etc. What I want to do is to linearly interpolate between the known points. So the data looks like this


Date--------reading------ interpolated reading
Jan1st-------- 56---------------56
Jan 2nd
Jan 3rd
Jan 4th
Jan 5th--------6----------------63

The problem is to complete the interpolated reading column. There are close on 4000 rows with a large number of gaps which vary from one row up to about 30 rows. There is some usage during the periods with absent readings.

My own solution is pretty messy, but as far as I can see if you need to look ahead an unknown number of rows to obtain the end points for the interpolation, it is bound to be untidy. Untidy maths and programming are error prone which is why I wondered if it could be simplified through the use of a suitable advanced formula and hence my enquiry.

Looking forward to your observations.

Peter
Open Office version 4.1.7 on Windows 10 pro 64
User avatar
keme
Volunteer
Posts: 3692
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Adding a loop function to "spreadsheets"

Post by keme »

Assuming that linear interpolation is a "fair guess", that I understand your readings, and that your data is in columns A - C (B: actual reading, C: interpolation).

C2: =IF(ISBLANK(B2);(C1+C3)/2;B2)

Copy that down column C.

There will be circular references, so you need to allow for that. This is the only "looping" available to spreadsheet formulas.

Tools - Options - Calc - calculate.
Set options for iterative references.
User avatar
MrProgrammer
Moderator
Posts: 4883
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Adding a loop function to "spreadsheets"

Post by MrProgrammer »

What direction do you want to take this topic? If you want to continue discussion about looping constructs, an idea which is contrary to spreadsheet fundamentals, continue here. Just understand that this is a user-to-user forum. No developers are going to see this dialog. It might become an interesting General Discussion topic, but these ideas are not going to influence the direction that Calc takes.

However, since you seem have a specific spreadsheet which you would like help with, I would be happy to see what I can offer if you create a new topic for that spreadsheet in the Calc forum and attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Clearly state your goal; indicate which column of which sheet holds the data of interest so that I don't need to guess. Linear interpolation with variable length gaps seems fairly simple using simple Calc formulas, but I want to see your actual data layout and I won't reply without an ODS attachment. I don't need to see 4000 rows of data and the forum limits attachments to 128K. A few dozen rows of representative data will do as long as they show some gaps. I believe you are having trouble because you are focused on your notion of a looping solution. That isn't the correct approach in a spreadsheet. We have other methods which accomplish what one does with loops in traditional programming languages.
XY Problem
 Edit: My design for linear interpolation would have been similar to the document below from Zizi64 
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Adding a loop function to "spreadsheets"

Post by Zizi64 »

Date--------reading------ interpolated reading
Jan1st-------- 56---------------56
Jan 2nd
Jan 3rd
Jan 4th
Jan 5th--------6----------------63
Is the number --6-- a typo, is not? That is really 63 too?

Please upload a real, .ods type sample file here to show the real structure of your spreadsheet.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Adding a loop function to "spreadsheets"

Post by Zizi64 »

The source of the problem is that there are quite a few gaps in the data ranging from a couple of days to several weeks depending on holidays, visits to relatives etc. What I want to do is to linearly interpolate between the known points.
Here is my sample file:
Linear interpolate in various ranges.ods
(14.03 KiB) Downloaded 198 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Mexicanpete
Posts: 12
Joined: Sun Oct 04, 2020 10:32 am

Re: Adding a loop function to "spreadsheets"

Post by Mexicanpete »

Once again thanks to everybody for their time and support.

Replying to Mrprogrammer first. I fully understand that spreadsheets are a simplified programming structure. Searching for the functionality of a function if you know its name is easy; looking for the name of a function when searching by its functionality is very hit and miss.

I know I can probably fairly easily do what I'm trying to achieve with a function that allows looping. What I didn't know when I started this post was whether such a function exists. From the comments so far I guess it doesn't. This is a positive result as I'll not use any more time looking for one.

When I was getting up to speed on the graphing I wanted to do, I downloaded a short manual on the subject from the Open Office website. I quickly got badly stuck a couple of times and it turned out that the rather elementary information I needed was not included in the manual. People on this forum were good enough to help and problem solved. Hence my reason for asking about looping functions.

Thanks Zizi64 for your demonstrator ods file. You are correct regarding the typo and I'm sorry for any confusion.

The only way I can see that this works is if Column E steps from High to Low rather than the more conventional low to high. D column clearly steps from low to high. I assume that the scanning is therefore column by column rather than row by row low to high. This is also presumably why your helper row in column E is at the high end rather than the low.

I did actually search for altering the calculation order when I started but found nothing hence my thoughts about loop functions.

So hopefully you'd be kind enough to share how you set the order of the stepping (eq row by row or column by column and high to low or low to high). Ingenious.

Peter
Open Office version 4.1.7 on Windows 10 pro 64
Post Reply