formula

[abbyabby]

Hello. I am trying to find a formula that looks at a column of figures with intermittent blank cells that can return the value of the cell above the blank cell. This formula gives me the oldest charged price for a customer

=IF(D2="",D3,"")

But I also need to be able to pull the value in the last cell before the blank cell that divides each customer's stats up occurs in each column

The annual sales are calculated using this formula which adjusts itself as it goes down the column, something like that but only getting the last blank cell's value?
=IF(F2="",SUM(F2:INDEX(F2:$F$74,MATCH(TRUE,(F3:$F$74=""),0))),"")

 

[Peter_SSs]

If you have to use Ctrl+Shift+Enter then it indicates that you are not using Office 365 as shown in your profile. In 365, Ctrl+Shift+Enter is never required. Perhaps you need to edit your account details to include the correct version(s) that you are using?

 

[abbyabby]

If you have to use Ctrl+Shift+Enter then it indicates that you are not using Office 365 as shown in your profile. In 365, Ctrl+Shift+Enter is never required. Perhaps you need to edit your account details to include the correct version(s) that you are using?

I'm sorry, I'm new to this forum and I didn't set my profile to say that, it must be a defalt setting. I'm updating my app to 365 and hopefully this will fix it It never did that prior to downloading the add-in so I assumed it was something to do with a setting in that.

Thank you for trying to help me.

 

[Peter_SSs]

I'm sorry, I'm new to this forum and I didn't set my profile to say that, it must be a defalt setting.

No, the default is to leave all the versions blank so you must have inadvertently checked 365 when setting up your account.

I'm updating my app to 365

You can do that if you want, but, assuming the results in my previous formula are what you want, you can do this without 365 and without Ctrl+Shift+Enter.

23 08 14.xlsm
	F	G	H
1	Amount	Original Price	2022 price rise
2		2.5	117.5
3	100
4	75
5	92.5
6	50
7	87.5
8	125
9	92.5
10	150
11	125
12	112.5
13	80
14	87.5
15	107.5
16	125
17	77.5
18	117.5
19		2	79.8
20	100
21	94.5
22	84
23	56.7
24	-2.1
25	94.5
26	58.8
27	84
28	84
29	84
30	121.8
31	126
32	105
33	79.8
34		2	126
35	200
36	220.5
37	126
38	-42
39	252
40	63
41	210
42	210
43	126
44	210
45	130.2
46	42
47	126
Lookup

Cell Formulas
Range	Formula
G2:G47	G2	=IF(D2="",D3,"")
H2:H47	H2	=IF(G2="","",INDEX(F:F,AGGREGATE(15,6,ROW(F3:F$1000)/(G3:G$1000<>""),1)-1))

 

[abbyabby]

No, the default is to leave all the versions blank so you must have inadvertently checked 365 when setting up your account.


You can do that if you want, but, assuming the results in my previous formula are what you want, you can do this without 365 and without Ctrl+Shift+Enter.

23 08 14.xlsm
	F	G	H
1	Amount	Original Price	2022 price rise
2		2.5	117.5
3	100
4	75
5	92.5
6	50
7	87.5
8	125
9	92.5
10	150
11	125
12	112.5
13	80
14	87.5
15	107.5
16	125
17	77.5
18	117.5
19		2	79.8
20	100
21	94.5
22	84
23	56.7
24	-2.1
25	94.5
26	58.8
27	84
28	84
29	84
30	121.8
31	126
32	105
33	79.8
34		2	126
35	200
36	220.5
37	126
38	-42
39	252
40	63
41	210
42	210
43	126
44	210
45	130.2
46	42
47	126
Lookup

Cell Formulas
Range	Formula
G2:G47	G2	=IF(D2="",D3,"")
H2:H47	H2	=IF(G2="","",INDEX(F:F,AGGREGATE(15,6,ROW(F3:F$1000)/(G3:G$1000<>""),1)-1))

I have 365 on my computer but somehow managed to create a 2010 file... This works now, perfectly in fact, and thank you so very much you are amazing

 

[Peter_SSs]

This works now, perfectly in fact, and thank you so very much ..

You're welcome. Glad we got there in the end. Thanks for the confirmation.