LinkBack URL
About LinkBacks
Hi Guys,
I'm trying to figure out in simplistic terms what the below formula is trying to achieve. I'm not sure why O35:O75 is being repeated in this formula.
=LOOKUP(2,1/(1-ISBLANK(O35:O75)),O35:O75)
Thanks in advance.
Hi Guys,
I'm trying to figure out in simplistic terms what the below formula is trying to achieve. I'm not sure why O35:O75 is being repeated in this formula.
=LOOKUP(2,1/(1-ISBLANK(O35:O75)),O35:O75)
Thanks in advance.
Welcome to the forum,
Trying to achieve or is achieveing?
The first mention of the range is for the criterion, the second is for the values to be returned for rows that meet the criterion.
LOOKUP(2,1/ ... will look for the LAST in the range to meet the criterion.
ISBLANK looks for cells that are blank.
What is the issue you are having with it, or is it just a case of trying to understand it?
Administrative Note:
Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Trying to understand it..... "LOOKUP(2,1/ ... will look for the LAST in the range to meet the criterion".... not sure what you mean.... sorry i'm having one of those days.
It is a very strange looking formula... thta will return the value of the last non-blank cell in the range. 99.99% of the time someone wants to do that, they will use:
=IFERROR(LOOKUP(2,1/(O35:O75<>""),O35:O75),"")
However, your formula.. this bit:
(1-ISBLANK(O35:O75))
returns 1 if the cell contains a value, zero if it doesn't.
One (1) is then divided by that array of numbers, returning 1 for 1 and #DIV/0 error for zero. LOOKUP(2... tells Excel to look for 2... there isn't one, so Excel simply returns the value in O35:O75 that corresponds to the last 1
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks for the replies, I'm trying to figure out whether the calculation is correct in a spreadsheet. The figure should be 30.37 so I'm told but its being calculated as 29.7274384 which is pretty close.
Attachment 772532
It is correctly returning the last non-blank value in the range. No calculation is being done.
There are instructions at the top of the page explaining how to attach your sample workbook (NOT a screenshot of it).
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
Please update your profile as requested earlier.
will do.... thanks
Yea... we need to see the sheet.... Excel is never "pretty close" It's unforgiving... yes or no; black or white... correct or incorrect.
Your formula is correctly returning the last non-blank cell's value in the range. There is no calculation - why are you expecting a figure of 30.37? What is the calculation you wish to perform?
Are there formulae in O35:O75... or values? What else is there in the entire range?
I'll get you the excel spreadsheet in the next hour i'll make it readable.... thanks guys for all your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks