I have a range of cells and I want to return the first value, penultimate value and last value. I have a formula to return the first value and another for the final value but I'm struggling to get a formula to work that will return the penultimate value.
If, for example, the range is P4:AD4 then not every cell will necessarily have a value as some cells are blank.
Can anyone help please?
Hi and welcome to the forum
Blank as in nothing entered, or blank as in a formula that is showing nothing?
What formula are you using for the last value?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this:
Formula:
Please Login or Register to view this content.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
This formula taken from another thread seems to work.
=INDEX(4:4,LARGE(IF(P4:AD4<>"",COLUMN(P4:AD4)),2))
=INDEX(4:4,LARGE(IF(P4:AD4<>"",COLUMN(P4:AD4)),2)) needs to be array entered tho
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Could you provide an example where the first value, the penultimate value, and the last value can be seen clearly?
=index(p4:ad4,large(index((p4:ad4<>"")*(column(p4:ad4)-column(p4)+1),0),2))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks