+ Reply to Thread
Results 1 to 8 of 8

Looking up value by closest match & determining cell address.

  1. #1
    Registered User
    Join Date
    12-20-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Looking up value by closest match & determining cell address.

    Hello all,

    I've been pulling my hair out over this problem recently, I'm struggling to come up with a solution to size a pipe from a calculated value. See the attached sheet for a more clear explanation for this.

    What I'm wanting the sheet to do is look at a calculated value, then refer to a table array. Once its found the minimum value greater than the calculated value (ie. the size up) I'd like it to identify what pipe is needed.

    In the arbitrary example attached, It needs to see that the value is 273.45 then look for the smallest value above that which in this case is 288.95. From that I'd like it to return the identifier for the NB of the pipe (in this case it's 12") and return the schedule (this time schedule 80).

    Alongside the table and below you can see my attempts to make it work:
    1. =INDEX(B5:L28, MATCH(N4,C6:L28,-1), MATCH(N4,C6:L28,-1))
    2. =ADDRESS(2,MATCH(N4,C6:L28),4,1)
    3. =CELL(Address, INDEX(B5:L28, MATCH(N4,C6:L28,-1), MATCH(N4,C6:L28,-1)))

    Lastly any ideas about how to approach this?
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Looking up value by closest match & determining cell address.

    Like so....:
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-20-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Looking up value by closest match & determining cell address.

    Hello JBeaucaire,

    Thank you for the quick reply!

    Thats really close to what I'm after. Instead of the data validation list for the schedule I'm after giving a set value in the same way as the the pipe size - would I be approaching the problem in the right way to adapt the formula being used for that cell?

    I.e. =INDEX($B$6:$B$28, MATCH($N$4, OFFSET($B$6:$B$28, , MATCH($N$5, $C$5:$L$5, 0), , ))+1) to suit searching along the columns?

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Looking up value by closest match & determining cell address.

    I guess I'm not understanding. For a two-dimensional table, it is standard to indicate an X and Y. You're doing something different?

  5. #5
    Registered User
    Join Date
    12-20-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Looking up value by closest match & determining cell address.

    Quote Originally Posted by JBeaucaire View Post
    I guess I'm not understanding. For a two-dimensional table, it is standard to indicate an X and Y. You're doing something different?
    Apologies for the misunderstanding. The intention is to suggest a suitable pipe schedule for the calculated sized part. So I suppose the best way to explain it, is the value is known and the X and Y are to be determined from that - for example, I'm wanting it to take the calculated value (in this case its 273mm) look at the table of data and find the next size up (minimum value which is greater or equal to the calculated value) which is similar to what a person would do with the pipe size tables. Once its found that to return the pipes nominal bore and the schedule.

    Attached is my the mark 1 spreadsheet which is pretty crudely put together (I'm new to using excel!) which half gets the desired results - Although it has a tendency to fail when you return an equal to result (i.e. if you put 598.52 in which is the same as a 24" NB Schedule 5s pipe).

    I'm beginning to think I'm asking too much of excel!

    Many thanks for the replies.

    Mike
    Attached Files Attached Files

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Looking up value by closest match & determining cell address.

    In L7 enter this horrible ugly array formula:

    =INDEX($B$1:$B$28, SUMPRODUCT(--(($C$6:$I$28-$L$4)=MIN(IF($C$6:$I$28>=$L$4,ABS($C$6:$I$28-$L$4),"")))*ROW($C$6:$I$28))) & """ Pipe"
    & ", Schedule " & INDEX($A$5:$I$5, SUMPRODUCT(--(($C$6:$I$28-$L$4)=MIN(IF($C$6:$I$28>=$L$4, ABS($C$6:$I$28-$L$4), "")))*COLUMN($C$6:$I$28)))


    ...Confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You'll know the array is active when the curly braces { } appear around your formula.


    It's formulas like that that make me want to resort to VBA. Wouldn't it be awesome to be able use a custom formula like:

    =BestPipe(L4, C5:I28)

    ....and get the same answer. Anyway, that works.

  7. #7
    Registered User
    Join Date
    12-20-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Looking up value by closest match & determining cell address.

    Quote Originally Posted by JBeaucaire View Post
    In L7 enter this horrible ugly array formula:

    =INDEX($B$1:$B$28, SUMPRODUCT(--(($C$6:$I$28-$L$4)=MIN(IF($C$6:$I$28>=$L$4,ABS($C$6:$I$28-$L$4),"")))*ROW($C$6:$I$28))) & """ Pipe"
    & ", Schedule " & INDEX($A$5:$I$5, SUMPRODUCT(--(($C$6:$I$28-$L$4)=MIN(IF($C$6:$I$28>=$L$4, ABS($C$6:$I$28-$L$4), "")))*COLUMN($C$6:$I$28)))


    ...Confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You'll know the array is active when the curly braces { } appear around your formula.


    It's formulas like that that make me want to resort to VBA. Wouldn't it be awesome to be able use a custom formula like:

    =BestPipe(L4, C5:I28)

    ....and get the same answer. Anyway, that works.

    That's absolutely brilliant! Thank you very much for that,is there anywhere you'd recommend I'd look to learn how to write formula line that?

    Many thanks,

    Mike

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Looking up value by closest match & determining cell address.

    No, that monster comes from years of having to come up with stuff like that. I don't recommend it at all!

    If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

+ 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07362 seconds
  • Memory Usage 9,056KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (8)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (3)postbit_attachment
  • (8)postbit_legacy
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (47):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1