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

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[abbyabby]

I have added the ad-in and it says it successfully copies the Mini Sheet to the clipboard but I can't paste it into this.

 

[abbyabby]

Also, when I close the document and reopen I have to re add the ad-in

 

[abbyabby]

sample.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Quantity	Annual Quantity	Item Price	Description	Amount	Original Price	2022 price rise	Annual Sales
2	customer 1		642				$2.50	needs to return last figure before blank cell in column F for customer 1	$1,605.00
3	10/08/2022	40		$2.50	product	$100.00
4	22/09/2022	30		$2.50	product	$75.00
5	6/10/2022	37		$2.50	product	$92.50
6	20/10/2022	20		$2.50	product	$50.00
7	9/11/2022	35		$2.50	product	$87.50
8	16/11/2022	50		$2.50	product	$125.00
9	15/12/2022	37		$2.50	product	$92.50
10	27/12/2022	60		$2.50	product	$150.00
11	5/01/2023	50		$2.50	product	$125.00
12	17/01/2023	45		$2.50	product	$112.50
13	31/01/2023	32		$2.50	product	$80.00
14	22/02/2023	35		$2.50	product	$87.50
15	16/03/2023	43		$2.50	product	$107.50
16	6/04/2023	50		$2.50	product	$125.00
17	24/04/2023	31		$2.50	product	$77.50
18	30/05/2023	47		$2.50	product	$117.50
19	customer 2		560				$2.00	needs to return last figure before blank cell in column F for customer 2	$1,171.00
20	11/08/2022	50		$2.00	product	$100.00
21	13/10/2022	45		$2.10	product	$94.50
22	11/11/2022	40		$2.10	product	$84.00
23	9/12/2022	27		$2.10	product	$56.70
24	20/12/2022	-1		$2.10	product	-$2.10
25	20/12/2022	45		$2.10	product	$94.50
26	23/12/2022	28		$2.10	product	$58.80
27	6/01/2023	40		$2.10	product	$84.00
28	30/01/2023	40		$2.10	product	$84.00
29	17/02/2023	40		$2.10	product	$84.00
30	28/02/2023	58		$2.10	product	$121.80
31	5/04/2023	60		$2.10	product	$126.00
32	24/05/2023	50		$2.10	product	$105.00
33	23/06/2023	38		$2.10	product	$79.80
34	customer 3		897				$2.00	needs to return last figure before blank cell in column F for customer 3	$1,873.70
35	28/09/2022	100		$2.00	product	$200.00
36	9/11/2022	105		$2.10	product	$220.50
37	6/12/2022	60		$2.10	product	$126.00
38	16/12/2022	-20		$2.10	product	-$42.00
39	16/12/2022	120		$2.10	product	$252.00
40	23/12/2022	30		$2.10	product	$63.00
41	29/12/2022	100		$2.10	product	$210.00
42	4/01/2023	100		$2.10	product	$210.00
43	19/01/2023	60		$2.10	product	$126.00
44	23/02/2023	100		$2.10	product	$210.00
45	21/03/2023	62		$2.10	product	$130.20
46	3/04/2023	20		$2.10	product	$42.00
47	19/04/2023	60		$2.10	product	$126.00
48	customer 4		1025				$2.00		$2,138.50
Sheet3

Cell Formulas
Range	Formula
C2:C48	C2	=IF(B2="",SUM(B2:INDEX(B2:$B$74,MATCH(TRUE,(B3:$B$74=""),0))),"")
G2:G48	G2	=IF(D2="",D3,"")
I2:I48	I2	=IF(F2="",SUM(F2:INDEX(F2:$F$74,MATCH(TRUE,(F3:$F$74=""),0))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

needs to return last figure before blank cell in column F for customer ...

Is this what you mean then?

23 08 10.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="","",XLOOKUP("?",G3:G$100&"",F2:F$99,,2))

 

[abbyabby]

Not exactly. I

Mine just returns #NAME? when I paste that in. What am I doing wrong??

 

[abbyabby]

Actully, since installing the X2bb add-in everything is returned #NAMe or #VALUE/!

 

[Peter_SSs]

Not sure why you quoted your own post 5 (twice)? I have removed those quotes.

Mine just returns #NAME? when I paste that in. What am I doing wrong??

#NAME? means that you have used a function that does not exists in your Excel version. Your profile says Office 365 and if you do have that version you should have XLOOKUP as used in my suggestion.

Actully, since installing the X2bb add-in everything is returned #NAMe or #VALUE/!

XL2BB should not cause that behaviour and with the many hundreds of users who have installed XL2BB & used I don't believe anybody has reported such an outcome. If that is still happening it might be that your Excel version has gone wrong and you may need to do a repair or Reinstall.

 

[abbyabby]

Not sure why you quoted your own post 5 (twice)? I have removed those quotes.


#NAME? means that you have used a function that does not exists in your Excel version. Your profile says Office 365 and if you do have that version you should have XLOOKUP as used in my suggestion.


XL2BB should not cause that behaviour and with the many hundreds of users who have installed XL2BB & used I don't believe anybody has reported such an outcome. If that is still happening it might be that your Excel version has gone wrong and you may need to do a repair or Reinstall.

I did a repair and also checked my Trust Centre settings but nothing worked. I ended up doing ctrl + shift + enter and it brought the answers back, but if I go into the cell to edit it I have to ctrl + shift + enter again to get it to work again.