Google Sheets LAMBDA Blank cells with IF condition

Question

I was trying to automatically add formulas when a new row is inserted in between and I did suceed with using arrayformula but now it gives error of circular dependecy. I do NOT want to use a SCRIPT for this. I tried too many methods like SUMIF, INDIRECT, etc. but it ain't working for me. I have made a DEMO sheet if someone can solve.

Total qty and Total invested was solved by user rockinfreakshow using MAP and LAMBDA.

After that I tried in other columns and after some trial and error I was able to get it working but I am still unable to replicate "WITHOUT LAMBDA" sheet. I am unable to get the blanks. Please a look at the sheet and see if any of you can solve or make the formulas more better.

https://docs.google.com/spreadsheets/d/1cr75E1u9SwnS9C3MpSnS0993MgNsZnnF4Xqm7cAjkyU/edit?usp=sharing

I have written the original formula in "WITHOUT LAMBDA" which gives blank cells properly and trail & error has been done in "WITH LAMBDA" sheet. Wil be greatly helpful if someone can solve.

Thank you :)

Original Question here: Automatic Formula when Inserting a New Row in between in Google Sheet without Circular Dependency

Answer 1

You can try these for Avg Price and Target Price columns. Hopefully you can adapt the remaining 7 column(formulas) based on these 2 since they seem to follow the same design structure and all...

Avg Price

={"Avg Price";map(indirect("B3:B"),indirect("G3:G"),indirect("H3:H"),lambda(etfcode,totalqty,totalinvested,
                  if(etfcode="",,if(etfcode<>offset(etfcode,1,),iferror(totalinvested/totalqty),))))}

Target Price

={"Target Price";map(indirect("I3:I"),lambda(avgprice,if(avgprice="",,avgprice*(1+3%))))}