Help with Offset formula

[EvansB2]

Hi there,

I'm looking to make things more efficient in the example below. Row 8 looks at row 4 and then divides the values in row 4 by 3. This is because row 4 values are presented across quarterly periods (3 months) and I would like to split those values down to monthly values. I have therefore on row 8 I been manually dividing the relevant quarter period by 3. So for example, taking cell F 4 which = 66 that is the total for the 3 months June, July & August and cell D8, E8 & F8 needs to return 1/3 of 66.

Any help would be appreciated.

Regards
Ben

Book3
	A	B	C	D	E	F	G	H	I	J	K
1
2	xxxxxxxxxxxx	Period End	30-Nov-19	28-Feb-20	31-May-20	31-Aug-20	30-Nov-20	28-Feb-21	31-May-21	31-Aug-21	30-Nov-21
3
4	INPUT LINE	2020 Update	0	0	20.00	66.00	20.00
5
6		Period	31-May-20	30-Jun-20	31-Jul-20	31-Aug-20	30-Sep-20	31-Oct-20	30-Nov-20	31-Dec-20	31-Jan-21
7
8	QUARTERLY	CURRENT MODEL FORECAST	6.67	22.00	22.00	22.00	6.67	6.67	6.67
INPUTS2 (2)

Cell Formulas
Range	Formula
C6	C6	=E2
D6:K6	D6	=EOMONTH(C6,1)
C8	C8	=E4/3
D8:F8	D8	=$F$4/3
G8:I8	G8	=$G$4/3

 

[Fluff]

What version of Excel are you using?
If you modify your account details, to show this, it saves members from having to ask.

 

[jasonb75]

Try this in E8 and drag right

=LOOKUP(EOMONTH(C6,2),$C$2:$K$2,$C$4:$K$4)/3

 

[Fluff]

Another option, if you have the new Xlookup function
=XLOOKUP(C6,$C$2:$K$2,$C$4:$K$4,"",1)/3

 

[EvansB2]

Thanks. I've input the LOOKUP formula as don't appear to have the XL function. I'm using excel in MSO 365 ProPlus 64-bit, can I add-in XL function?

I'll update account with excel version.

Once again, thanks

Ben

 

[Fluff]

The Lookup function Jason supplied, works for me
If you are on the semi annual channel, you may not have the Xlookup function yet.

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	xxxxxxxxxxxx	Period End	30/11/2019	28/02/2020	31/05/2020	31/08/2020	30/11/2020	28/02/2021	31/05/2021	31/08/2021	30/11/2021
3
4	INPUT LINE	2020 Update	0	0	20	66	20
5
6		Period	31/05/2020	30/06/2020	31/07/2020	31/08/2020	30/09/2020	31/10/2020	30/11/2020	31/12/2020	31/01/2021
7
8	QUARTERLY	CURRENT MODEL FORECAST	6.666666667	22	22	22	6.666666667	6.666666667	6.666666667
9			6.666666667	22	22	22	6.666666667	6.666666667	6.666666667	0	0
10			6.666666667	22	22	22	6.666666667	6.666666667	6.666666667	0	0
Sheet2

Cell Formulas
Range	Formula
C6	C6	=E2
D6:K6	D6	=EOMONTH(C6,1)
C8	C8	=E4/3
D8:F8	D8	=$F$4/3
G8:I8	G8	=$G$4/3
C9:K9	C9	=XLOOKUP(C6,$C$2:$K$2,$C$4:$K$4,"",1)/3
C10:K10	C10	=LOOKUP(EOMONTH(C6,2),$C$2:$K$2,$C$4:$K$4)/3

 

[EvansB2]

Sorry, I didn't say it worked fine! Looks like I will have to wait for Microsoft.

Ben

 

[Fluff]

My mistake I misread your post & thought you were saying it didn't work.
I suspect that the semi-annual channel will get the new functions in July.