[Solved] Index, Match & CountA works then breaks

[misketti]

Grocery list export 17-02-10.ods

(83.02 KiB) Downloaded 133 times

I've created a list that includes grocery items and the price I pay for them each time I buy them. I want to pull out bits of this information. One of the bits I want is the "date last purchased". I am using an index/counta/match formula with some success, but I keep encountering random errors.

The basic goal of the formula is to count the number of items in the column and index it so I can match the corresponding date. I seemed to have it all working, so I copied it across the top of my range and got a whole bunch of correct answers, a whole bunch of (expected due to no data) #NA answers and a whole bunch of unexplained #NA answers. In addition to the unexplained ones, I find that columns that were giving correct answers start giving incorrect answers if I add new data. But not just ANY new data. If I add a 1, I might get an error, but if I add 4 I don't. And that changes depending on which column I add to.

I've attached the jolly thing since it is so hard to explain. I thought maybe it was because of gaps, but it seems weird that it would work fine in some cases and not others. I just can't see what the common denomiator is with the errors. I've done conditional formatting to highlight the errors I don't understand

Formula is this:
INDEX($A9:$UE$5001;MATCH(COUNTA(B9:B5001);B9:B5001;-1);1)

Any help would be great thanks. If I've done this wrong, then sorry - first post!

[FJCC]

If I follow you correctly,it seems some of the functions do not work the way you think they do. The formula in CM6 is

Code: Select all

=INDEX($A$9:$UE$5001;MATCH(COUNTA(CM9:CM5001);CM9:CM5001;-1);1)

Unpacking it from the inside out. The COUNTA() function returns 16, as I found by placing it in its own cell. The MATCH() function then becomes

Code: Select all

MATCH(16;CM9:CM5001;-1)

You get strange results because setting the third parameter to -1 tells MATCH that the column is in descending order but it isn't ordered at all. Also, you don't want to find 16, you want the 16th entry, which isn't what MATCH is looking for.
That the formula ever works is just blind luck. I can't think of a formula that will do what you want at the moment but I will think about it.

[MrProgrammer]

Hi, and welcome to the forum.

One of the bits I want is the "date last purchased". Formula is this:
INDEX($A9:$UE$5001;MATCH(COUNTA(B9:B5001);B9:B5001;-1);1)

• English: Find the largest date where the corresponding cell of the row has a value, but if no cells have data return "not available".
• Formula for B6: =IF(COUNT(B9:B5001);MAX(IF(ISBLANK(B9:B5001);"";$A9:$A5001));NA()).

Type the formula and press ⇪⌘Enter (Shift+Command+Enter) on a Mac or Ctrl+Shift+Enter on other platforms; if you press Enter by mistake, use Edit → Undo and try again. Copy that formula from B6 to C6:TF6 using Edit → Copy, Edit → Paste.

If I follow you correctly,it seems some of the functions do not work the way you think they do.

You'll want to review Calc Functions listed by category to learn about MATCH, ISBLANK, NA, MAX, etc. and Using Arrays to learn about when to use Command/Ctrl+Shift+Enter.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[misketti]

That the formula ever works is just blind luck.

I had my suspicions about that! Nothing I read made it clear why it would work in some instances and not in others, so I wondered.

Hi, and welcome to the forum...

Thanks!
I will give this a try when I get some more spare time and will read up on arrays. I've used a few, but not often. I'm using Excel at work, but OO at home, so trying to blend a bit of knowledge where possible.

I will update with solved once I've tried your suggestion.

[misketti]

• Formula for B6: =IF(COUNT(B9:B5001);MAX(IF(ISBLANK(B9:B5001);"";$A9:$A5001));NA())

Works perfectly! Copied and pasted at first then re-read the instructions and typed it in... I understand it too, which is also quite handy Now to mark as solved...