Hi everyone!
I have a problem to find a cell number within a column containing increasing, numerical data. Let's say I have a column with numbers : 1 2 3 6 8 9 and I want to find the last cell number for which the data satisfies a condition <8. So that would be a cell containing 6 in this case.
I would be very grateful for your suggestions.
Thanks,
Dominika
Try this
Formula:
Please Login or Register to view this content.
v A B C 1 1 6 2 2 3 3 4 6 5 8 6 9
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thank you, that worked! Now, if I have found the ''6'' and I found that it is in A4 I want to find the max value withing the range A1:A4 (assuming it's not increasing so it's not necessarily a last value). So I want to use sth like max(A1:A4) but the "A4" is a result of the function not a constant.
Maybe you need something like this...
Array entered**:
=MAX(IF(A1:A6<8,A1:A6))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
That will return the highest number that is less than 8 no matter where it is located within the range. Is that what you want?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
You might also want to try this formula, based on AlKey's:Let me know if you have any questions.
Please Login or Register to view this content.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Solved it! Thank you all
Can't tell who you're replying to but we do appreciate the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks