Bug 45372 - Provide a WALKUP() function
Summary: Provide a WALKUP() function
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.4 release
Hardware: All Linux (All)
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-01-29 08:15 UTC by Terri
Modified: 2015-08-22 04:27 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
Shows formulas now and hints at what a walkup function would look like. (15.56 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-01-29 09:03 UTC, Terri
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Terri 2012-01-29 08:15:12 UTC
I would like to propose a walkup function.  The purpose of the function is to walk up a column testing for a value that is displayed.  When it finds a displayed value, it returns the row.
   Spreadsheets originally tried to mimic a ledger.  A ledger uses a few columns on the left that have identifying info like, a date, a description, and a dollar amount of the purchase or expense. The columns to the right of those normally indicate various logical accounts with the effect of the transaction on that particular account. An entry is made in the transaction amount in the first set of columns and then in the appropriate column, same row, showing how much is added/subtracted from that particular account.
   This leads to a very clean and easily understood array of cells that join a particular credit/debit (the row) to a particular account (the column).  
   Since each entry in an accounts column is dependent on the last balance for that account, anyone maintaining a spreadsheet has to constantly update the cell references to ensure each transaction is properly posted using the last previous balance of that particular account or column.  
   The function would have a single argument, the column to walk up.  The row would be assumed to be the row value of the cell calling the function.  It would walk up that column from the cell's row, subtracting 1 from the row each time until it found the last value of the account from the last transaction.  It then would return that value.  If an intervening row were inserted and a new transaction affecting that account were put in that new row, the next recalculate would deliver that new row value of that column.  
   In that way, the running value of that account is maintained, all the way down the spreadsheet regardless of changes made above.
   Enhancements for later might be to allow an argument using a different row or column.  But I think that is for later.   I will provide more visuals upon request.  I am going to work on a simple sample spreadsheet demonstrating the idea which I'll add as an attachment.
Comment 1 Terri 2012-01-29 09:03:16 UTC
Created attachment 56295 [details]
Shows formulas now and hints at what a walkup function would look like.

A walkup function could simplify this type of spreadsheet greatly, by allowing a pulldown copy of a canned formula at the top instead of manually tweaking all the cell references every time a row is deleted or inserted.
Comment 2 David Fischer 2015-08-22 04:27:35 UTC
"The purpose of the function is to walk up a column testing for a value that is displayed.  When it finds a displayed value, it returns the row."

This sounds exactly like the MATCH() function to me.

I am going to mark this bug as Resolved and NotABug since I believe the functionality you are looking for already exists.(In reply to Terri from comment #0)
> I would like to propose a walkup function.  The purpose of the function is
> to walk up a column testing for a value that is displayed.  When it finds a
> displayed value, it returns the row.
>    Spreadsheets originally tried to mimic a ledger.  A ledger uses a few
> columns on the left that have identifying info like, a date, a description,
> and a dollar amount of the purchase or expense. The columns to the right of
> those normally indicate various logical accounts with the effect of the
> transaction on that particular account. An entry is made in the transaction
> amount in the first set of columns and then in the appropriate column, same
> row, showing how much is added/subtracted from that particular account.
>    This leads to a very clean and easily understood array of cells that join
> a particular credit/debit (the row) to a particular account (the column).  
>    Since each entry in an accounts column is dependent on the last balance
> for that account, anyone maintaining a spreadsheet has to constantly update
> the cell references to ensure each transaction is properly posted using the
> last previous balance of that particular account or column.  
>    The function would have a single argument, the column to walk up.  The
> row would be assumed to be the row value of the cell calling the function. 
> It would walk up that column from the cell's row, subtracting 1 from the row
> each time until it found the last value of the account from the last
> transaction.  It then would return that value.  If an intervening row were
> inserted and a new transaction affecting that account were put in that new
> row, the next recalculate would deliver that new row value of that column.  
>    In that way, the running value of that account is maintained, all the way
> down the spreadsheet regardless of changes made above.
>    Enhancements for later might be to allow an argument using a different
> row or column.  But I think that is for later.   I will provide more visuals
> upon request.  I am going to work on a simple sample spreadsheet
> demonstrating the idea which I'll add as an attachment.

This sounds exactly like the MATCH() function to me.

I am going to mark this bug as Resolved and NotABug since I believe the functionality you are looking for already exists.