MATCH from right to left
 

Hello,

I have a row, which is populated with a few "X" letters.
Dependent where an "X" is located I'd like to do a calculation.

Example:

A B C D E F G
X X X X

Now I make a calculation in "G2", which uses values out of the last
column
with an "X", in my case out of column "E".
How can I determine the offset of -2 columns from G to E?

Regards
Werner

Answer: MATCH from right to left
 

Hi Werner,

To determine the offset of -2 columns from G to E, you can use the

Formula:

---

MATCH 


---

function in Microsoft Excel. The MATCH function searches for a specified value in a range of cells and returns the relative position of that value within the range.

Here's how you can use the MATCH function to find the offset of -2 columns from G to E:

1. Select cell G2 where you want to enter the formula.
2. Type the following formula:
   Formula:

   ---

   =MATCH("X",$A$1:$F$1,-1) 


   ---

3. Press Enter.

This formula searches for the value "X" in the range $A$1:$F$1 from right to left (-1). The MATCH function returns the relative position of the last "X" in the range, which is 4 in this case.

1. Subtract 2 from the result to get the offset of -2 columns from G to E. In this case, the offset is 2.

So, to reference the last "X" in column E from cell G2, you can use the following formula:

Formula:

---

=E2-offset 


---

, where "offset" is the result of the MATCH function minus 2. In this case, the formula would be:

Formula:

---

=E2-2 


---

.

MATCH from right to left
 

This will give you the column of that cell, but what do you want to do with
it?

MAX(IF(A1:F1="X",COLUMN(A1:F1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Werner Rohrmoser" wrote in message
...
Hello,

I have a row, which is populated with a few "X" letters.
Dependent where an "X" is located I'd like to do a calculation.

Example:

A B C D E F G
X X X X

Now I make a calculation in "G2", which uses values out of the last
column
with an "X", in my case out of column "E".
How can I determine the offset of -2 columns from G to E?

Regards
Werner

MATCH from right to left
 

Hi Bob,

thank you very much, I'm going to use it
for a formula which calculates the difference between
the production quantities of different fiscal years.
Because I have a few scenarios for one fiscal year I'd like to
be able to select a base scenario with an "X".

Regards
Werner

MATCH from right to left
 

So is what I gave you enough, or do you need it to get something else?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Werner Rohrmoser" wrote in message
...
Hi Bob,

thank you very much, I'm going to use it
for a formula which calculates the difference between
the production quantities of different fiscal years.
Because I have a few scenarios for one fiscal year I'd like to
be able to select a base scenario with an "X".

Regards
Werner

MATCH from right to left
 

It's enough, now I calculate the difference between the two columns
and use
the result in the OFFSET function to get the correspondent value,
which I
need for my calculation.

=(G2-OFFSET(G2,0,-COLUMN()+MAX(IF($A$1:F$1="X",COLUMN($A$1:F$1))),
1,1))/
OFFSET(G2,0,-COLUMN()+MAX(IF($A$1:F$1="X",COLUMN($A$1:F$1))),1, 1)

When I have an "X" in column "B", then it is equal to "= (G2-B2)/B2".

Or do you have a shorter solution?

Regards
Werner

MATCH from right to left
 

Hello Werner,

Shorter and non-volatile:
=(G2-LOOKUP(2,1/(A1:F1="X"),A2:F2))/LOOKUP(2,1/(A1:F1="X"),A2:F2)

Regards,
Bernd

MATCH from right to left
 

Hi Bernd,

that's cool!

Thanks.

Werner