+ Reply to Thread
Results 1 to 4 of 4

Nonblank values from a row and the corresponding values from another row to use in formula

  1. #1
    Registered User
    Join Date
    11-11-2021
    Location
    New Zealand
    MS-Off Ver
    Office 365
    Posts
    11

    Nonblank values from a row and the corresponding values from another row to use in formula

    Hi, I have an excel worksheet where I need to pick up the values from 2 non-blank cells from Row 5, and the corresponding values from Row 1 to use in a formula: (see attached xl sheet). Here the cells B5 and D5 are non-blank. The value for the formula is returned in B6.
    B1 + ((D1 - B1) * (10 - B5) / (D5 - B5))
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,973

    Re: Nonblank values from a row and the corresponding values from another row to use in for

    Maybe this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-11-2021
    Location
    New Zealand
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Nonblank values from a row and the corresponding values from another row to use in for

    What i want is to determine which cells in row 5 are not blank and then just pick those values in the formula. In my given example cell B5 and D5 are not blank, so i picked them in the formula. My formula should determine which cells are non-blank and use them in the formula. The above formula was just an example where B5 and D5 were non-blank. So the formula should have the following logic:

    IF (AND(B5<>"",C5<>"",D5="",E5=""),B1 + ((C1 - B1) * (10 - B5) / (C5 - B5)) ELSE

    IF(AND(B5<>"",D5<>"",C5="",E5=""),B1 + ((D1 - B1) * (10 - B5) / (D5 - B5)) ELSE

    IF(AND(C5<>"",D5<>"",B5="",E5=""),C1 + ((D1 - C1) * (10 - C5) / (D5 - C5)) ELSE

    and so on - so the formula should check which 2 cells are not blank and take it from there. The above approach would be a quite cumbersome since it would have about 6 IF conditions which is not a good way to write a formula)

    Hope this is clear

    Thanks G
    Last edited by docGee; 11-11-2021 at 07:44 PM.

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Nonblank values from a row and the corresponding values from another row to use in for

    Here is a suggestion using a "helper" area in G1:H2 (it can be located wherever is convenient)

    The helper column formulae pick out the relevant 4 numbers and so the solution cell, B6, contains your equation which now only references the "helper" area.

    The helper area formulae are similar but not quite identical:
    G1: =INDEX(B1:E1, AGGREGATE(15, 6, {1,2,3,4}/ISNUMBER(B5:E5), 1))
    G2: =INDEX(B5:E5, AGGREGATE(15, 6, {1,2,3,4}/ISNUMBER(B5:E5), 1))
    H1: =INDEX(B1:E1, AGGREGATE(14, 6, {1,2,3,4}/ISNUMBER(B5:E5), 1))
    H2: =INDEX(B5:E5, AGGREGATE(14, 6, {1,2,3,4}/ISNUMBER(B5:E5), 1))

    B6: =G1 + ((H1-G1)*(10-G2)/(H2-G2))

    I have attached an update to your workbook.

    Let us know if this works for you.
    Attached Files Attached Files
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

+ 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: 5
    Last Post: 01-31-2021, 01:46 PM
  2. I have to count nonblank values in a filtered list by multiple criteria. Help!
    By Jarvis_Cain in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2021, 12:06 AM
  3. [SOLVED] Dynamic macro that copies formulas down to rows with nonblank values
    By Stuartzz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-02-2018, 06:35 PM
  4. [SOLVED] Average of last 12 nonblank values in row
    By devunow in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-07-2015, 07:49 AM
  5. pasting nonblank values into new workbook
    By mabm529 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-21-2012, 04:48 PM
  6. [SOLVED] How to count the number of nonblank cells which have values
    By BNCOXUK in forum Excel General
    Replies: 2
    Last Post: 10-31-2012, 07:36 PM
  7. Replies: 2
    Last Post: 12-22-2011, 07: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