How do i combine the fomrulas in columns M,N, and O here so it's just in one column? Do I just nest these or is there a cleaner way to do this?
Posts by kooala


I tried this formula in another cell on your formula to add the hyphen; however, ther ewas a space before the hyphen and it iddnt work when there were 3 letters in ticker:
=IF(LEN(M2)=7,LEFT(M2,5)&""&RIGHT(M2,2),IF(LEN(M2)=7,LEFT(M2,10)&""&RIGHT(M2,2),""))

In L2, formula copied down :
=LEFT(A5,FIND(" ",A5)+2)
Great, and how do I then add in this formula a hyphen between the ticker and US so instead of MVIS it now says MVISUS?

1) In column L, I would like a formula that will change the ticker list in column A from MVIS US EQUITY to MVISUS or BIGG CN Equity to BIGGCN. Or GBI US EQUITY to GBIUS .
I tried this formula: =LEFT(A28,LEN(A28)FIND("US",A28)) But dont know how to adjust it to US or CN but the other issue was that sometimes it brought back the letter E when the ticker had 3 characters instead of four.
2) Ultimately I would like to compare the list of tickers in K and L and see which tickers did not show up in K but did in L

If this is too much even just one piece would help. how build so the 14 stores after 4 quarters is multiplied by 87% from 60%. That would start in 1Q 2022.

Hello,
I have a two part question. I need to make this spreadsheet more legible, more user friendly, and more professional.
1) Formulas: Column F shows new stores each quarter. So, 14 new stores on Row F12 which is Q1 (quarter 1) of 2020  the year is in A12 and the quarter in AB. Now, columns G through J on row 12 show the total sales per quarter for each year. The formulas can change too that I have. What I need to automate is that those 14 stores will have 60% of total sales in year 1, 87% of total sales in years 23, and then be mature at 100% In year 4 . By row 13, there are 6 new stores, but those 14 stores from the prior quarter are still in year 1. Also, of these total sales  so in 202Q1 in column M, it would multiply G by M6 or by 2% royalty in year 1 of the 14 new stores. Once these 14 new stores hit year 2, it is then multiplied by M5 which is a 3% royalty. So M would include the royalties received on these sales. Is there formulas that can automate this and recognize when stores hit each quarter and at what % of sales and what royalty rate to use?
2) If there are any ideas how to make this page more user friendly and professional looking  more formatting  please advise.
Kindly,
Kooala

Re: Count Numbers In Visible Rows Only
I just tried with:
=SUBTOTAL(2,F13:F40) and it is not returning the right count. I looked up that value 2 = count.
Is this the wrong subtotal formula?

If you look at the attached file in Row 41, Column H, the count function is not working properly... it is miscounting, the numbers that appear. It does not work either in F41 and G41. It seems to be having a problem reading the if function I created. Does anyone know how to correct this, or change the if function formula so it doesn't have these issues.