Solved

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

Posted on 2012-04-09
11
311 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 41

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 41

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Closing Comment

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

Expert Comment

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

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 41

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 41

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

809 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