Re: [libreoffice-users] dynamic range (based on current date/month)

On 07/21/2014 09:19 AM, Brian Barker wrote:
> At 13:47 20/07/2014 -0400, Charles Marcus wrote:
> > ... we keep sales for Sales by month, with the Sales Rep in a Row and 
> > each month in a column... So something like this:
> > Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct Nov   Dec   
> > YTD
> > Sales Rep
> > Rep 1
> > Rep 2
> >
> > What I'm trying to do is come up with a formula that can go in the 
> > YTD column that will SUM only Jan thru Jul for each Rep - and 
> > automatically change to SUM Jan thru Aug once we hit 8/1, etc... 
> > Anyone have a clue? I've tried so many different permutations of 
> > SUMIF(), SUM(INDIRECT()), etc, and just can't figure this one out...
>
> At 08:39 21/07/2014 -0400, Charles Marcus wrote:
> > I have one sheet for the current year, and other sheets for previous 
> > years. [...] The problem is the same column for the prior year 
> > sheets. I need the YTD column for those sheets to sum up each Sales 
> > Reps totals for that past year, but only to the *current* month. 
> > Subsequent months are not zero, so I can't just SUM the entire range.
> >
> > Actually I want it to sum the range from Jan to MONTH(TODAY())-1. So, 
> > through the end of the prior month. Hope this better defines the 
> > problem...
>
> Better, but no cigar. You twice above say to the *current month* 
> (change to August on 1 August; "current month") and twice suggest the 
> previous month (MONTH(TODAY())-1; "prior month"). The formula will 
> differ depending on which you require, of course.
>
> Let's suppose you have the months in columns from B to M. Then try:
> =SUM(Bn:OFFSET(Bn,0,MONTH(TODAY())-1))
>
> The OFFSET() function here takes a reference to Bn - the January cell 
> - and offsets it by MONTH(TODAY))-1 cells to the right. So today, in 
> July, this is six cells to the right, or Hn. The SUM() function sums 
> from Bn to Hn, which is what you require for July.
>
> If you want the sum to the previous month, you could start with:
> =SUM(Bn:OFFSET(Bn,0,MONTH(TODAY())-2))
> - but this would not work for January, where you actually need to sum 
> nothing. You probably need to handle that as a special case by 
> including an IF() in your expression:
> =IF(MONTH(TODAY())=1,0,SUM(Bn:OFFSET(Bn,0,MONTH(TODAY())-2)))
>
> I trust this helps.
>
> Brian Barker
     Perhaps I have databases on the brain, but a database seems like a 
simpler solution than a spreadsheet.
     What is confusing to me is the YTD column. I thought Year To Date 
meant the total from January to the current date. With this assumption, 
a simple SUM() of the monthly totals for each salesman would always give 
you the year to date total.
     But I still think a database would be better. With it you could 
enter the daily (or weekly) sales of each representative and let the 
database do the all the calculations for you including weekly, monthly, 
year to date, and annual totals in one or more queries. It likely will 
require the use of SQL in the queries though. Specifically, it may 
require the use of the GROUP BY clause and doing this in SQL View.

Dan

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted