Conditional cell update

[LibreUser_MC]

I have a spreadsheet where I’m tracking grocery purchases (excerpt attached) and trying to automate when the lowest price was paid and the last time it was purchased. Column A is a list of products; Columns B & C is the last time & Price paid for the item; Columns D & E is the date & lowest price paid for the item; Column F, G, H empty for future use; Columns I onward is the date and price paid.

When purchases are made on a new day, a new column I is inserted, date added to I2 and appropriate items updated with the price., resulting in cells B, C, D, & E being updated if applicable, otherwise remaining untouched. What conditional function tests can be used for cells in Columns B,C,D,and E to test if a cell in Column I is empty, then leave me alone. Otherwise, if the new price in Column I is not empty and is less than the price in Column E, replace the price in Column E and date in Column D.

In the attached spreadsheet I’ve separated each day into a separate sheet showing the expected outcome (i.e. manually overrode data in B,C,D,E as the formulas I used did not produce the output I was looking for.

Initially started with the following range definitions:
Min_Purchase_Price → IF($2022.I5<$2022.E5,$2022.I5,"")

Min_Purchase_Date → IF($2022.E5=$2022.I5,$2022.I$2,"")

Last_Purchase_Price → IF($2022.K5<>””,$2022.K5)

Last_Purchase_Date → IF($2022.I5=””,””,$2022.I$2)

Any help would be appreciated.
InflationTest.ods (15.5 KB)

[joshua4]

I think you are looking at the problem in a way that is correct, but not the way a spreadsheet usually works. Instead of thinking about what happens each time you insert a “new column I” rather think just about finding the location of the lowest price among all of the up to 31 columns I to AM then use the date value above that as the date of that event. The key formula I put to use is

=OFFSET($A$1, 1,COLUMN($I5:$AM5) + MATCH(SMALL($I5:$AM5,1),$I5:$AM5,0) - 2,1,1)

for the date, and just the core of this, SMALL($I5:$AM5,1), for the price itself.

You can also automate your Last Purchase and Last Purchase Date. I’ve done that in the sample ODS below. Notice that the formula in column C has to be entered as an array formula, so with Ctrl+Shift+Enter instead of just Enter. [I learned that array formula from ExcelJet, credit where due.]

Also, your use of Named Ranges to create mini-programs isn’t necessarily wrong, but it definitely is not a normal pattern for spreadsheets. Those could be removed in the sample ODS I’ve attached.

InflationTestAuto.ods (17.1 KB)

[LibreUser_MC]

@joshua4

Thank you so very much! I’ve been struggling with this for a while. I’ll have to read up on OFFSET, MATCH and ARRAYS. From what I can see playing around with the version you modified, it only works for the current fixed set of columns or rows. If I add a new Column I for 23Jan with butter @ $2.99 and eggs @ $1.99 the respective items’ MIN price doesn’t change - resolved by changing absolute reference to Column I. Other issues I found - copying and pasting the formula for new rows gets an error when the new rows have no data; adding new columns before Column A messes up the LAST & MIN dates ( no problem if new column added after Column A and won’t be an issue in the original file this subset was created from).

[joshua4]

Right about the relative reference for I. Relative references are just offsets, and that is what we wanted here. Good catch. My habit is to lock down as much as possible based on other habits. For example, I wouldn’t use column insertion to add data. It’s intriguing and slick in this particular case, but habit would be to add data to the right into existing columns and freeze columns A to E. I think this is based on the uncertainty of what happens to named ranges when adding to the first or last column or row, and usually I’d be naming the raw data as a named (absolute) range and referencing that for clarity. In fact, I used the offset from $A$1 (which causes the first-column-insertion issue) specifically because I was avoiding referencing from the top left corner of a named data range to keep the insertion idea.

I made your correction in the download so it won’t bother anyone coming along later who finds this from a search for a similar need.

[LibreUser_MC]

@joshua4

The updates you made doesn’t work when you add a new column and enter a lower price fort an item. I’ve been tweaking it by adding 3 empty columns before column B so that the test file better matches my main file. To handle purchases on a new date (i.e. add new column L) I used absolute referencing to Column K to anchor the start of the search. Also added error handing to leave the last/min date/price values untouched or ‘blank’ (formula only added down to row 13). However, for ‘empty’ min date/price items I end up with 1899-12-30 / 0.00 if I enter a price in the column before a date is entered in L2…
InflationTestAuto_V1.ods (14.8 KB)

[joshua4]

Again, right. I didn’t intend to update the question of inserting columns at the far left. Changing an anchor like that is standard practice. Just count the distance from whichever anchor you choose to the first column of your data and adjust the $A$1 to the new anchor and the offset by the count, or count-1 depending on how you count.

[schiavinatto]

Hello, @LibreUser_MC , here is a file with a suggested solution.
The cells in yellow have been changed.

InflationTest_GS.ods (20.5 KB)

Follow adjustment. The inclusion of new columns must occur after column I and before column R ( I > include < R ), in this way the formula remains valid.

InflationTest_GS_V2.ods (16.9 KB)

[LibreUser_MC]

@schiavinatto
Your solution shows there’s more than one way to solve a problem. I find yous a little easier to comprehend but there’s still an error in your formula when propogated down the column (formula only added down to row 13). I’ve been tweaking it by adding 3 empty columns before column B so that the test file better matches my main file. When a new Column L is added, LAST date/price get ‘#NA’ error and MIN date/price GET ‘1899-12-30/0.00’ in the empty items. I’ve added error handling which fixed LAST date/price but not having luck fixing MIN date/price.
InflationTest_GS_V1.ods (15.1 KB)

[schiavinatto]

See addendum in Answer above.