Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to use an array of non-adjacent cells.

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
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 teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
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:

564 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