Date corresponding to last value

hhenrion

Board Regular
Joined
Dec 19, 2005
Messages
73
Hi experts,

In range A1:A100 I have dates
In range B1:B100 I have values (a lot of them are zeros).
In range C1:C100 I have values (a lot of them are zeros).
In range D1:D100 I have values (a lot of them are zeros).

I try to get in cell B102 the date corresponding to the last non-zero value in range B1:B100 ...
Same for cell C102 with range C1:C100.
Same for D
...

Can you help ?

Thanks in advance,
Rgds,

H.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Richard

If I'm not mistaken the 2 in your formula means that the number must be >= .5? I think it should be the number that Aladin uses to write, 9.9999999999999e307.

Kind regards
PGC
 

hhenrion

Board Regular
Joined
Dec 19, 2005
Messages
73
Thanks & First non-zero value

Richard,

thanks a lot.

And to get the date corresponding to the first non-zero value ?

Rgds,

H.
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615

ADVERTISEMENT

=INDEX($A$1:$A$100,MATCH(TRUE,B$1:B$100>0,0))

Confirmed with Ctrl+Shift+Enter

HTH
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Kris

maybe <>0 instead of >0?. hhenrion doesn't tells us what kind of data it is. If it is financial data you could have negative values?

Cheers
PGC
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

If the values in the range are all either zero or positive then this seems to work (no CSE formula involved):

=INDEX(A1:A100,MATCH(0,B1:B100)+1)

Richard
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi PGC

The 2 seems to work fine for me - is it not for you?

Richard

PS I'm pretty sure I got it from an Aladin/Barry Houdini post anyway
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again Richard

The 2 seems to work fine for me - is it not for you?

No. I just tried your formula in a small range

=LOOKUP(2,1/(B1:B6),A1:A6)

I used in B1:B6 (1,2,3,0.1,0.2,0). The result is the value in A3 instead of the value in A5.

It makes sense, since you are comparing 2 to 1/B1:B6 and 1/0.1=10, 1/.2=5 are both bigger than 2 and so the lookup does not consider them.

Since you are using 2, the boundary value is 1/2=.5

That's why to catch all the numbers the formula should have instead of the 2 the biggest excel possible number.

Hope this makes sense.
PGC
 

Forum statistics

Threads
1,136,701
Messages
5,677,284
Members
419,684
Latest member
BOB101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top