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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
Thanks & First non-zero value

Richard,

thanks a lot.

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

Rgds,

H.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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
Back
Top