If Formula & Symbol

Nicole Martin

Good day,

In our Company we use "Stages" to determine the quality/lead time of a new sale. We have 6 Stages. Stage one which is a potential client that has low intrest (cold Lead) and where stage 5 is a hot, almost sold client. Stage six is when the client bought one of our equipement and the order is successful. This is called our Sales Funnel.

Our director of the company wants a column added (Symbol: Up arrow, and down arrow). This needs to be an automatic column that changes when a sales rep changes the stage up or down, so that we can track on a report

, the clients that moved down.

What formula/automation/solution can I use to make the arrows change according to the updates?

Kind regards

Paul Newcome

You would need a unique ID on every row, and I would suggest a helper column that converts the Stage text string into a numerical value. From there you would use a Copy Row automation to copy the row over to a second sheet every time the stage changes. Next you would use an INDEX/COLLECT to reference the second sheet based on the unique ID and pull the second most recent change in. Finally you would use a nested IF statement to output the up or down arrows.

=IFERROR(INDEX(COLLECT({2nd Sheet Number Helper Column}, {2nd Sheet Unique ID Column}, @cell = [Unique ID]@row), COUNTIFS({2nd Sheet Unique ID Column}, @cell = [Unique ID]@row) - 1), [Number Helper]@row)

=IF(above_formula< [Number Helper]@row, "Down", IF(above_formula> [Number Helper]@row, "Up"))

=IF(IFERROR(INDEX(COLLECT({2nd Sheet Number Helper Column}, {2nd Sheet Unique ID Column}, @cell = [Unique ID]@row), COUNTIFS({2nd Sheet Unique ID Column}, @cell = [Unique ID]@row) - 1), [Number Helper]@row)< [Number Helper]@row, "Down", IF(IFERROR(INDEX(COLLECT({2nd Sheet Number Helper Column}, {2nd Sheet Unique ID Column}, @cell = [Unique ID]@row), COUNTIFS({2nd Sheet Unique ID Column}, @cell = [Unique ID]@row) - 1), [Number Helper]@row)> [Number Helper]@row, "Up"))