Updating Data in Calc

users
#1

Hello:

Using LO vers 7.1.3.2 (x64) on desktop Windows 10 Home (64-bit).

I would greatly receiving an explanation of a formula or function in Calc that updates the data within cell $Y$4 each time the data in column [M] changes.  The formula in $Y$4 =$M18-$W$4.

[M]                                        [Y]

Row 18 ---------------------->  Row 4
Row 19 ---------------------->  Row 4
Row 20 ---------------------->  Row 4
Row 21 ---------------------->  Row 4

TIA;

VinceB.

#2

Hi Vince,

Hello:

Using LO vers 7.1.3.2 (x64) on desktop Windows 10 Home (64-bit).

I would greatly receiving an explanation of a formula or function in Calc that updates the data within cell $Y$4 each time the data in column [M] changes.  The formula in $Y$4 =$M18-$W$4.

[M]                                        [Y]

Row 18 ---------------------->  Row 4
Row 19 ---------------------->  Row 4
Row 20 ---------------------->  Row 4
Row 21 ---------------------->  Row 4

  Where is column W in your layout? The below is done on assumption.

A simple sum will do it i.e.:

In cell Y4 insert everything between the "

"=SUM(M18-$W$4)"

In cell Y5

"=SUM(M19-$W$4)"

In theory you could just drag the formula from cell Y4 down to the bottom and then just change those M reference cell numbers.

Remember that putting a $ before either a letter or number reference of a cell LOCKS that formula i.e. dragging the formula down a column to populate cells below will keep whatever is prefixed with $.

Hope it helps
Hylton

#3

Hi Hylton:

Thank you, OM, for giving this problem some thought; I do appreciate your suggestion/comments.

I understand your approach in your suggested solution; however, it does not satisfy my requirements. The numerical data that is entered, or calculated in this situation, within the rows of column [$M] must be reflected, overtime, only at cell $Y$4.  (I may later need to assign a name for cell Y4; something like: /Predicted Remaining Fuel (Gals)/ for use in other, future, calculations).

Perhaps I should have written in my OP that "The formula /presently/ in $Y$4=$M18-$W$4". I expect that that formula, obviously,must be changed to reach a solution to my problem, but "=SUM(M18-$W$4)" does not do it for me. Please note that the updated/reflected numerical data must be displayed only within cell Y4, and not elsewhere. Sorry for any confusion that I may have caused.

I have other Calc spreadsheets that require this same type of numerical data updating.  In 2020 Jan/Feb, I received a workable solution to one of my (bowling scores) spreadsheets; IIRC, it invoked the =INDEX( ) function, i.e., /=INDEX($'2020-21 WINTER SR LEAGUE'.G6:$'2020-21 WINTER SR LEAGUE'.G41,COUNTA($'2020-21 WINTER SR LEAGUE'.G6:$'2020-21 WINTER SR LEAGUE'.G41),1)/, but that has somehow gotten messed up by me and no longer works (another story, which I may soon write about in another thread). I mention =INDEX( ) here because I feel that function, which I still do not understand, may be needed to solve my Y4 updating problem.

Thanks again, OM. 73 de WA2RSX

VinceB.

#4

I would greatly receiving an explanation of a formula or function in Calc that updates the data within cell $Y$4 each time the data in column [M] changes. The formula in $Y$4 =$M18-$W$4.

Generally speaking, this process is automatic in spreadsheets, of course. If you change the values in cells that are referenced in formulae elsewhere, the result of those formulae will immediately change.

Perhaps I should have written in my OP that "The formula /presently/ in $Y$4=$M18-$W$4". I expect that that formula, obviously, must be changed to reach a solution to my problem, ...

That formula was the only hint anyone had as to what you are trying to achieve. If, as you now say, it does not define your need, there is absolutely no indication so far in what you have given for anyone to go on. You are seeking a solution to a problem you have not been prepared to state. If you put some formula in Y4 (in this context it makes no sense to call it "$Y$4") that depends on (some? all of the?) values in column M, its result will change when those source values change.

Whether you need
=SUM(M1:M99)
or
=MAX(M1:M99)
or
=VLOOKUP(M4,X1:Y10,2,FALSE)
or
=IF(SUM(M1:M99)>0,"Hooray: I'm solvent!","Shucks, I'm bankrupt.")
or one of a thousand other possibilities we can only guess.

I trust this helps.

Brian Barker

#5

I'm often puzzled by suggestions such as this. In what way do you think that =SUM(Xm-Yn) differs from =Xm-Yn? The minus sign effects the subtraction between two values, generating a single value as its result. You then pass this single value to the SUM() function. What do you expect that function do with a single value? The sum of two and two is four; what do you think is the sum of just two?

Brian Barker

#6

Dear Brian,

A simple sum will do it i.e.: In cell Y4 insert [...]
=SUM(M18-$W$4)

I'm often puzzled by suggestions such as this. In what way do you think that =SUM(Xm-Yn) differs from =Xm-Yn? The minus sign effects the subtraction between two values, generating a single value as its result. You then pass this single value to the SUM() function. What do you expect that function do with a single value? The sum of two and two is four; what do you think is the sum of just two?

I too was puzzled by the OP post however I assumed that it was the function that the OP was seeking, consequently my answer included the SUM option. The two formulae above do indeed calculate the same thing, but as you can see from the OP reply, he perhaps wanted a more Index(Match()) query.

I will leave the explanation of the different parts of whatever function he chooses to someone who has knowledge of such.

It has often puzzled me why when knowledgeable members of mailing lists such as this, find fault in what someone, less qualified than themselves, suggests. It is not taken via private mail but splashed on the mailing list.

I have been very thankful for your help in years past, and despite an endeavour to understand the Index/Match formula I am still battling with what cell selections go where in the formula. Perhaps in showing the OP verbosely you will allow both him, I and perhaps a few others also understand the formula, if you think it is the one needed.

I, and many others, look forward to your continued support.

Warmest Regards
Hylton

#7

Don't take it personally. It's just a quirk of list netiquette. It's
considered rude to post a private message to somebody as a result of a
list postings, unless specifically invited. Plus by posting it to the
list Brian makes sure that any other people who were also in any doubt
about what had gone before also have the same new information to
clarify their thinking. So there's no criticism of you intended.

He perhaps could have chosen slightly better phrasing to avoid
irritating you, but such events are part of life on a mailing list,
where not everybody has perfect skills in written English plus a
considerate personality.

HTH, Dave

#8

List netiquette quirk noted, apologies to Brian.

Hylton