Help! Google sheets formula to find lower and higher values (fairly simple i think)

[kropotkin]

Hi - can anyone help with this?

I have a sheet that that looks up values in a table to calculate via interpolation.
What I need is a reliable formula that looks at the following sheet:


It is given an input that is say m= 235
It then looks at the first row and returns the lower and higher values that correspond to this value- namely here 200 and 250.
Ideally if the value is the same as a column (e.g 300) it would say the lower and higher are both the same, but I can sort this out later if it cant be done in one formula.

The ones i'm writing dont work properly. Can someone with a less addled brain help me out?

 

[kropotkin]

I'm currently using filter, abs and min in one formula to find the lower value- but this fails- e.g. an input value of 245 would output 250 instead of 200.

 

[moochedit]

Calculate the smallest or largest number in a range - Microsoft Support

Let's say you want to find out who has the the smallest error rate in a production run at a factory or the largest salary in your department. There are several ways to calculate the smallest or largest number in a range.

support.microsoft.com

 

[8ball]

Calculate the smallest or largest number in a range - Microsoft Support

Let's say you want to find out who has the the smallest error rate in a production run at a factory or the largest salary in your department. There are several ways to calculate the smallest or largest number in a range.

support.microsoft.com

And if that doesn’t work - here is my solution:

Spoiler

Paging kabbes 2hats ….

(My brain is too addled now to even understand what the question)

 

[kabbes]

I’m a little bit unsure what you’re trying to do, to be honest, but it sounds like something that in excel you could do with either the match or vlookup function based on ordered data and with the parameter set to nearest match rather than exact match. I know nothing about google sheets though.

 

[8ball]

If I was in work I’d ask what it is you’re trying to achieve and why, in case there is another way round it.

(I work in clinical trials stuff and am regularly specifying and mocking up assorted shizz from dose calculations to disease progression criteria to stopping algorithms etc. so you never know…)

 

[moochedit]

I’m a little bit unsure what you’re trying to do, to be honest, but it sounds like something that in excel you could do with either the match or vlookup function based on ordered data and with the parameter set to nearest match rather than exact match. I know nothing about google sheets though.

Oh...durr....it's google sheets not excel. Ignore my post then

 

[moochedit]

And if that doesn’t work - here is my solution:

It defo doesnt work as i mixed up excel and google sheets

 

[8ball]

It defo doesnt work as i mixed up excel and google sheets

Never used Google sheets myself either. Is it all that different?

 

[kabbes]

Never used Google sheets myself either. Is it all that different?

It has a much more restricted function set, I believe, and the functions may have different names. Also, I don’t think there is (or at least know of) any VBA equivalent to custom write your own bodges

 

[moochedit]

Never used Google sheets myself either. Is it all that different?

I have used google sheets before at work but not really using formulas so don't really know how different it is from excel.

 

[existentialist]

Hi - can anyone help with this?

I have a sheet that that looks up values in a table to calculate via interpolation.
What I need is a reliable formula that looks at the following sheet:
View attachment 332600

It is given an input that is say m= 235
It then looks at the first row and returns the lower and higher values that correspond to this value- namely here 200 and 250.
Ideally if the value is the same as a column (e.g 300) it would say the lower and higher are both the same, but I can sort this out later if it cant be done in one formula.

The ones i'm writing dont work properly. Can someone with a less addled brain help me out?

I've had a little look at this, and this is what I've discovered so far...(I've put the formulas in a screengrab below)

Assuming that the numbers across the top row are always in ascending order, we can use the "Sorted" flag on HLOOKUP, which lets it return the value less than or equal to the value being searched for (so your search for 235 would return 200).

What we can then do is to use the MATCH function to get which column in the range holds the value we have found. This depends on the values in the range being unique.

From that, we can first of all establish whether it was an exact match, in which case - per your example - we can simply return the same value for both upper and lower. If not, then we return the value we found (which will be the lower one, and use OFFSET() to find the next one along.

The reason for using the MATCH function is so we can nudge the offset up by one to give us the next element along.

Caveats

I haven't looked at edge cases, like putting a value that is out of range in.
You will note that the OFFSET function starts the range at A1 - that was purely cheating, so as to give me a 0-based range (since the search starts one column to the left of the actual data range), so as to save cluttering up the formula with loads of C5-1s.

The Upper and Lower results are using OFFSET to return the actual value, rather than the column offset, just to show it's working. You may want to work with offsets, depending on what you are planning to do with the numbers from here...


Numbers output:


Exact match example:

 

[kropotkin]

Thanks so much for this!
I'm in a field in Brecon currently but will look properly when I get back to modernity

 

[8ball]

Thanks so much for this!
I'm in a field in Brecon currently but will look properly when I get back to modernity

Nice one. Had it in my head that you’d been working this whole time.

 

[existentialist]

Thanks so much for this!
I'm in a field in Brecon currently but will look properly when I get back to modernity

Oh, you're only 50 miles up the road...

 

[kropotkin]

Taking the

Nice one. Had it in my head that you’d been working this whole time.

Luckily not!
Taking the kids down the Wye on canoes tomorrow

 

[kropotkin]

This worked perfectly- thanks so much

 

[DaveCinzano]

There's no love like spreadsheet geek love

 

[kropotkin]

none at all

 

[existentialist]

There's no love like spreadsheet geek love

Don't accuse me of being a spreadsheet geek.!I hate the fucking things...

 

[two sheds]

try doing all that without a spreadsheet

 

[existentialist]

try doing all that without a spreadsheet

Programming languages.

 

[two sheds]

pah a spreadsheet's just another language you don't get away with that

 

[High Voltage]

You lot are weird. Can't you print out the table and use scissors, or for a really professional looking result a scalpel, metal straight edge and a cutting mat, cut the vertical rows up, magic tape them back together in the order that you want and then photocopy the result. (I suppose, if you really had to have it in a digital format you could scan it)

 

[Ted Striker]

I missed this first time, but i think MAXIFS and MINIFS would have been a solution for this (instead of MATCH, and doesn't require sorted columns), but some nice work here, if you'll excuse some Excel/VBA snobbery and faux antipathy I see creeping in

 

[two sheds]

I missed this first time, but i think MAXIFS and MINIFS would have been a solution for this (instead of MATCH, and doesn't require sorted columns), but some nice work here, if you'll excuse some Excel/VBA snobbery and faux antipathy I see creeping in

Yes indeed

/looks at existentialist

 

[Ted Striker]

Tbf, MINIFS/MAXIFS are still witchcraft to a number of us...I still consider SUMIFS-ers johnny-come-lately's that never knew the years spent carving out the same functionality out of SUMPRODUCT

 

[existentialist]

Yes indeed

/looks at existentialist

I'd be interested to see an implementation .

Regarding my antipathy to spreadsheets...it's not that I've got anything against them per se, but I spent a significant part of my 30 year IT career trying to persuade managers that their hideously complex spreadsheets - which usually came to my attention because they'd come up against some kind of limit and they were looking to me to "fix" the problem for them - were a complete misapplication of the technology. There's a grey area of crossover between spreadsheets and databases, but people know (or think they know) how to manage data in a spreadsheet, but find databases incomprehensible.

FWIW, kropotkin's use case looks like a good example of where a spreadsheet is appropriate - I guess I wouldn't have bothered to try and work out a solution if I hadn't thought so

 

[kabbes]

Tbf, MINIFS/MAXIFS are still witchcraft to a number of us...I still consider SUMIFS-ers johnny-come-lately's that never knew the years spent carving out the same functionality out of SUMPRODUCT

I never use any of these things. I use array formulae, which are much cleaner. Instead of =SUMIF(), you use {=SUM(IF(…))} and then you can use as many complex Boolean triggers and nested statements as you like.

Keep it simple for the layman though. Easier to sort your data and use MATCH.

 

[Ted Striker]

I never use any of these things. I use array formulae, which are much cleaner. Instead of =SUMIF(), you use {=SUM(IF(…))} and then you can use as many complex Boolean triggers and nested statements as you like.

At some point you'll need to explain this to others. And I'm a bit of a refusenik on ctrl+enter all the time.

Keep it simple for the layman though

Absolutely. At some point you'll want someone else to do this for you. Minimising the length of that knowledge transfer (to as many perhaps-less-capable people as possible) is a key success criteria.

Easier to sort your data and use MATCH.

No way! You're inviting a fundamental quality issue to ensure your data is always sorted (and removing the whole 'order-neutral' focus of all your analysis).