Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to use an array of non-adjacent cells.

Posted on 2015-01-27
3
Medium Priority
?
130 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

606 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