# Selecting Higher/Lower Value based on User Input

1. ## Selecting Higher/Lower Value based on User Input

I'm building a worksheet with 5 data colums (rounded weight amounts like 100, 500, 1000) and over 1000 rows of data (prices). I've already construced an interface to select a price based on weight amount but I'm stumped on what to do if the user enters a number between the rounded weight amounts (ex. 453 instead of 100 or 500)

How could I set up a formula which takes the user-entered value and based on that number automatically select the next-highest column value? (ex. 75 goes to 100, 325 goes to 500)?

Thanks for any help!

2. ## Re: Selecting Higher/Lower Value based on User Input

Without seeing your data it's hard to be specific but you could use INDEX() with MATCH() formula. For the column argument put an ISNA() test on the MATCH() using FALSE as the 3rd argument. If there is no exact match then use MATCH() with TRUE and then add 1 to adjust one column to right, otherwise use MATCH() with FALSE.

Post a small sample file if you don't follow this.

3. ## Re: Selecting Higher/Lower Value based on User Input

I've been working with your suggestion, but I've had no success getting anything to work and seem to be going in circles.

Attached is a version of the spreadsheet cut down from its current 4000 row size.

As you can see, A8 & B8 are filters which reduce the displayed data based on two selections. A6 is where the user will enter their weight number.

The formula needs to take the number in A6 and then select the next-highest column value in D8:I8 and display only the data results from that column.

Cant help but feel like I'm in over my head on this one...thanks for any help!

4. ## Re: Selecting Higher/Lower Value based on User Input

First off, you need to change the value in C8 to 0 instead of "Min". Then, if you want "Min" to show instead of 0 apply a custom format of ;;"Min"

Now you can use this formula to get the correct result based on A6 value:

=IF(A6="","",INDEX(C8:I8,MATCH(A6,C8:I8,1)+IF(ISNA(MATCH(A6,C8:I8,0)),1,0)))

5. ## Re: Selecting Higher/Lower Value based on User Input

Thanks so much, that did the trick.

One more question if you dont mind -

I'd like to add another cell, lets say C6, which will display the lowest value in the returned data based on the filtering and weight changes. I came up with this formula:

=MIN(IF(\$C\$8:\$J\$8=B6,INDEX(\$C\$10:\$K\$65,MATCH(A8:B8,\$A\$10:\$B\$65,0),0)))

But it keeps producing a zero. What am I doing wrong?

6. ## Re: Selecting Higher/Lower Value based on User Input

You're welcome - and thanks for the "scales tap".

As for the MIN() you could use:

=IF(A6="","",MIN(INDEX(\$C\$10:\$I\$325,0,MATCH(B6,C8:I8,0))))
assuming the value to match is in B6
BUT that will give the MIN() for the entire column.

If you want the MIN() for the filtered values only you can use:

=IF(A6="","",SUBTOTAL(105,INDEX(\$C\$10:\$I\$325,0,MATCH(B6,C8:I8,0))))

7. ## [SOLVED] Selecting Higher/Lower Value based on User Input

Again, that worked like a charm. Thanks for your help!!

9. ## Re: [SOLVED] Selecting Higher/Lower Value based on User Input

To summarize your latest request (from PM):
If a MIN rate of \$0 is the result of the SUBTOTAL(105) formula have it return the MIN rate from the next column to right.

Based on that I placed 1500 in A6 and selected "TVC" for the filter on col A.
This returned a value of 0 in D6 because all filtered cells for the Def Weight of 2000 (col G) were blank.
Amending the formula in D6 to this:
``Please Login or Register  to view this content.``
returns \$15.85 which is the MIN rate from col H which has Def Weight of 3000.

Is that what you're after?

10. ## Re: [SOLVED] Selecting Higher/Lower Value based on User Input

Originally Posted by Cutter
Is that what you're after?
Yes, thank you. The formula does work on the trial sheet as I would like, however when transferring it to the final product it seems not to work the same way.

In the interest of not wasting any more of your time, I'm attaching the final product sheet which I've been working on. I've entered your above formula in cells D6 & F6, with amendments to the formula in F6.

The first test I tried was entering 1500 in A6 with the filters set to JFK (A8) and LAX (B8). This displays three lines of data. The formula's in C6 & D6 return, as they should, the next-highest column value (2000) and the MIN value from that column (29.99). However, the formula does not return the correct lowest value, which is located in the 3000 column (23.62).

Similarly, when I enter 7200 in A6 with the same filters, C6 & D6 function properly but still do not include the correct MIN value, which can be found in the 10000 column (21.38).

In both these instances, the formulas in D6 should look down the corresponding column from C6 and if it encounters a blank cell should move one more column to the right and return the MIN of the combined columns.

Since I had to transfer the original formulas from the test sheet to this final sheet, I'm assuming that I missed a conditional setting which is returning these errors. If thats not the case hopefully this version of the sheet and the above examples can show what I'm trying to do with these new formulas.

11. ## Re: [SOLVED] Selecting Higher/Lower Value based on User Input

OK, I think I understand it now.

Try this in D6 and copy to G6 (it won't need amendment):
``Please Login or Register  to view this content.``
There's probably a more elegant solution but this seems to work.

12. ## Re: [SOLVED] Selecting Higher/Lower Value based on User Input

Yeah that does the job. Thanks again, sorry for all the confusion!

13. ## Re: [SOLVED] Selecting Higher/Lower Value based on User Input

You're welcome. I'm glad we got it working for you.

14. ## Re: Selecting Higher/Lower Value based on User Input

From PM
Is it possible to have the value in C6 display the column value where the min rate was located?
After much thought and coming close to declaring it beyond me I came up with a solution - but you may not like it. It involves a helper column (I used M) which can be hidden.
I also CUT the formula from C6 and pasted it in C4 - you can paste it anywhere you want - maybe near top of column M so it can be hidden with the helper column.
Now place this formula in the now vacant C6:
``Please Login or Register  to view this content.``
And in the helper column M place this formula in cell M39 (and drag all the way down to M3984):
``Please Login or Register  to view this content.``
Give it a try and let me know (in this thread as opposed to PM).

15. ## Re: Selecting Higher/Lower Value based on User Input

Ok, proceeding as you instructed I entered all the formula's with the following alterations.

1). Changed all range data from "_39:_3984" to "_10:_3984" to encompass the entire data range
2). After moving the original C6 formula to M1, I change all C6 references in the new formula's to M1.

Unfortunately, after doing this I immediately get a Circular Reference Warning along with annoying blue arrows after entering the formula into M10 and dragging it to M3984. Also, the formula's in C6 & D6 no longer seem to calculate when entering different data or changing the filter options.

I have uploaded the sheet as it is now with all the new formula's applied. Is the error resulting from my alterations?

16. ## Re: Selecting Higher/Lower Value based on User Input

The error is in D6 - there are references to C6 that now need to be M1.
The reason I told you to CUT C6 and paste it elsewhere was to avoid that kind of error. When you cut a cell and paste it to another location all references to that original address in all formulas that point to it will automatically update to the new address.

The formula in D6 should now be:
``Please Login or Register  to view this content.``
Give that a try.

17. ## Re: Selecting Higher/Lower Value based on User Input

Ok - checking now.

18. ## Re: Selecting Higher/Lower Value based on User Input

Ah, I see what I did now. OK, so using the updated formula the values in C6 & D6 seem to be working, but it looks like it might be working too well

As I went through some different values in A6 I started to see a few problems;

If you can check the following:
Enter JFK in A8
Enter LAX in B8
In cell A6
Entering 150 produced the correct results in C6&D6 but returned the wrong data in G6
Entering 550 produced the correct results in all fields.
Entering 1150 produced the correct results in all fields
Entering 2150 produced the MIN value in the 5000 column, not the 3000 column and the wrong data was returned in G6
Entering 3150 produced the MIN value in the 5000 column, not the 3000 column and the wrong data was returned in G6
Entering 4150 produced the MIN value in the 7500 column, not the 5000 column and the wrong data was returned in G6
Entering 5150, 6150 & 7150 produced the correct results in C6&D6 but returned the wrong data in G6
Entering 8150 & 9150 produced a #REF! result.

Questions;
1). Is there a way to stop the issues with the 2150/3150/4150 (or similar 2000-5000) values?
2). The 8150 & 9150 values are reporting an error obviously since there is no data in columns to the right. Can this be corrected by adding another helper column in row L with the repeated data already in row K?
3). I'm assuming that the error in G6's formula is the result of a +1, 0, or -1 not being in place. If not, should a version of the new D6 formula be placed in G6?

19. ## Re: Selecting Higher/Lower Value based on User Input

``Please Login or Register  to view this content.``
For 150 I don't see why the G6 value is wrong. It looks in the 100 column (D) and, because there is a blank in D3863, it takes the MIN() from D:E which happens to be in E1630.

For 2150 - same thing. D6 checks the 3000 column (H) and sees blanks in 2 of the 3 cells so it takes the MIN from H:I and returns \$23.49 from I1630. Same for G6 - looks in the 2000 column (G), finds blanks so takes MIN from G:H and returns \$23.62 from H1630.

Same for everything else except the #REF errors. But let's get the perceived erroneous returns sorted out first before we address that. So I guess we're back to what exactly determines the result you want. For all those examples you gave tell me what is expected and why.

20. ## Re: Selecting Higher/Lower Value based on User Input

Sorry for the long delay in replying - I've sent the document out for review and the feedback I've rec'd has been that it is functioning as it should, and returns the proper information. Again, thanks so much.

The only hitch at this point is the remaining #REF issue with any entries above 7500. Would the addition of another dummy row to the right of the 10000 colum containing a duplicate of the 10000 column's data solve the issue? Or would the presence of blank cells cause the formula to continue searching to the right and return the #REF?

21. ## Re: Selecting Higher/Lower Value based on User Input

Sorry for my delay - I took on a job that is taking WAY more hours than I thought would be required and my brain is getting fried.

I think all you need is a minor adjustment to what you already have:

Try this for D6:
``Please Login or Register  to view this content.``
and copy that formula to G6

and in M10 and down:
``Please Login or Register  to view this content.``

22. ## Re: Selecting Higher/Lower Value based on User Input

Looks like that solved the #REF issue.

Question though, should the D6 and G6 formula's really be the same? They are returning the same values despite the different values in C6 & F6.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1