Average/Sum formula with offset
 

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

Average/Sum formula with offset
 

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" wrote in message
...
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

Average/Sum formula with offset
 

On Mon, 6 Dec 2010 02:14:59 -0800 (PST), Stav19
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.

Average/Sum formula with offset
 

On Dec 6, 11:51*am, Ron Rosenfeld wrote:
On Mon, 6 Dec 2010 02:14:59 -0800 (PST), Stav19





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

Average/Sum formula with offset
 

On Dec 6, 12:08*pm, Stav19 wrote:
On Dec 6, 11:51*am, Ron Rosenfeld wrote:





On Mon, 6 Dec 2010 02:14:59 -0800 (PST), Stav19

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

Average/Sum formula with offset
 

On Mon, 6 Dec 2010 04:08:08 -0800 (PST), Stav19
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.

Average/Sum formula with offset
 

On Dec 6, 12:55*pm, Ron Rosenfeld wrote:
On Mon, 6 Dec 2010 04:08:08 -0800 (PST), Stav19

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

Average/Sum formula with offset
 

On Mon, 6 Dec 2010 05:45:05 -0800 (PST), Stav19
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))

Average/Sum formula with offset
 

On Dec 6, 1:57*pm, Ron Rosenfeld wrote:
On Mon, 6 Dec 2010 05:45:05 -0800 (PST), Stav19

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!

Average/Sum formula with offset
 

On Mon, 6 Dec 2010 06:06:41 -0800 (PST), Stav19
wrote:

On Dec 6, 1:57*pm, Ron Rosenfeld wrote:
On Mon, 6 Dec 2010 05:45:05 -0800 (PST), Stav19

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!

Glad to help. Thanks for the feedback.