Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index



On 2013-03-27 20:47, Brian Barker wrote:
At 07:15 26/03/2013 -0700, Jason C. Wells wrote:
That works well. I'll check it against real data today. The details of the numerical comparison are hidden from me with that equation ...

I'm sure you'll be able to analyse the formula =VLOOKUP(A1;C$1:D$9;2) - but here goes.

The array C1 to D9 contains your limiting values for the classes and the corresponding values you need to retrieve. This is indicated as a single parameter in the function reference as C1:D9. When we fill the resulting formula down column B, this array reference would be automatically modified to reference different rows; since you don't want this, you need to lock the row numbers, which you do by preceding them with the $ character. This gives C$1:D$9 as the second parameter.

The VLOOKUP() reference takes the value of its first parameter (A1, A2, and so on) and searches for it in the first column (C) of the above array. The third parameter, 2, indicates that the value you want to retrieve is from the second column of the array - column D. The absence of the optional fourth parameter indicates that column C is sorted in numerical order and makes the function select the value next below when there is no exact match.

Thanks Brian. I have use VLOOKUP but until your example had not realised the implications and usefulness of "select the value next below when there is no exact match"
Steve

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.