Solved

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

Posted on 2012-04-09
11
318 Views
Last Modified: 2012-04-09
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
Comment
Question by:eyes59
11 Comments
 
LVL 42

Expert Comment

by:dlmille
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

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

Please press the command button.
Calculate-10-days.xls
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Closing Comment

by:eyes59
ID: 37825603
THANK YOU.  Worked PERFECTLY
0
 
LVL 9

Expert Comment

by:Anthony Mellor
ID: 37825651
:-)
0
 
LVL 42

Expert Comment

by:dlmille
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

by:Anthony Mellor
ID: 37825667
I need to type faster :-)

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

Expert Comment

by:dlmille
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

by:Anthony Mellor
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

by:dlmille
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

by:Anthony Mellor
ID: 37825710
yes, certainly tidier.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question