OFFSET WIDTH VALUE PROBLEM

[drluke]

I am trying the sum col AP:BA based on the period number in AN1. The sum formula needs to be dynamic as the period number changes. My problem is that my width argument that takes the first 2 characters from AN1 (which is in a date format) = 44 instead of 08. Is there any way to overcome this problem without having to change the format/type of AN1. If I change the format of AN1 it will cause formulas elsewhere in the spreadsheet to fail. Any advice greatly appreciated.

Macro FAR2 (version 1).xlsb.xlsm
	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA
1	08/21		2	3	4	5	10	20	30	40	50	60	70	80
2			374
Details

Cell Formulas
Range	Formula
AN1	AN1	=C4
AP2	AP2	=SUM(OFFSET(AP1,0,0,1,LEFT(AN1,2)),0)

 

[Toadstool]

Hi DrLuke,

1 August 2021 was the 44,409th day since 1 Jan 1900 (as Excel holds dates as integer days since that date) so you're seeing the 44.

Assuming your date is shown as mm/yy (as we don't see C4 so can't see its origin) then try

=SUM(OFFSET(AP1,0,0,1,MONTH(AN1)),0)

 

[Michael M]

MAybe also....

=SUM(OFFSET(AP1,0,0,1,DAY(AN1)),0)

 

[Alex Blakenburg]

Just a consideration, how many of these formulas are you planning on using ?
Offset is a volatile function and while using a few of them will be of little concern if you are planning on applying it to thousands of rows it may impact your spreadsheet performance.

In which case you could consider using index as per the below. Just set the end of the index range wider than you are ever going to need.

Book2
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1		21/08/2021		2	3	4	5	10	20	30	40	50	60	70	80
2				114
3
Sheet1

Cell Formulas
Range	Formula
AP2	AP2	=SUM(AP1:INDEX(AP1:BZ1,0,MONTH(AN1)))