Solved

How to use an array of non-adjacent cells.

Posted on 2015-01-27
3
114 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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
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…

839 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