+ Reply to Thread
Results 1 to 22 of 22

Selecting Higher/Lower Value based on User Input

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

    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!
    Last edited by FTN; 05-18-2011 at 08:48 AM.

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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. #3
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

    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!
    Last edited by FTN; 05-24-2011 at 01:18 PM.

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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. #5
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

    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. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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. #7
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

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

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

  8. #8
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

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

    New sheet uploaded.
    Last edited by FTN; 05-24-2011 at 01:17 PM.

  9. #9
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

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

    I've had a look at your file.
    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. #10
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

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

    Quote Originally Posted by Cutter View Post
    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.
    Attached Files Attached Files

  11. #11
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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. #12
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

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

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

  13. #13
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

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

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

  14. #14
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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. #15
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

    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?
    Attached Files Attached Files

  16. #16
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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. #17
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Selecting Higher/Lower Value based on User Input

    Ok - checking now.
    Last edited by FTN; 05-25-2011 at 01:42 PM. Reason: Revising

  18. #18
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

    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?

    My head hurts.

  19. #19
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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. #20
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

    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?
    Last edited by shg; 06-06-2011 at 12:11 PM. Reason: deleted quote

  21. #21
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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.
    Last edited by Cutter; 06-09-2011 at 09:00 AM.

  22. #22
    Registered User
    Join Date
    01-12-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    30

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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