Solved

How to use an array of non-adjacent cells.

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Microsoft Office 2010 is Microsoft's latest edition to their office productivity collection. It "promises to include rich and powerful new ways to deliver your best work". So to start off, I have to say that I'm slightly disappointed... That bei…
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
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 create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now