# Posts by kooala

• ## Nesting formulas

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?

• ## cutting words with Len and Left and vlookup

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

• ## cutting words with Len and Left and vlookup

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 MVIS-US?

• ## cutting words with Len and Left and vlookup

1) In column L, I would like a formula that will change the ticker list in column A from MVIS US EQUITY to MVIS-US or BIGG CN Equity to BIGG-CN. Or GBI US EQUITY to GBI-US .

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

• ## Mapping vintages to sales and different rates

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.

• ## Mapping vintages to sales and different rates

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 2-3, 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

• ## Count Numbers In Visible Rows Only

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?

• ## Count Numbers In Visible Rows Only

If you look at the attached file in Row 41, Column H, the count function is not working properly... it is mis-counting, 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.