12 Excel Functions You've Never Heard Of

 

12 Excel Functions You've Never Heard Of

There are countless functions in the Excel library…with many new ones popping up.

Stay ahead of the game and get familiar with these functions 👇

Today’s function is REVERSE_TEXT. Returns the text in reverse order.

 

Today’s function is REVERSE_TEXT. Returns the text in reverse order.

BY/ 

Mark ProctorMark Proctor

 

Total Running without scan function

 

Total Running

MAP(C3:C14,LAMBDA(x,SUM(C3:x)))=

=MAP(SEQUENCE(ROWS(C3:C14)),LAMBDA(x,SUM(OFFSET(C3,,,x))))

-------------

Find the missing letters between first and last letters

 

 Find  the missing letters between first and last letters

BY / Meganathan Elumalai

Meganathan Elumalai

Today’s function is DICE_ROLL. Returns random values from the required number of dice

 Today’s function is DICE_ROLL. Returns random values from the required number of dice

Numerous ways in Excel to Accomplish a Task

 

Numerous ways in Excel to Accomplish a Task

Today’s function is SUPER_TEXTJOIN. It joins text based on rows or columns.

 

Today’s function is SUPER_TEXTJOIN. It joins text based on rows or columns.

,LAMBDA(delimiter,ignoreEmpty,range,[by_col]=

IF(by_col,

BYCOL(range,LAMBDA(col,TEXTJOIN(delimiter,ignoreEmpty,col))), BYROW(range,LAMBDA(row,TEXTJOIN(delimiter,ignoreEmpty,row)))))

Today’s function is SUM_BETWEEN_POSITION. Sums the values between a start and end, based on a two-way lookup.

 

Today’s function is SUM_BETWEEN_POSITION. Sums the values between a start and end, based on a two-way lookup.

,LAMBDA(values,vCategory,hCategory,vStart,hStart,vEnd,hEnd=

LET(

position,TOCOL(MAKEARRAY(ROWS(values),COLUMNS(values),LAMBDA(r,c,r&"-"&c))),

startPosition,XMATCH(vStart,vCategory)&"-"&XMATCH(hStart,hCategory),

endPosition,XMATCH(vEnd,vCategory)&"-"&XMATCH(hEnd,hCategory), match,SUM((position>=startPosition)*(position<=endPosition)*TOCOL(values)),

match))

Today’s function is SUPER_TEXTSPLIT. Splits multiple text values into rows and columns.

 

Today’s function is SUPER_TEXTSPLIT. Splits multiple text values into rows and columns.

Today’s function is CAGR. It returns the compound annual growth rate.

 

Today’s function is CAGR. It returns the compound annual growth rate.

=LAMBDA(values, RRI(COUNT(values)-1,INDEX(values,1),INDEX(values,COUNT(values))))

Today’s function is SLICER_SELECTION. Returns the items visible in a Table column. Therefore, these are the items selected by a slicer.

 Today’s function is SLICER_SELECTION. Returns the items visible in a Table column. Therefore, these are the items selected by a slicer.

=LAMBDA(tableColumn,
FILTER(tableColumn,MAP(tableColumn,LAMBDA(cell,SUBTOTAL(3,cell)))))

Today’s function is PAYBACK. It calculates the length of time required for an investment to recover its initial outlay.

 

Today’s function is PAYBACK. It calculates the length of time required for an investment to recover its initial outlay.

=LAMBDA(cashflows,
LET(
cumCF,SCAN(0,cashflows,SUM),
period,XMATCH(0,cumCF,1)-1,
periodCF,INDEX(cumCF,period+1)-INDEX(cumCF,period),
periodFraction,1+INDEX(cumCF,period)/periodCF,
paybackPeriod,period-periodFraction,
result,IF(INDEX(cumCF,1,1)>=0,0,paybackPeriod),
result))

Today’s function is ISWEEKEND. Checks whether a date is a weekend day, and returns TRUE/FALSE.

 

Today’s function is ISWEEKEND. Checks whether a date is a weekend day, and returns TRUE/FALSE.

=LAMBDA(date,WEEKDAY(date,2)>=6)

Today’s function is FLEXIBLE_FILTER. It returns an array of values based on multiple partial text searches.

 Today’s function is FLEXIBLE_FILTER. It returns an array of values based on multiple partial text searches.

=LAMBDA(returnArray,lookupArray,searchStrings,[delimiter],[matchEntireCell],[matchAllSearchStrings],[ifEmpty],
IF(matchEntireCell,
FILTER(returnArray, BYROW(ISNUMBER(XMATCH(lookupArray, TEXTSPLIT(searchStrings, IF(ISOMITTED(delimiter), ",", delimiter)))), IF(matchAllSearchStrings, AND, OR)), ifEmpty),
FILTER(returnArray, BYROW(ISNUMBER(SEARCH(TEXTSPLIT(searchStrings, IF(ISOMITTED(delimiter), ",", delimiter)), BYROW(lookupArray, CONCAT))), IF(matchAllSearchStrings, AND, OR)), ifEmpty)))

Today’s function is CHRISTMAS_JUMPER_DECIDER. Lets you know if you should or should not wear a Christmas Jumper on any given day.

 

Today’s function is CHRISTMAS_JUMPER_DECIDER. Lets you know if you should or should not wear a Christmas Jumper on any given day.

=LAMBDA(date,singing_carols,christms_party,casual_dress_permitted,
IF(OR(singing_carols, christms_party * casual_dress_permitted, (DAY(date) >= 22) * (MONTH(date) = 12), (DAY(date) >= 12) * (MONTH(date) = 12) * casual_dress_permitted),
"Christmas Jumper? Go for it!", "Christmas Jumper? Maybe not a great choice."))

Today’s function is BEST_PIZZA_DEAL; it returns a summary of pizza value.

 

Today’s function is BEST_PIZZA_DEAL; it returns a summary of pizza value.

,LAMBDA(Pizza1_Size,Pizza1_Count,Pizza1_Price,Pizza2_Size,Pizza2_Count,Pizza2_Price=

LET(Pizza1Calc, Pizza1_Price / (PI() * (Pizza1_Size / 2) ^ 2 * Pizza1_Count), 

Pizza2Calc, Pizza2_Price / (PI() * (Pizza2_Size / 2) ^ 2 * Pizza2_Count), 

Variance, Pizza1Calc - Pizza2Calc, 

Choice, IF(Variance <= 0, "Pizza 1", "Pizza 2"), 

Result, HSTACK(

VSTACK(Choice & " is better value.", "", "Pizza 1", "Pizza 2", "Variance"), 

VSTACK("", "Per SQ Inch", Pizza1Calc, Pizza2Calc, Variance)),  

Result))