Last entry ignoring formulae

[ozbeachbum]

I have Formulae in Column "BF" which returns the last entry in each section.
EG: "BF106" 23.24 from range "BF107:BF110".
I am trying to achieve the same action for Column "BG"
EG: "BG82" 23.24 from range "BF83:BF86".
However due to there being Formulae in the sections the same Formula does not work.
Any assistance greatly appreciated.

01 REGISTER holding co.xlsx
	BF	BG
82	00.00	00.00
83		00.00
84		00.00
85		23.24
86		00.00
87
88	00.00	0.00
93
94	00.00	0.00
99
100	00.00	0.00
105
106	23.24	0.00
107	23.24	00.00
108		00.00
109		00.00
110		00.00
111
Primary Entry (2)

Cell Formulas
Range	Formula
BF106:BG106,BF100:BG100,BF94:BG94,BF88:BG88,BF82:BG82	BF82	=IFERROR(INDEX(BF83:BF86,MATCH(1E+100,BF83:BF86,1),1),0)
BG83:BG86,BG108:BG110	BG83	=IF('[02 REG CHR securities.xlsx]Securities Acquired Divested'!BR83>0,'[02 REG CHR securities.xlsx]Securities Acquired Divested'!$CS$50&"."&'[02 REG CHR securities.xlsx]Securities Acquired Divested'!$CS$52,0)

 

[yoseeeph]

use if(,,,)
or merge

 

[Peter_SSs]

A bit simpler for col BF

=IFNA(LOOKUP(9^9,BF83:BF86),0)

For BG try

=LOOKUP(9^9,FILTER(BG83:BG86,BG83:BG86>0,0))

 

[ozbeachbum]

A bit simpler for col BF

=IFNA(LOOKUP(9^9,BF83:BF86),0)

For BG try

=LOOKUP(9^9,FILTER(BG83:BG86,BG83:BG86>0,0))

Hi Peter,
Unfortunately neither work BF returns zero and BG returns #NA.

use if(,,,)
or merge

Thanks for your reply

 

[ozbeachbum]

A bit simpler for col BF

=IFNA(LOOKUP(9^9,BF83:BF86),0)

For BG try

=LOOKUP(9^9,FILTER(BG83:BG86,BG83:BG86>0,0))

Apologies my mistake BF works fine.
Thanks.

 

[Peter_SSs]

Apologies my mistake BF works fine.

That's good but what about BG?

If it is not working can you post some XL2BB sample data with my formula showing #N/A?

 

[ozbeachbum]

Mini sheet as requested.

01 REGISTER holding co.xlsx
	BG
82	#N/A
83	00.00
84	00.00
85	23.24
86	00.00
Primary Entry (2)

Cell Formulas
Range	Formula
BG82	BG82	=LOOKUP(9^9,FILTER(BG83:BG86,BG83:BG86>0,0))
BG83:BG86	BG83	=IF('[02 REG CHR securities.xlsx]Securities Acquired Divested'!BR83>0,'[02 REG CHR securities.xlsx]Securities Acquired Divested'!$CS$50&"."&'[02 REG CHR securities.xlsx]Securities Acquired Divested'!$CS$52,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BG82	Expression	=$H82="S"	text	YES
BG82	Expression	=$H82="T"	text	YES
BG82	Expression	=$H82="F"	text	YES
BG82	Cell Value	>0	text	YES

 

[Peter_SSs]

Thanks. The error indicates (& I should have seen it in your longer formula but did not have the relevant workbooks/worksheets to actually test) that your formulas in BG83:BG86 are returning text values not numbers.

I would suggest changing those formulas so that they do return actual numbers. The formula in BG83 would be changed like this

Remove the red "&" where shown:
=IF('[02 REG CHR securities.xlsx]Securities Acquired Divested'!BR83>0,'[02 REG CHR securities.xlsx]Securities Acquired Divested'!$CS$50&"."&'[02 REG CHR securities.xlsx]Securities Acquired Divested'!$CS$52,0)

Add the blue "+" and pair of parentheses:
=IF('[02 REG CHR securities.xlsx]Securities Acquired Divested'!BR83>0,'[02 REG CHR securities.xlsx]Securities Acquired Divested'!$CS$50+("."&'[02 REG CHR securities.xlsx]Securities Acquired Divested'!$CS$52),0)

 

[ozbeachbum]

Thanks so much, that did the trick.
Really appreciate your expertise and attention to detail.
Cheers.

 

[Peter_SSs]

No problem Glad to help.