Formula to get first name from 2 formats either (Lastname, First) or (Firstname Last) formats.
Category: Formulas – combined functions
Two or more functions = One Formula, how to combine more than one function to present a powerful formula that does the job well
Search across sheets in function
Just when you need to search for a thing across sheets in a workbook.
I am bored, so I am adding articles from my old archive.
Min Max vehicle
Finding which vehicle with minimum (or maximum) amount of savings among peers.
This is an old request, found its formula in my archive to calculate which of the 4 columns is actually has the minimum (or maximum) of savings among other columns.
Trick is as you guessed it was OFFSET.
0% interest promotion
The past few months, I established a formula to help me determine if certain credit card promotion makes sense or not.
This helped me a lot in consolidating my debts (credit card as well others) into interest-free payments. Benefiting from banks and cards promotions. The key part is to determine how much monthly I will commit to pay in order to keep it actually 0%.
So here is the formula.
VALUE() of LEFT()
I have been in a lot of situation needing to convert a number came out from a string into actual numbers. Sometimes N() worked, sometimes not, others VALUE() worked, but I did not see a function-does-all solution.
Schedule column
Create list of dates excluding weekends and holidays.
Basically create list of workdays in a certain year, you may fill in to get more.
List of holidays is defined in another column, formula already excludes Saturdays and Sundays.
GoLast to jump to last cell in a column
Edit 2021-07-12: I needed this formula to work in a worksheet having space in its name, so original one did not work, here is the new version: =HYPERLINK(“[“&MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-1)&”]'”&MID(CELL(“filename”,A1),SEARCH(“]”,CELL(“filename”,A1))+1,500)&”‘!B”&COUNT(B:B)+6,”Go Last”) Again, above formula works in a worksheet with space in its name (and any other character), it already works in a workbook with space in its…
CHOOSE – tree of decisions
If you are like me, then you must have needed to use CHOOSE functionThis is basically like “SELECT CASE” statement, but more limited So, in few words, you can derive the flow of formulas to go to function1, function2, function3, or function4 based on an integer.More like a nested IF, it can actually replaces a…
Count Unique
A question by a colleague on how to count number of unique items in a column made me realize, I don’t have that here.
So here it is …
Fixed length ID
I needed few months back to make a fixed-length ID iut if none-fixed-length numbers.
SO I used the function REPT with RIGHT to force all numbers to be converted into fixed-length ID (mainly having leading zeros)
And here is the formula
SimpleANBox
This is a financial box, a saving account, a credit, a loan, or any other financial amount that has input and output.
During my time with technology (since 1997) I found myself needing to open a lot of these boxes, to track accounts, or loans, or virtual business partnerships between multiple partnerrs. And this is the core to it.