Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Find the value in a row of cells where all but one column is blank

Posted on 2012-04-09
Medium Priority
342 Views
I have a spreadsheet with month columns there will be a date in only ONE month want to be able to calculate 10 days prior in another cell to confirm notification to attendee.  I have attached a sample
Calculate-10-days.xls
0
Question by:eyes59
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 42

Expert Comment

ID: 37825544
** Edited **
If the row will have nothing, but one date, then you can use COUNTA to determine if there is an entry, then the LOOKUP formula to find that entry and then subtract 10 from it.

Put this formula in C3 and copy down:

[C3]=IF(COUNTA(\$D3:\$O3)=0,"",LOOKUP(1,1/(\$D3:\$O3<>""),\$D3:\$O3)-10)

See attached.

Dave
Calculate-10-days.xls
0

LVL 17

Expert Comment

ID: 37825549
I modified your file, and I added a command button to it.

Calculate-10-days.xls
0

LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 37825565
The way the LOOKUP works:

=LOOKUP(1,1/(\$D3:\$O3<>""),\$D3:\$O3)

The first parameter is looking for a 1, if found, then it uses that INDEX from the lookup to find the value in the \$D3:\$O3 range of cells (the lookup_vector)

So, that leaves us with the middle part:

1/(\$D3:\$O3<>"")

\$D3:\$O3 returns an array of values.  \$D3:\$O3<>"" returns an array of TRUE and/or FALSE's.  The 1/(\$D3:\$O3<>"") returns an array of #DIV!0's or 1's.  #DIV!0 if its FALSE, or 1 if its TRUE.

The lookup then finds the FIRST occurrence of the 1 in that array, bypassing all the #DIV!0's.  In essence, returns an index to the first non-blank, and with the result vector, uses that index to return the actual value.

The rest of the formula is just to subtract 10 as you requested, if a date is found.

Cheers,

Dave
0

Author Closing Comment

ID: 37825603
THANK YOU.  Worked PERFECTLY
0

LVL 9

Expert Comment

ID: 37825651
:-)
0

LVL 42

Expert Comment

ID: 37825663
That would work with a little help and find the max date (as opposed to the first date which is what lookup did):

[C3]=IF(COUNTA(\$D3:\$O3)=0,"",MAX(\$D3:\$O3)-10) and copy down
0

LVL 9

Expert Comment

ID: 37825667
I need to type faster :-)

Congrats - looks like you just hit 1,000,000 !
0

LVL 42

Expert Comment

ID: 37825676
Thanks.

Lookup is not that complicated as a function re: relative efficiency, though if my head wasn't into these functions all the time, I would have pulled out MAX as well.  Sometimes its the simple solutions, lol.

Dave
0

LVL 9

Expert Comment

ID: 37825687
I'm still trying to figure out on what sub system you just passed 1,000,000.. very nice anyway.

yes I was wondering why all the effort to deliver the null because he didn't ask for it and that's what makes the answer complex. My first thought was the humble =sum() but elected the slightly "cleverer" max or min... min probably better. I supposed it was because it's a vb solution.

edit: because you used an if I wondered if it could be done without.
0

LVL 42

Expert Comment

ID: 37825704
Can't be done without an IF if you want blank on a no-date situation.  Any array function that resolves to one result would have been OK - the lookup was just my latest "hammer", while MAX and SUM, MIN, INDEX/MATCH, etc., would work as well.  But I think formulas should be written with error checking and/or returning "" value if there is no valid result.

Cheers,

Dave
0

LVL 9

Expert Comment

ID: 37825710
yes, certainly tidier.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll