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

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
eyes59Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
** 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
vb_elmarCommented:
I modified your file, and I added a command button to it.

Please press the command button.
Calculate-10-days.xls
dlmilleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

eyes59Author Commented:
THANK YOU.  Worked PERFECTLY
Anthony MellorChartered AccountantCommented:
:-)
dlmilleCommented:
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
Anthony MellorChartered AccountantCommented:
I need to type faster :-)

Congrats - looks like you just hit 1,000,000 !
dlmilleCommented:
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
Anthony MellorChartered AccountantCommented:
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.
dlmilleCommented:
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
Anthony MellorChartered AccountantCommented:
yes, certainly tidier.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.