Finding last value greater than 0
 

In A1:L1 I have January through December
In A2:L2 I have --

200 -- A2
0 -- B2
150
225
0
0
300
400
0
600
0
0 -- L2

To find the first month in which sales are greater than 250 I use this
formula entered as an array - index(A1:L1,match(true,A2:L2250,0))

What formula can I use to find the last month in which sales were greater
than 0, in this example it will be October (600 units)?

Can I modify my existing formula?

Thank you for any help!

Ted

Finding last value greater than 0
 

One way

=INDEX(A1:L1,MAX((A2:L20)*(COLUMN(A2:L2))))

entered with ctrl + shift & enter

note that column will always count from column A so if your values should
start in C2 going to N2 you should still start with A1 in the INDEX part or
offset the result of the max formula by - 2, but in your example it will
work unattended


--

Regards,

Peo Sjoblom



--

Regards,

Peo Sjoblom

"Ted Metro" wrote in message
...
In A1:L1 I have January through December
In A2:L2 I have --

200 -- A2
0 -- B2
150
225
0
0
300
400
0
600
0
0 -- L2

To find the first month in which sales are greater than 250 I use this
formula entered as an array - index(A1:L1,match(true,A2:L2250,0))

What formula can I use to find the last month in which sales were greater
than 0, in this example it will be October (600 units)?

Can I modify my existing formula?

Thank you for any help!

Ted

Finding last value greater than 0
 

Thank you Peo!

"Peo Sjoblom" wrote:

One way

=INDEX(A1:L1,MAX((A2:L20)*(COLUMN(A2:L2))))

entered with ctrl + shift & enter

note that column will always count from column A so if your values should
start in C2 going to N2 you should still start with A1 in the INDEX part or
offset the result of the max formula by - 2, but in your example it will
work unattended


--

Regards,

Peo Sjoblom



--

Regards,

Peo Sjoblom

"Ted Metro" wrote in message
...
In A1:L1 I have January through December
In A2:L2 I have --

200 -- A2
0 -- B2
150
225
0
0
300
400
0
600
0
0 -- L2

To find the first month in which sales are greater than 250 I use this
formula entered as an array - index(A1:L1,match(true,A2:L2250,0))

What formula can I use to find the last month in which sales were greater
than 0, in this example it will be October (600 units)?

Can I modify my existing formula?

Thank you for any help!

Ted