LibreCalc:Two Questions on Formula Manipulation

English
#1

Hi All,

Sorry if these two questions have been asked before.

As I’m finding it hard to describe what I’m trying to achieve by dent of being a very inexperienced operator, I can’t even figure out how to search here for my answers.

Here goes:

  1. I have a column of calculated figures which is rapidly growing very long (tall?). Each additional entry row has the updated “total” of my calculations in this particular column’s cell.

What I would like to achieve, is to be able to create a formula in a cell at the top of my sheet, that displays the current latest “running total” figure as it gets created. Much like a Sum of the numbers could be displayed (obviously easy to program this), except it’s a display of the content of a cell that’s moving further and further down the list away from where I can easily scroll to see it.

  1. When I highlight a row and I get the little drag point in the bottom right hand corner, if I drag the rows down they correctly auto update to amend the formulas on that row, by incrementing each value by 1.

That’s fine, except, I’d like to be able to hold one of the values in the formula as a constant. ie: one of the values, is a reference to a particular cell at the top of the sheet, that holds a “%” value that needs to be included in each line ie: say I type in 25 to the “Master %” cell (say D16) at the top of the sheet, I want that to be in the formula of each line. But each line that’s “dragged”, increments this value also (ie: D17, D18, D19 etc).

eg: =IF(E30=0,0,K28*D16%)
I want E30 and K28 to increment, but D16 to stay constant.

My only idea to hold cell D16 as a constant, is some kind of search and replace after I’ve drag created the additional rows.

I very much appreciate the help of anyone who might know how to solve these two questions. And sorry if I’ve duplicated on questions that come up often.

Regards.

CT

#2

Maybe something like =INDIRECT("D"&COUNT(D3:D10000)+2) where data starts at D3 and the data is unlikely to reach D10000. You could use D:D but you would need to know what rows above the start of data contained numbers.
RandomDebitCreditRunningTotal.ods (19.8 KB)

See Addresses and References, Absolute and Relative
So in this case you would include the reference to your percentage cell as =IF(E30=0,0,K28*$D$16.

Alternatively, you could click in cell D16, then in the Name Box (left of the formula bar) type something like Markup. You would then refer to that cell in formulas by that name, e.g. =IF(E30=0,0,K28*Markup)

.
Best if Cell D16 is either formatted as percentage and you enter 25 in it, or you enter 25% in the cell. It makes it clear that it is a percentage.

#4

Hey EarnestAI,

Thanks so much for the valuable help.

Absolute Addressing solved my second question perfectly. This is a great discovery for me. I’ll be able to make use of this function a lot. Learning all the time.

Thanks also for sending the “Running Totals” demo spreadsheet and instructions on how to use INDIRECT.

A couple of follow up questions if I may?

In the formula, D is in quotation marks. What is the reason for the quotation marks? I assume D refers to the whole of column D?

Then the & (ampersand) denotes what?

I have (I think) managed to use the formula you’ve shown me in my own spreadsheet, but with the one caveat that, as my cells in this column are themselves formulas, they mostly contain the number 0 whilst waiting to be engaged with the arriving input data, so that makes the formula return a 0 in the “display” cell at the top of the sheet. Even if I hide zeros across the whole spreadsheet, that produces an empty result in the cell I want as a monitor cell.

As a test to try and ignore cells with zeros, I tried COUNTIF and added a “>0” to the formula string, but I got an error.

Once again, I very much appreciate your help with this.

Regards.

CT

#5

You were right first time, use the comment bubble to reply, not Suggest a solution

I don’t know the spreadsheet, maybe change the formula to =IF(E30=0,"",K28*$D$16. The two double quotes represent a blank cell and won’t be counted by COUNT

  • the D is just text and as such it needs to be in quotation marks or you will get an error.
  • The COUNT(D3:D10000) counts the number of cells with a number in them between D3 and D10000. 0 is a number but "" isn’t. So you get a count of the number of cells, in the example 19, then you add the cells above (2) to give you a row number, 21
  • The ampersand concatenates the D and the 21 giving D21, but that is just a string, not a cell address
  • INDIRECT converts the string to a cell address so you get =D21

Modified to include Total calculation to row 40
RandomDebitCreditRunningTotal2.ods (20.3 KB)

1 Like
#6

Hi EarnestAI,

Sorry about that. I only did that, because I can’t work out how to get a comment to NOT remove all my formatting ie: all my line breaks get removed and it becomes an unreadable blob of dense text.

That did the trick!! Thanks. Another penny has dropped. Often reading the help, concepts don’t take hold without a concrete example to make things clear in one’s mind.

Before I respond (with my confusions of course) to the rest of this section, the file you posted appears to be a re-run of the original ie: only goes to Row 21, not 40 as described.

Regards.

CT

#7

Instead of looking up for the credit and debit values,
D4: =IF(AND(ISBLANK(B4);ISBLANK(C4));"";D3+B4-C4)

you can look up for the date:
D4: =IF(ISBLANK(A4);"";D3+B4-C4)

If do you replace D4 and down with =IF(A4<>"";D3+B4-C4), you will get 0s when there is no data, but could use =LOOKUP(2;1/(D:D<>0);D:D) to know the last non 0 cell of column D. (credits to @erAck).

A next step (for me):
Fill D4 and down with =IF(A4="";"";D3+B4-C4)
In D1: =LOOKUP(2;1/(A:A<>0);D:D); the search stops with the last date.
RandomDebitCreditRunningTotal3.ods (21.3 KB)

#8

Hey LeroyG,

Thanks for the alternative formulas. I won’t have a chance to get back to working on this until tomorrow now (UK time), so I’ll give these a go then, and let you know how I get on.

Thanks again.

Regards.

CT

#9

The Total column goes to row 40. Enter 172.06 in the Debit column, the running total will update as will the current total at the top of the column

#10

Hey EarnestAI,

Thanks. I see what you mean now.

Still working on adapting what you’ve shown me to my own spreadsheet.

I’ll get back when I (inevitably) have questions.

Thanks again.

CT

#11

Hey LeroyG,

As I’m keen to learn, I’m trying your formula out too.

One thing I don’t understand in this:

is the … (2;1/ … part.

What do the “2” and the “1/(” parts represent??

Thanks for your help.

Regards.

CT

#12

From the help of LOOKUP()

If there is no exact match between Search criterion and any of the entries in the search vector, LOOKUP matches the largest value in the search vector that is less than Search criterion.

So 1/ will force the values to be smaller than 1 (except for numbers between 0 and 1). Now you can search for any number greater than 1 to get “the largest value” as result.
.
Actually it was mentioned already above:

1 Like
#13

Hey Wanderer,

Thanks for helping out here.

As I’m still a total beginner, it’s interesting how subtle the concepts can get. Of course it should have been obvious what a 1/ meant. First level maths!! :grinning:

I think I’ve blundered though. Probably didn’t make it clear what I’m trying to achieve, and LeroyG responded to a badly enunciated thought.

I’m not looking to search for and report the largest number. What I’m trying to do is display in a cell at the top of the sheet, the last number in the column (lowest down the growing list) for further analysis work and general “at a glance” monitoring of the current state of the most up to date calculated value.

That’s because as the list grows really long and disappears out of sight, it’d be useful to have that most recent number from the list, visible back up where all the action is. Hoping that makes sense.

Do you have any insight into a formula that might achieve that? I’m still experimenting with Earnest and Leroy’s formulae, with very limited success so far.

Thanks again for your help.

Regards.

CT

#14

How does @LeroyG suggestion of =LOOKUP(2;1/(D:D<>0);D:D) not do what you want? See for example the attached file which has the list of numbers in A and the requisite formula in D1. A contains randomly calculated numbers; use Recalculate to get different values.
lastnum.ods (17.8 KB)

#15

Hey Robleyd,

Thanks for helping out. Much appreciated.

I’m gonna go with: because I’m a total doofus!!?! :grinning:

I’ve been playing around with trying to get the formula to work correctly in my sheet, but my total lack of skills is showing badly.

Your example sheet (as does Leroy’s) works perfectly, and I can see I need to change the (A:A<>0),A:A) reference cells to my equivalents, but when I do that, it doesn’t seem to work. I came to suspect my lack of understanding of the =LOOKUP(2,1/ part was my problem. I still don’t understand what the “2” is referring to.

Would you be able to see where I’ve gone wrong from a screenshot, or would a proper working copy of my sheet work better?

Screenshot 2025-01-24 at 12.14.08 pm
Screenshot 2025-01-24 at 12.14.08 pm1491×1513 331 KB

I’m trying to display the figure I want in the merged cells G11 and 12. I replace your (A:A<>0),A:A) references with my G column reference, but it doesn’t work. That’s where I confused myself over what role the (2,1/( was playing in this scenario.

Again sorry for my ignorance and thanks so much for your help.

Regards.

CT

#16

PS,

As Earnest originally pointed out, do I somehow need to exclude the rows G1-19 from the search, as I’m worried that there’s some kind of self-referential doom-loop potentially going on in there??

ie: you and Leroy have clean, uncluttered columns to work with.

CT

#18

If you have a problem with your car, do you take a picture of it to the mechanic?

1 Like
#19

Hey Robleyd,

Fair point.

Thanks again for your time. Much appreciated.

CT

CT Backtest - ATR 1h Variable Percentages V3.ods (61.2 KB)

#20

LOOKUP must search an array that is sorted. The result array can be different. See Spreadsheet Functions
.
The Date column is sorted in order and is the same length as the end of the data, so your formula should read =LOOKUP(2;1/(B:B<>0);G:G)

#21

Hi again Earnest,

Thanks for your reply.

Yes, I keep re-reading that section of the Help, but as I’m not experienced enough to be able to parse some of the sentences (don’t really understand what all the terminology means yet) I still have this one burning question you may be able to answer.

When the Help states:

Syntax

LOOKUP(Lookup; SearchVector [; ResultVector])

Lookup is the value of any type to be looked for; entered either directly or as a reference.

then what does the number “2” represent in our formula?? ie: =LOOKUP(2;1/(…

CT

#22

PS,

Interestingly, in Robleyd’s test sheet that he posted:

lastnum.ods (17.8 KB)

his numbers are random, so I would not have classed them as “sorted”, yet the formula works on his column.

I don’t know what to make of that. Still experimenting though.

Cheers.

CT

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.