0

In this example which simplifies my data set, I have two tables representing months. I would like to be able to track the trend columns month on month.

Two excel tables

In plain language the question is

If "cats" has moved up from last month mark it as up, if "cats" has moved down mark it as down and mark if no change.

I would like to catch errors if there are new entries and I need a draggable generic formula.

Thank you all.

Improve this question
2
  • What have you tried? Why are there no trends for cats and sheep on your second table? Why is the the mice's trend on the second table shown as down instead of up?
    – Reddy Lutonadio
    Commented Jul 26, 2023 at 11:19
  • Thanks for the response, I am less concerned about the number of Mice, just that they have gone down from position 2 to 3. The real data represents contributors providing units of 'feedback'. It is more important to know who is providing the most v how many units. I have reverted to a more basic two-formula approach with something like =IFERROR(MATCH(B3,$G$2:$G$6,0),"New") And a conditional if to figure out if the position has changed from there. It is less than clean tho
    – Aaron
    Commented Jul 26, 2023 at 11:28

1 Answer 1

Reset to default
0

Based on the explanation from the comments, if you have Excel 365, try the following formula:

=LET(position1,IFERROR(MATCH(H1,$B$1:$B$5,0),0), position2,IFERROR(MATCH(H1,$H$1:$H$5,0),0),IF(OR(position1>position2,position1=0),"Up",IF(position1<position2,"Down","-")))

The positions are checked on both tables. They are then compared:

  • If position1 (position on the first table) is greater than position2 (position on the second table), Up is shown. If the type doesn't exist on the first table, the case is handled in the IFERROR part, and position1 is set to 0.
  • If position1 (position on the first table) is less than position2 (position on the second table), Down is shown.
  • If they are the same, - is shown.

Drag it down to fill the other cells.

Improve this answer
1
  • Absolutely immaculate, thank you!
    – Aaron
    Commented Jul 26, 2023 at 12:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .