How to find the nearest greater value and closest lower val

Discuss the spreadsheet application

How to find the nearest greater value and closest lower val

Postby sudheeshm » Sat Dec 14, 2019 4:47 pm

Hi All,

Please help me to get formulas to find the nearest greater/equal value and the closest lower value from the list.

Attached the sheet for reference.

I want to input any random number in 'D1' and it should scan column 'A' with numbers and print the values on 'G2' and 'G3'. In this example I used 22 as input and expect 23 in G2. Please help.

With love...
Attachments
Test.ods
example
(10.02 KiB) Downloaded 21 times
OpenOffice 4.1.6 on Windows 10
sudheeshm
 
Posts: 1
Joined: Sat Dec 14, 2019 4:33 pm

Re: How to find the nearest greater value and closest lower

Postby RusselB » Sat Dec 14, 2019 7:07 pm

Welcome to the Forums.
You could get the smaller of the two numbers by using the VLOOKUP function, with a formula like
Code: Select all   Expand viewCollapse view
=VLOOKUP(D1;A2:A19;1)

Please note that this will return the exact match if the number you enter in D1 is in the range A2:A19

I'm having a harder time getting something that returns the greater value...but I'll keep at it... in the mean time, there's a start.

This formula
Code: Select all   Expand viewCollapse view
=INDIRECT(ADDRESS(RANK(D4;A2:A19;1)+2;1))
seems to work for returning the higher of the two numbers you were wanting
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6100
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to find the nearest greater value and closest lower

Postby FJCC » Sat Dec 14, 2019 7:34 pm

If it is allowed to have a copy of the list in the reverse order in column B, then you can use
Code: Select all   Expand viewCollapse view
=INDEX(B2:B19;MATCH(D1;B2:B19;-1))

and
Code: Select all   Expand viewCollapse view
=INDEX(A2:A19;MATCH(D1;A2:A19;1))
Attachments
Test_fjcc.ods
(9.34 KiB) Downloaded 14 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7742
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How to find the nearest greater value and closest lower

Postby Lupp » Sat Dec 14, 2019 10:22 pm

You may also want to study the attached example.
Attachments
aoo100380NearestUpDownFromList_1.ods
(13.04 KiB) Downloaded 16 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2911
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests