+ Reply to Thread
Results 1 to 20 of 20

Highlighting first value that is just above cut-off and returning a different cell

  1. #1
    Registered User
    Join Date
    11-19-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Highlighting first value that is just above cut-off and returning a different cell

    Hello all,

    I struggling with generating a formula or multiple formulas to return what I need.

    Essentially I would like a formula that runs though cells B26 to B33 (bottom table), looks for the cell that is just above but not equal the cut-off value (in J35) and highlights the box in yellow. e.g. in B26 to B33 we the first value 0.091 is one just above the cut-off, the one below is 0.090 which is equal to the cut-off.

    Exception: if the values never go =< cut-off, then to highlight cell in the last row (e.g. H33 where the values remained above cut-off

    2) Once highlighted, i'd like to have an additional formula that returns the equivalent cell in the table above. e.g. B26 to B33, it the top value (0.091) in row A of the bottom table, so i'd like to return the value equivalent to row A in the top table i.e. 100 in cell B10.

    I have tried many formulas with little success particularly with 1).





    Capture 2.PNG
    Attached Files Attached Files
    Last edited by MByd; 11-19-2020 at 10:41 AM. Reason: Added an excel sample attachment

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    A picture is worth 1000 words, a sample sheet is worth 1000 pictures!!

    You have attached a non-editable picture of an Excel sheet. That's not very easy to work with. Also... I'm lazy. I have to re-type your information before I can begin to address your problem. That puts me off completely.

    So.... Do yourself a favour and please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough). However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever).

    Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Make sure confidential information is removed first!!!!
    Glenn



  3. #3
    Registered User
    Join Date
    11-19-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Highlighting first value that is just above cut-off and returning a different cell

    Thank you. I added a sample excel sheet

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    So.. How much above the cut-off is "just above the cutoff"?? 0.095, 0.1, 0.2???

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    ... or are you looking for the FIRST value in EACH column that exceeds the cut-off

  6. #6
    Registered User
    Join Date
    11-19-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Highlighting first value that is just above cut-off and returning a different cell

    It doesn't really matter. It's like i'm going down the column (e.g. from B26 to B33) and looking for the first value that is equal or less than cut-off. Once I hit it I would want to highlight the cell above it.

    To answer how many rows, this is the standard row format and it'll always be 8 rows.

    I hope that answered your question

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    Ahhh. Measurement of p-nitrophenol in microtitre plates... That takes me back a few years!!

  8. #8
    Registered User
    Join Date
    11-19-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Highlighting first value that is just above cut-off and returning a different cell

    Yessss. very very similar to what i'm doing. I'm trying to automate it to reduce errors when doing multiple plates

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    OK. First part (which caused me enough problems because of the two values of 0.091 in the 8th column):

    use this as the CF formula:

    =B26+ROW(B26)/10^10=SMALL((B$26:B$33+ROW($B$26:$B$33)/10^10),COUNTIF(B$26:B$33,"<"&$J$35)+1)

    Next thing. Explain (slowly) what you want for your exception...

    applied to the whole range.

    See sheet.
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    So, I begin to follow you. Do you mean if ALL the cells in the PLATE remain above the C/O highlight the entire last ROW

    or do you mean

    if ALL the cells in any individual column(s) remain above the C/O, highlight the bottom row of THAT column??

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    To return the matching value from the sample table:

    =IFERROR(1/(1/INDEX(B$10:B$17,MATCH(SMALL(B$26:B$33,COUNTIF(B$26:B$33,"<"&$J$35)+1),B$26:B$33,0))),"")
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    11-19-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Highlighting first value that is just above cut-off and returning a different cell

    Thanks so much. Though it looks like columns 5 and 8 aren't correct though. Cells F30 and and I30 should be highlighted not F33 and I28 respectively. Any guess where the error is coming from?

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    OK. Firstly, answer the Q in Post 10 that you didn't address.

    Secondly, the precision of the Absorbance readings from your plate is 2 dp. The precision of your cut-off is 15 dps (Excel's default). What degree of rounding, if any, do you wish to apply to the cut-off? This affects, for example, the answer for column 1, as the value (0.09) is being compared with 0.0902222523976801.

    Explain the result you want for column 6, where one value (E6) exceeds the cut-off.

  14. #14
    Registered User
    Join Date
    11-19-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Highlighting first value that is just above cut-off and returning a different cell

    Hi Glenn, not sure why these posts aren't appearing when i refresh the page.

    Regarding decimal places, the i'd like the three decimal places to be maintain across including the cut-off. I realized the latter is a calculation and and hence why it has multiple dp. I guess it needs to be switched to 3dp just like the values in the table.

    regardingg if ALL the cells in any individual column(s) remain above the C/O, highlight the bottom row of THAT column?? , Yes that's the correct argument. Only highlight the last cell in that column not all columns if the the values in that column all remain above c/o

    Regarding column 6, it's ok if the same formula is used. I don't use that value (or the entire column for that matter) in subsequent calculations.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    I have it done... on my laptop, which I will be beside in about 30 mins. So, look back then!!

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    It was still wrong, hence the delay!!

    GREEN - your desired shading (note that rounding the C/O means that 0.085 is the first value < cut-off and so 0.90 is the value in the row above.

    Orange - last row highlight

    Yellow - matching values from Table 1.

    Hopefully that's it!!
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    11-19-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Highlighting first value that is just above cut-off and returning a different cell

    Hi Glenn,

    Thanks for the file. I have been MIA with other life things. Just had a chance to look at the file. It works well so far though with one issue. If the cut-off value exists in the array it highlights it rather than highlighting the value above it. Basically, a value equal to the cut-off is seen as values that are lower than the cut-off. For example B26 to B33 array should highlight 0.091 and not 0.090 (=C/O). The current formula highlights 0.090. Is there a work around this?

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    OK. But one last question from me. What should happen in lane 7 where NO values are < C/O?

  19. #19
    Registered User
    Join Date
    11-19-2020
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Highlighting first value that is just above cut-off and returning a different cell

    In cases where the values in the array never go below the cut-off, i would to highlight the cell in row H (0.091 in this cases) and return row H from the table at the top (12800). I believe the equation you provided does work with column 7 already.

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Highlighting first value that is just above cut-off and returning a different cell

    OK. we'll get there!! Here's a version that I think covers everything.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-11-2016, 03:43 PM
  2. Replies: 2
    Last Post: 11-28-2014, 03:43 PM
  3. Replies: 6
    Last Post: 02-16-2013, 07:29 AM
  4. Replies: 2
    Last Post: 02-06-2013, 12:13 AM
  5. Replies: 0
    Last Post: 02-05-2013, 07:51 AM
  6. Replies: 3
    Last Post: 05-03-2012, 04:00 PM
  7. Replies: 6
    Last Post: 08-28-2005, 05:05 PM

Tags for this Thread

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