Average/Sum formula with offset

[Stav19]

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

 

[Jim Rech]

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))

 

[Ron Rosenfeld]

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.

 

[Stav19]

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

 

[Stav19]

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

 

[Ron Rosenfeld]

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.

 

[Stav19]

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

 

[Ron Rosenfeld]

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))

 

[Stav19]

"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!

 

[Ron Rosenfeld]

Fantastic, thanks very much Ron, appreciate it!

Glad to help. Thanks for the feedback.