Solved: Formula to return the last cell that contains data

[Lawrence]

Attached is the book I am working on, which some members here already helped me with. The trend and the indicator are both based on the current day's data, and the problem is that so instead of having to reset all the formulas have to be reset each time.

Is there a way to insert a dummy column (K in this case) and have a formula that would look for the last cell that contains a value? For example the formula in K6 would look at F6 and move to G6 since F6 has a value, then move to H6 since G6 has a value, then return G6 since H6 is blank.

[Bob Phillips]

=LOOKUP(2,1/($F6:$J6<>""),$F6:$J6)

[Lawrence]

Wow Bob, really cool. Can you explain to me how it works, more specifically why the 2 as a lookup value and why the 1/($F6:$J6<>"") as a lookup vector? I'd like to use a variation of this for column P to give me the past 4 week, or the past X amount of days.

One last request, in this example for column L, I am using the following formula and it works
L6: =SUMPRODUCT(--(F6:J6<2.4),--( F6:J6>0),--(MONTH(F4:J4)=MONTH(TODAY())))

But, I have another tab to compile weekly data and the headers on row 4 are "Week of 9/12" etc... So I tried modifying the above formula but I haven't found a solution yet. No matter what I try, I get a #VALUE! error

[Bob Phillips]

The ($F6:$J6<>"") in the lookup_vector will return an array of TRUE/FALSE values depending upon whether those cells are empty or not. Dividing this array into 1 gives an array of 1s and #DIV/0, for the same reason. 2 is matched against this array, but no value will be 2 or greater *1 is the most it can be), so it matches with the last value less than that, and returns the corresponding value from the result_vector, which is als $F6:$J6.

What excatly did you try with that data, you cannot use MONTH obviosuly as you don't have dates but text.

[Lawrence]

What excatly did you try with that data, you cannot use MONTH obviosuly as you don't have dates but text.
That's what I used by trying to extract the date. It may not be very efficient but I did the follwing. On row 5 I used
=MID(Z4,FIND("/",Z4)-2,6)
which returned 7/21, 7/28, 8/4, etc.... Then I set my other formula to read row 5 by using
=SUMPRODUCT(--(T9:Z9<2.4),--(T9:Z9>0),--(MONTH(T5:Z5)=MONTH(TODAY())))
and I get the #VALUE!

[Bob Phillips]

You need a lot more than that to get a date

=--(TRIM(MID(Z4,FIND("/",Z4)-2,99))&"/"&YEAR(TODAY()))

but why not just use the date from which you derived the text in Z4?

[Lawrence]

You need a lot more than that to get a date
=--(TRIM(MID(Z4,FIND("/",Z4)-2,99))&"/"&YEAR(TODAY()))

Thanks for that formula Bob, works like a charm.


but why not just use the date from which you derived the text in Z4?
Well it works for the daily tab, but for the weekly and monthly tabs, the ones who came up with this layout have the headers "Week of 8/21", "Week of 8/28", and so on. They don't want us to modify it and expect everyone to manually copy/paste data and shapes on a daily basis. :rofl: So, I am building my own, which I'll link to theirs, then break the links before emailing so it will be transparent to them.

[Bob Phillips]

It's often amazing how some are quite prepared to make others jump through hoops to avoid them making simple changes ... sometimes you wonder if you work for the same company.

[Lawrence]

=LOOKUP(2,1/($C6:$Z6<>""),$C6:$Z6)


Sorry for being thick in the head, but I am not getting the functionality of this. It works wonders for what I asked before, but I now have another column where I need to return the number of times the data was less than say 2.4 in the past 4 weeks.

I currently have the following formula which works but which I need to "slide" every time a new week (column) is added.
=SUMPRODUCT(--(W9:Z9<2.4),--(W9:Z9>0))

I am looking to combine this with your formula above but I have no idea where to start.

[Bob Phillips]

Why not just cater for the fullest range

=SUMPRODUCT(--(W9:IV9<2.4),--(W9:IV9>0))

As long as the columns are empty they won't get counted until you put real data in.

[Lawrence]

Because there is stuff in other columns which would throw off the formula.

[Bob Phillips]

There is problem there then because the formula gets the last cell, so it will get your other stuff.

Do you have a restricted range that the SP formula will look at, or some criteria to identify it?

[Lawrence]

Attached is a sample of the layout and I am not allowed to move anything around or change headers, per the ones who came up with this, hence the challenge. :banghead: They'll keep adding weeks to it which is why I was trying to have a formula that wasn't static.

[Bob Phillips]

.

[Lawrence]

Thank you Bob. Yet another thing I discovered. :)