Hi All

I'm trying to create a formula to use in a monthly report to calculate
YTD sum for PL, and average for Balance sheet.

For P/L I can use the following:

SUM(AZ28:OFFSET(BL28,0,-(12-$C$2)-1),)

Where C2 is the month number.

However with the following figures:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
487 450 405 428 492 443 415 550 357 416 416 442

and the formula:
AVERAGE(AZ6:OFFSET(BL6,0,-(12-$C$2)-1),)

Instead of getting an average of 450, I get 405, and I'm struggling to
resolve this.

Can anyone point out what I'm doing wrong?

Cheers
Ins

If I follow...

To get the average of X cols by 1 row starting at AZ6, where X is the number
in C2, I'd use this formula:

=AVERAGE(OFFSET(AZ6,0,0,1,C2))

"Stav19" <(E-Mail Removed)> wrote in message
news:d4dfd9f6-103d-430f-b662-(E-Mail Removed)...
> Hi All
>
> I'm trying to create a formula to use in a monthly report to calculate
> YTD sum for PL, and average for Balance sheet.
>
> For P/L I can use the following:
>
> SUM(AZ28:OFFSET(BL28,0,-(12-$C$2)-1),)
>
> Where C2 is the month number.
>
> However with the following figures:
>
> Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
> 487 450 405 428 492 443 415 550 357 416 416 442
>
> and the formula:
> AVERAGE(AZ6:OFFSET(BL6,0,-(12-$C$2)-1),)
>
> Instead of getting an average of 450, I get 405, and I'm struggling to
> resolve this.
>
> Can anyone point out what I'm doing wrong?
>
> Cheers
> Ins
>

On Dec 6, 11:51*am, Ron Rosenfeld <r...@nospam.net> wrote:
> On Mon, 6 Dec 2010 02:14:59 -0800 (PST), Stav19
>
>
>
>
>
> <pete.rebe...@gmail.com> wrote:
> >Hi All
>
> >I'm trying to create a formula to use in a monthly report to calculate
> >YTD sum for PL, and average for Balance sheet.
>
> >For P/L I can use the following:
>
> >SUM(AZ28:OFFSET(BL28,0,-(12-$C$2)-1),)
>
> >Where C2 is the month number.
>
> >However with the following figures:
>
> >Jan *Feb *Mar *Apr *May *Jun *Jul * Aug *Sep *Oct *Nov *Dec
> >487 *450 *405 *428 *492 * 443 *415 550 * 357 * 416 *416 *442
>
> >and the formula:
> >AVERAGE(AZ6:OFFSET(BL6,0,-(12-$C$2)-1),)
>
> >Instead of getting an average of 450, I get 405, and I'm struggling to
> >resolve this.
>
> >Can anyone point out what I'm doing wrong?
>
> >Cheers
> >Ins
>
> You need to supply more data.
>
> What is in the other referenced cells than C2.
>
> Which of the posted values above AVERAGE 450? *Using a YTD Averaging
> formula, I can't find any combination that does so.- Hide quoted text -
>
> - Show quoted text -

Hi Ron

Apologies, you're right I wasn't very clear, and I typed the wrong
average...For November YTD, the average should be 442, but I was
calculating 405. C2 had the number of months, so was 11 for November.

Thanks

On Dec 6, 12:08*pm, Stav19 <pete.rebe...@gmail.com> wrote:
> On Dec 6, 11:51*am, Ron Rosenfeld <r...@nospam.net> wrote:
>
>
>
>
>
> > On Mon, 6 Dec 2010 02:14:59 -0800 (PST), Stav19
>
> > <pete.rebe...@gmail.com> wrote:
> > >Hi All
>
> > >I'm trying to create a formula to use in a monthly report to calculate
> > >YTD sum for PL, and average for Balance sheet.
>
> > >For P/L I can use the following:
>
> > >SUM(AZ28:OFFSET(BL28,0,-(12-$C$2)-1),)
>
> > >Where C2 is the month number.
>
> > >However with the following figures:
>
> > >Jan *Feb *Mar *Apr *May *Jun *Jul * Aug *Sep *Oct *Nov *Dec
> > >487 *450 *405 *428 *492 * 443 *415 550 * 357 * 416 *416 *442
>
> > >and the formula:
> > >AVERAGE(AZ6:OFFSET(BL6,0,-(12-$C$2)-1),)
>
> > >Instead of getting an average of 450, I get 405, and I'm struggling to
> > >resolve this.
>
> > >Can anyone point out what I'm doing wrong?
>
> > >Cheers
> > >Ins
>
> > You need to supply more data.
>
> > What is in the other referenced cells than C2.
>
> > Which of the posted values above AVERAGE 450? *Using a YTD Averaging
> > formula, I can't find any combination that does so.- Hide quoted text -
>
> > - Show quoted text -
>
> Hi Ron
>
> Apologies, you're right I wasn't very clear, and I typed the wrong
> average...For November YTD, the average should be 442, but I was
> calculating 405. *C2 had the number of months, so was 11 for November.
>
> Thanks- Hide quoted text -
>
> - Show quoted text -

Hi Jim

That worked a treat, however I must be honest, I don't quite
understand how, could you quickly explain?

Thanks

On Dec 6, 12:55*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Mon, 6 Dec 2010 04:08:08 -0800 (PST), Stav19
>
> <pete.rebe...@gmail.com> wrote:
> >Hi Ron
>
> >Apologies, you're right I wasn't very clear, and I typed the wrong
> >average...For November YTD, the average should be 442, but I was
> >calculating 405. *C2 had the number of months, so was 11 for November.
>
> >Thanks
>
> I get 441.7273 so either the values for the months are a bit
> diffferent than what you've posted, or your rounding the result (or
> displaying it with zero decimals).
>
> I'm still not sure what's where but I would use a formula like:
>
> =average(offset(JanCellRef,0,0,1,C2))
>
> Where JanCellRef is the cell address where you store the January
> results.
>
> What you are doing is manipulating the size of the Offset by setting
> the width parameter to be equal to the number of months.

Hi Ron

The 405 I was getting was with my orginal formula, the one you've got
here, and Jim's one work fine, the 442 is what I was after!

In terms of the formula, I'm happy it works but still not 100% clear
how it works...

If the reference is say AZ, the first part of the formula I get, "0,0"
means 0 rows and 0 columns, I don't get why the height has to be 1
though? I get the width being the number of months.

Thanks

On Dec 6, 1:57*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Mon, 6 Dec 2010 05:45:05 -0800 (PST), Stav19
>
> <pete.rebe...@gmail.com> wrote:
> >If the reference is say AZ, the first part of the formula I get, "0,0"
> >means 0 rows and 0 columns, I don't get why the height has to be 1
> >though? I get the width being the number of months.
>
> "height" = the number of rows to return.
> "width" = the number of columns to return
>
> Does your data span more than one row?
>
> If your data spans more than 1 row, adjust the height parameter to
> fit, or leave it "empty" in which case it will default to the height
> of the original reference.
>
> e.g:
>
> =average(offset(JanCellRef,0,0,,C2))
>
> or even:
>
> =average(offset(JanCellRef,,,,C2))
>
> are all equivalent to the original, if JanCellRef is a single cell.
>
> =average(offset(JanCellRef,0,0,1,C2))

Fantastic, thanks very much Ron, appreciate it!