Log In
Register
Home
March 10, 2016
Offline
Hi
The attachment contains the yellow cell that has a function that I do not understand,
I would appreciate if you could explain the second part of the function?
Because I need to build a similar function in another file
and it's important for me to understand what logic is here?
Thank you very much !!
Leah
See Answer
July 16, 2010
Online
Hi Lea,
This is a super advanced formula that uses concepts typically only known and understood by the developers of Excel, namely broadcasting and pairwise. Here is my explanation of the last part of the formula (note: my dates are dd/mm/yyyy):
CEILING(MONTH(B4),3) - Finds the last month in the quarter. e.g. for 1st Feb 2018 it finds 3 as the last month in the qtr.
SUM(DATE(YEAR(B4),CEILING(MONTH(B4),3)+{-2,1},0) - The +{-2,1} generates two values for the Month argument in the DATE function. i.e. Where B4 = 1st Feb 2018 CEILING(MONTH(B4),3)+{-2,1} returns 1 i.e. (3-2) and 4 i.e. (3+1).
SUM(DATE(YEAR(B4),{1,4},0) - The DATE function therefore returns two dates; 43100 and 43190 (which are 31/12/2017 and 31/3/2019) This is because the zero in the day argument of DATE returns the date prior to the first date of the month in the month argument. i.e. DATE(2018,1,0) returns 31/12/2017 and DATE(2018,4,0) returns 31/3/2018
SUM({43100,43190}*{-1,1}) -*{-1,1} converts the first date to a negative and the second date to a positive so that the SUM function subtracts one from the other. It returns:
SUM({-43100,43190})
Which results in the number of days in the quarter i.e. 90
For an understanding on pairwise and broadcasting I encourage you to watch this video by Joe McDaid from the Excel team at Microsoft where he explains how arrays work in Excel.
Mynda
Answers Post
1 Guest(s)
