Line Chart Issue

[Steve 1962]

Hi

I'm attempting to show the last value in column D (cell D5 in this case) as an output in D9 (D9 needs to capture the last value in the column).

Obviously the #N/A from cells D6 to D8 are causing the result however, I need the #N/A to be there when there is no value in column A. The reason for the #N/A in column D (cells 1 to 8) is that it is driving a line chart on another worksheet and the #N/A is required to cutoff the line in the chart with the last value because if column D did not show #N/A and instead showed a blank or zero, the chart line would dip abruptly from its last value to zero. I am trying to avoid this abrupt dip in the chart at the last value by showing #N/A in column D but unfortunately it also destroys the last value output that I need to also show in cell D9.

Two objectives required - 1) Last value of column D to be shown in cell D9 and 2) column D able to drive a line chart but not show an abrupt dip in the line when there is a null value.

Thanks and open to suggestions.

Book1
	A	B	C	D
1	22/06/2023	20	16	320
2	23/06/2023	23	17	391
3	24/06/2023	25	18	450
4	25/06/2023	27	62	1674
5	26/06/2023	29	42	1218
6				#N/A
7				#N/A
8				#N/A
9				#N/A
Sheet5

Cell Formulas
Range	Formula
D1:D8	D1	=IF(A1="",#N/A,(B1*C1))
D9	D9	=INDEX(D1:D8,MAX(ROW(D1:D8)*(D1:D8<>"")))

 

[Fluff]

How about

=LOOKUP(2,1/(D1:D8<>"#N/A"),D1:D8)

 

[Steve 1962]

Hi Fluff

Brilliant !!! Thanks very much - works fine.

Have a good day.

 

[Fluff]

You're welcome & thanks for the feedback.