How to find a previous value in a row before a non blank cell? (Photo Ins

[raphazzz]

Hi Guys,

I have the excel file below where I need to compare the current and previous week results (Columns Y and Z). My idea is to have a formula to find the current week which is the last filled column (in this example is the Column G) and the previous week before the last filled column. I know the formula to find the last filled cell but I couldn't solve the formula to find the previous cell.

Does anyone know to help me?

excel_example.jpg

[FlameRetired]

Hi raphazzz. Welcome to the forum.

Honestly. Can't read that or work with it.

Try uploading that Excel workbook directly to this forum. It gives context.

Here's how:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[raphazzz]

Actually, I have found a way to do it. Another thread had a similar issue but I'd like to understand the logic behind it.

For finding the last value (numeric) in a roll on a specif rage I used this formula: =(LOOKUP(2,1/(ISNUMBER(E3:V3)),E3:V3))

For finding the previous value before the last value (numeric) in a roll on a specif rage I used this formula: =INDEX(E3:V3,LARGE(INDEX((E3:V3<>"")*(COLUMN(E3:V3)-COLUMN(E3)+1),0),2))

[JeteMc]

If you'll select the cell that has the formula in it and run the Evaluate Formula feature you'll see that after the ninth time that you select the Evaluate button there will be an array in which the columns that have values are listed as counting numbers (i.e. E3 = 1, F3 = 2 etc.), while the columns that do not contain values are listed as zeros. You'll then see that at that point the LARGE function is applied with a Kth value (pull up the help page for LARGE) of 2, meaning it will pass along the 2nd largest value from the array to the Index function. (I hope that makes sense).
Let us know if you have any questions.