Numbers : matrix formula ??

Question

Level 1

4 points

Numbers : matrix formula ??

Hello to all,

by asking on an Excel forum, a person found me the solution to the problem below. However, it is an Excel solution with a matrix formula so validate with Ctrl+Shift+Enter

By transferring this formula to numbers, it does not work, even by validating with Ctrl+Shift+Enter. Do you have an idea?

here is the formula in excel: {=INDEX(DECALER($C:$C;0;EQUIV($J$3;$C$2:$G$2;0)-1);EQUIV(L3&I3;$A:$A&$B:$B;0))}

under Numbers it doen'st work??? and how do we validate it?

reminder of my problem

I have criteria to define the surface (of a house). Example : a house with 2 rooms (criteria from 1 to 5 rooms) that can accommodate 4 people (criteria from 1 to 20). I then add my "category" criterion: according to the chosen category (from 1 to 5) the minimum surface of the house is not the same.

This gives me a house of 2 rooms AND for 4 persons AND for a category of 3 = a nb present in the category, here "25". I put you the screenshot, I think you'll see right away "what I'm talking about (!)".

Currently, I do it manually by adding conditional formatting for the nb of pers. and piece to simplify the reading but with a formula that couples the categories, the idea would be to automate and avoid reading errors.

thanks

--

Bonjour à tous,

en demandant sur un forum Excel, une personne m'a trouvé la solution au problème ci-dessous. Par contre, c'est une solution Excel avec une formule matricielle donc valider avec Ctrl+Maj+Entrée

En reportant cette formule sous numbers, cela ne fonctionne pas, même en validant avec Ctrl+Maj+Entrée. Avez-vous une idée ?

voici la formule sous excel : {=INDEX(DECALER($C:$C;0;EQUIV($J$3;$C$2:$G$2;0)-1);EQUIV(L3&I3;$A:$A&$B:$B;0))}

sous Numbers??? et comment on la valide ?

rappel de ma problèmatique

je dispose de critères pour définir la surface (d'une habitation). Exemple : une maison de 2 pièces (critère de 1 à 5 pièces) pouvant accueillir 4 personnes (critères de 1 à 20). J'ajoute ensuite mon critère "catégorie" : selon la catégorie choisie (de 1 à 5) la surface minimale de la maison n'est pas la même.

Ce qui me donne une maison de 2 pieces ET pour 4 pers ET pour une catégorie de 3 = un nb présent dans la catégorie, ici "25". Je vous mets la capture d'ecran, je pense que vous verrez de suite de "quoi je parle (!)".

Actuellement, je le fais en manuel en ajoutant des mises en forme conditionnelles pour le nb de pers. et de piece pour me simplifier la lecture mais avec une formule qui couple les catégories, l'idée serait d'automatiser et éviter les erreurs de lecture.

Mac mini, macOS 10.15

Posted on Jul 26, 2022 8:32 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,858 points

Jul 27, 2022 2:25 PM in response to APocc11

Your spreadsheet is very Excel-like. Excel puts everything in one large table. Numbers is built for breaking tables up into separate tables that can be arranged on the sheet however you wish. The example below is based on your spreadsheet but with it broken into 3 tables. Also below is the formula that fits with your table as it is now.

The only formula is for the result in Table 3. It is

=MAXIFS(OFFSET(Data::C;0;Selection::B2−1);Data::B;Selection::A2;Data::A;Selection::D2)

With your tables all as one table the formula would be

=MAXIFS(OFFSET(C;0;J3−1);B;I3;A;L3)

The formula will return numeric values as results (because it uses MAXIFS, a numeric function). It will not work if the results are text. Luckily your results are numeric.

I am wondering, though, if there is a formula for calculating this result rather than having to look it up in a table.

Reply

Answer 2

SGIII

Level 8

37,562 points

Jul 26, 2022 9:14 AM in response to APocc11

As you probably know Numbers does not support most so-called "array" formulas. But there are usually workarounds.

It would be most helpful if you could post a screenshot showing what you are trying to do: shift-command-4, select the area, release, start new post here and use the 'mountains-and-moon' Image Insertion icon to insert the screenshot from the Desktop.

DECALER is OFFSET in English, EQUIV is MATCH.

SG

Reply

Answer 3

SGIII

Level 8

37,562 points

Jul 28, 2022 3:55 AM in response to APocc11

APocc11 wrote:

Numbers tells me that it doesn't recognize the MAXIFS formula. Is there a specific command to validate the result or did I make a mistake?

Is your language still set to French, as in your original post? If so, then Numbers will not recognize a function name in English. You need to use SISMAX instead of MAXIFS. And DECALER instead of OFFSET.

As I posted above, would you be kind enough to explain why 25 is the expected result in your original example? I'm not sure I'm following the logic.

SG

Reply

Answer 4

APocc11 Author

Level 1

4 points

Jul 28, 2022 3:37 AM in response to Badunit

Thank you for the help, however, if I do as you do, either with three tables or a single table, making sure that all the cells are in numeric format and that the result is in "numeric system" or "number" format, it doesn't work. Numbers tells me that it doesn't recognize the MAXIFS formula. Is there a specific command to validate the result or did I make a mistake?

thank you for your help

Reply

Answer 5

APocc11 Author

Level 1

4 points

Jul 28, 2022 4:51 AM in response to SGIII

ok I'll test right away, thanks. And sorry, it's a mistake on my part indeed, it's not 25 but 35.4 your logic was right. Originally I wanted to explain my problem with different numbers and I put the old screenshot and forgot to update the result. Hence my desire to automate this result. Thanks, I'll test and answer you right after. Thanks again for your help

Reply