Return value

[Dharmendra_Bharwad]

Hi,

As per attached sheet, I want the following result from NI4 to ABJ73:

I want a formula to find percentage change in current date by taking base as previous day value. If there is no value in previous day, then last previous day having value must be taken as base day.

For example: Return for January 9, 1950 would be = (17.08/16.98-1) = 0.59% [because there is no data for Jan 7 and Jan 8)

Need the solution urgently.

[#2]

It looks to me as if your formula is working just fine for most of those cells if you just do a copy...paste of the existing formulas. So could you explain a bit more clearly what's NOT working.

[#3]

Thank you for your response.
Yes, my formula works for most of the cells. But, when the immediate previous cell is empty, the formula does not work. I am looking for a formula that in case immediate previous cell is empty then, it must include next immediate non blank cell as a base.

Hope you got what I require.

[#4]

That's what's puzzling to me. It looks to me as if your "raw data" section has no empty cells. In your very example, Jan 9, 1950, the two days before it are filled with the value from Jan 6, so your standard formula works just fine. Look at cell NQ4: it gives exactly the value you say it should.

So let me repeat, what precisely is NOT working?