Solved

How to use an array of non-adjacent cells.

Posted on 2015-01-27
3
110 Views
Last Modified: 2015-01-28
I am trying to figure out how to perform a calendar week number formula, array formula, or maybe it is just a VBA function.

My workbook has a Yearly worksheet, and 1 thru 12 worksheets for each month. The week number is calculated on a end or the week (Saturday) value from a typical looking calendar. The issue is that the end of the month may not always end on a Saturday. So I need to figure out how to move from Right to left in a row until it finds a non zero value in a series of cells.

The attached file has more detail on worksheet "3"

Any guidance here would be appreciated.
Rotation-Calendar.xlsm
0
Comment
Question by:DougDodge
  • 2
3 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40574781
Change all references of M27 to MAX(A27:M27).

So you have:

in P27: =WEEKNUM(MAX(A27:M27),1)
in P28: =MAX(A27:M27)-DATE(YEAR(MAX(A27:M27)),1,0)
in P29: =DATE(YEAR(MAX(A27:M27)),12,31)-MAX(A27:M27)
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40574785
Then, to accommodate February 2015, add a condition IF(A27="","",      rest of formula     )

So you have:

in P27: =IF(A27="","",WEEKNUM(MAX(A27:M27),1))
in P28: =IF(A27="","",MAX(A27:M27)-DATE(YEAR(MAX(A27:M27)),1,0))
in P29: =IF(A27="","",DATE(YEAR(MAX(A27:M27)),12,31)-MAX(A27:M27))

You can then copy these three formulas down to P33:P35 so that it would work in May 2015..
0
 

Author Closing Comment

by:DougDodge
ID: 40575293
Worked well. I did however have to make some slight adjustments....

=IF(A33="","",WEEKNUM(MAX(A33,C33,E33,G33,I33,K33,M33),1)& " - Week No.")
=IF(A33="","",MAX(A33,C33,E33,G33,I33,K33,M33)-DATE(YEAR(MAX(A33,C33,E33,G33,I33,K33,M33)),1,0)& " - Day of the Year.")
=IF(A33="","",DATE(YEAR(MAX(A33,C33,E33,G33,I33,K33,M33)),12,31)-MAX(A33,C33,E33,G33,I33,K33,M33)& " - Days Remaining.")

Simply because THERE COULD BE NUMBERS IN B33, D33, F33, H33, J33, L33, and this would also hide the text if the value of the If statement was ""

Thank you once again Philip.....
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cascading drop down lists in Excel 4 109
Excel Power Pivot - Aggregation 7 78
Excel formula lookup multiple charges 11 72
why can't I sort this by internment date 1 47
In this article I will provide some simple productivity hacks that will help you use Google to specifically show results from any web site (Experts-Exchange.com in my example), with minimal effort in Chrome and Firefox. I've seen a common theme a…
Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

773 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