Sage 100

Sage Intelligence Excel formula question. I need to compute quarter to date GLActuals if I know the period. I can string a giant IF statement together but was hoping for something more elegant. Ideas?

If your date is in A1 use this =ROUNDUP(MONTH(A1)/3,0)

@RobertWood that gives me the quarter number but how do I use that in an Excel GLActual formula?

That I don't know.

Well, I can nest a whole bunch of IF functions but if I figure out a better way, I'll post it. This can't be the first time someone has wanted a QTD column on an income statement, surely.

What if you create all 4 quarters and conditionally suppress them based on the qtr?

@JoeUhl might have a good answer.

If you wanted to brute force it, you could use Robert's formula three times as the Period field: =GLActual(blahblah,ROUNDUP(MONTH(A1)/3,0)\*3,blahblah)+GLActual(blahblah,(ROUNDUP(MONTH(A1)/3,0)\*3)+1,blahblah)+GLActual(blahblah,(ROUNDUP(MONTH(A1)/3,0)\*3)+2,blahblah)

Keep it simple. Use Steve's brute force method. (""You ARE the brute squad!"")

Et Tu, Brute?

Inconceivable!

Sage Intelligence, not sure which version you are using, but I believe instead of using Roundup(Month...) you can just use the reference 1,2,3 or 4,5,6 or 7,8,9 etc. at Period in the GL function. It will accept comma separated lists, at least in later versions.

@MoiraGoggin I can use the month reference. I found it will accept a range as well (01 To 03, etc).

That's right, I forget the 'to' works as well.