Need Formula To Reference Last Row Of Data & Calculate For Current Row

[Dhira]

Thanks to everyone who helped in the last question here.

I needed it for this question:
How do I use a formula to calculate a new cell based on the last row where data was entered?
See below:

Data.xlsx
	A	B	C	D	E	F
1	ROI %	Action	$ SOLD	# SOLD	ROI % Difference	$ TO BUY
2	29.80%	Hold
3	29.70%	Hold
4	27.70%	Hold
5	28.50%	Hold
6	29.40%	Hold
7	31.80%	Sell	$1,172.12	4951
8	29.80%	Hold
9	29.70%	Hold
10	27.70%	Hold
11	28.50%	Hold
12	29.40%	Hold
13	14.20%	Buy			17.60%	$205.93
14	29.70%	Hold
15	27.70%	Hold
16	28.50%	Hold
17	29.40%	Hold
18	38.40%	Sell	$764.92	3231
19	29.70%	Hold
20	27.70%	Hold
21	28.50%	Hold
22	29.70%	Hold
23	21.50%	Hold
24	18.00%	Hold
25	16.20%	Hold
26	14.20%	Buy			24.20%	$185.25
27	29.70%	Hold
28	27.70%	Hold
29	28.50%	Hold
Sheet5

I'd like the "To Buy" column to show the answer only when the condition is met (difference of ROI% at time of sale and current row ROI % being greater than 15%), otherwise, shwoing nothing.
In the above, I'd like to get rid of column D and simply have one formula in Column F that I can drag & copy down.

To wit, the formula structure should basically be:

If Column A (@ row of last sale) - Column A (@ current row) > 15%, Calculate difference between % and multiply it by the dollar amount of last sale (Column C), Else, show nothing (blank).

Appreciate your help.

 

[Cubist]

I'm getting different results based on your requirements. Rows 23-25 have ROI > 15%, should it be showing?

Book1 (version 1).xlsb
	A	B	C	D	E
1	ROI %	Action	$ SOLD	Difference in ROI	$ TO BUY
2	0.298	Hold
3	0.297	Hold
4	0.277	Hold
5	0.285	Hold
6	0.294	Hold
7	0.318	Sell	$ 1,172.12	0.00
8	0.298	Hold		0.02
9	0.297	Hold		0.02
10	0.277	Hold		0.04
11	0.285	Hold		0.03
12	0.294	Hold		0.02
13	0.142	Buy		0.18	$ 206.29
14	0.297	Hold		0.02
15	0.277	Hold		0.04
16	0.285	Hold		0.03
17	0.294	Hold		0.02
18	0.384	Sell	$ 764.92	0.00
19	0.297	Hold		0.09
20	0.277	Hold		0.11
21	0.285	Hold		0.10
22	0.297	Hold		0.09
23	0.215	Hold		0.17	$ 129.27
24	0.18	Hold		0.20	$ 156.04
25	0.162	Hold		0.22	$ 169.81
26	0.142	Buy		0.24	$ 185.11
27	0.297	Hold		0.09
28	0.277	Hold		0.11
29	0.285	Hold		0.10
Sheet7

Cell Formulas
Range	Formula
D2:D29	D2	=IFERROR(LOOKUP(2,1/($B$2:B2="Sell"),$A$2:A2)-A2,"")
E2:E29	E2	=LET( ROI_sell,LOOKUP(2,1/($B$2:B2="Sell"),$A$2:A2), IFERROR(IF(ROI_sell-A2>15%,LOOKUP(2,1/($B$2:B2="Sell"),$C$2:C2)*(ROI_sell-A2),""),"") )

 

[Vogateer]

I had a different formula starting with E2, but I'm getting the same results as Cubist.

=IFERROR(LET(last_row,MAX(ROW($A1:$A$2)*ISNUMBER($A1:$A$2)*($B1:B$2="Sell")),diff, INDIRECT("A" & last_row)-A2, IF(diff>0.15,diff*INDIRECT("C" & last_row), "")), "")

 

[Dhira]

I'm getting different results based on your requirements. Rows 23-25 have ROI > 15%, should it be showing?

Book1 (version 1).xlsb
	A	B	C	D	E
1	ROI %	Action	$ SOLD	Difference in ROI	$ TO BUY
2	0.298	Hold
3	0.297	Hold
4	0.277	Hold
5	0.285	Hold
6	0.294	Hold
7	0.318	Sell	$ 1,172.12	0.00
8	0.298	Hold		0.02
9	0.297	Hold		0.02
10	0.277	Hold		0.04
11	0.285	Hold		0.03
12	0.294	Hold		0.02
13	0.142	Buy		0.18	$ 206.29
14	0.297	Hold		0.02
15	0.277	Hold		0.04
16	0.285	Hold		0.03
17	0.294	Hold		0.02
18	0.384	Sell	$ 764.92	0.00
19	0.297	Hold		0.09
20	0.277	Hold		0.11
21	0.285	Hold		0.10
22	0.297	Hold		0.09
23	0.215	Hold		0.17	$ 129.27
24	0.18	Hold		0.20	$ 156.04
25	0.162	Hold		0.22	$ 169.81
26	0.142	Buy		0.24	$ 185.11
27	0.297	Hold		0.09
28	0.277	Hold		0.11
29	0.285	Hold		0.10
Sheet7

Cell Formulas
Range	Formula
D2:D29	D2	=IFERROR(LOOKUP(2,1/($B$2:B2="Sell"),$A$2:A2)-A2,"")
E2:E29	E2	=LET( ROI_sell,LOOKUP(2,1/($B$2:B2="Sell"),$A$2:A2), IFERROR(IF(ROI_sell-A2>15%,LOOKUP(2,1/($B$2:B2="Sell"),$C$2:C2)*(ROI_sell-A2),""),"") )

You're right it should show for those rows - my bad for not checking the conditions for those rows.

However my excel sheet is showing "#NAME?" error in column E (The formula contains unrecognized text).
Could that be due to my version of excel? I'm using Microsoft Office Professional Plus 2019....

 

[Cubist]

Your profile says 2021...try this.

=IFERROR(IF(LOOKUP(2,1/($B$2:B2="Sell"),$A$2:A2)-A2>15%,LOOKUP(2,1/($B$2:B2="Sell"),$C$2:C2)*(LOOKUP(2,1/($B$2:B2="Sell"),$A$2:A2)-A2),""),"")

 

[Dhira]

I had a different formula starting with E2, but I'm getting the same results as Cubist.

=IFERROR(LET(last_row,MAX(ROW($A1:$A$2)*ISNUMBER($A1:$A$2)*($B1:B$2="Sell")),diff, INDIRECT("A" & last_row)-A2, IF(diff>0.15,diff*INDIRECT("C" & last_row), "")), "")

Thanks! That works also!

Your profile says 2021...try this.

=IFERROR(IF(LOOKUP(2,1/($B$2:B2="Sell"),$A$2:A2)-A2>15%,LOOKUP(2,1/($B$2:B2="Sell"),$C$2:C2)*(LOOKUP(2,1/($B$2:B2="Sell"),$A$2:A2)-A2),""),"")

Yes sir! That works.
I (like a dork), have 2021 on my laptop & 2019 on my desktop lol. Time to upgrade my desktop.

Thank you both!

 

[Dhira]

Guys, sorry, there's a little glitch in the question.
I know I wrote: "In the above, I'd like to get rid of column D and simply have one formula in Column F that I can drag & copy down."
I meant to say "In the above, I'd like to get rid of column E and simply have one formula in Column F that I can drag & copy down." (which was why it was greyed out) - My bad.

The formulas you gave seem to be calculating whether the text "sell" exists in column B, when it should be checking whether the # of units sold exists in Column D.
If so, then it should calculate using the the % difference should be multiplied by whatever's in Column C. ∴ Column C "$ Sold" & D "# Sold" are both needed.
If you could tweak the formula for the original sheet accordingly, I'd appreciate it.

 

[Cubist]

Book1
	A	B	C	D	E
1	ROI %	Action	$ SOLD	# SOLD	$ TO BUY
2	0.298	Hold
3	0.297	Hold
4	0.277	Hold
5	0.285	Hold
6	0.294	Hold
7	0.318	Sell	$ 1,172.12	$ 4,951.00
8	0.298	Hold
9	0.297	Hold
10	0.277	Hold
11	0.285	Hold
12	0.294	Hold
13	0.142	Buy			$ 206.29
14	0.297	Hold
15	0.277	Hold
16	0.285	Hold
17	0.294	Hold
18	0.384	Sell	$ 764.92	$ 3,231.00
19	0.297	Hold
20	0.277	Hold
21	0.285	Hold
22	0.297	Hold
23	0.215	Hold			$ 129.27
24	0.18	Hold			$ 156.04
25	0.162	Hold			$ 169.81
26	0.142	Buy			$ 185.11
27	0.297	Hold
28	0.277	Hold
29	0.285	Hold
Sheet2

Cell Formulas
Range	Formula
E2:E29	E2	=LET( ROI_diff,LOOKUP(2,1/($D$2:D2<>""),$A$2:A2)-A2, IFERROR(IF(ROI_diff>15%,LOOKUP(2,1/($D$2:D2<>""),$C$2:C2)*(ROI_diff),""),"") )

 

[Dhira]

Book1
	A	B	C	D	E
1	ROI %	Action	$ SOLD	# SOLD	$ TO BUY
2	0.298	Hold
3	0.297	Hold
4	0.277	Hold
5	0.285	Hold
6	0.294	Hold
7	0.318	Sell	$ 1,172.12	$ 4,951.00
8	0.298	Hold
9	0.297	Hold
10	0.277	Hold
11	0.285	Hold
12	0.294	Hold
13	0.142	Buy			$ 206.29
14	0.297	Hold
15	0.277	Hold
16	0.285	Hold
17	0.294	Hold
18	0.384	Sell	$ 764.92	$ 3,231.00
19	0.297	Hold
20	0.277	Hold
21	0.285	Hold
22	0.297	Hold
23	0.215	Hold			$ 129.27
24	0.18	Hold			$ 156.04
25	0.162	Hold			$ 169.81
26	0.142	Buy			$ 185.11
27	0.297	Hold
28	0.277	Hold
29	0.285	Hold
Sheet2

Cell Formulas
Range	Formula
E2:E29	E2	=LET( ROI_diff,LOOKUP(2,1/($D$2:D2<>""),$A$2:A2)-A2, IFERROR(IF(ROI_diff>15%,LOOKUP(2,1/($D$2:D2<>""),$C$2:C2)*(ROI_diff),""),"") )

Thanks again. You're like the fastest gun in these excel streets. Played around with this for a bit & it's exactly as needed.