Excel has tons of of features, and I actually do not know what all of them do. However once in a while, I discover one which makes me marvel why I would by no means come throughout it earlier than. Listed below are seven that caught me without warning.
GESTEP: Verify whether or not a quantity has reached a threshold
A tiny perform for a standard query
GESTEP checks whether or not a quantity has reached a specified threshold, returning 1 if it has and 0 if it hasn’t. Say you are monitoring your each day steps and need Excel to flag the times whenever you reached your 10,000-step objective:
=GESTEP(B2,$E$2)
the place B2 incorporates the variety of steps, and E2 incorporates the goal.
It is basically the identical end result as =IF(B2>=$E$2,1,0), however GESTEP makes the aim instantly apparent: you are testing whether or not the quantity has reached your goal.
That makes it helpful for monitoring on a regular basis issues, from steps and energy to financial savings and factors. As a result of the result’s numeric, you too can use it straight in additional calculations.
CONVERT: Swap between measurements
Cease attempting to find conversion elements
CONVERT switches a worth from one measurement unit to a different. Suppose you are taking a look at a chunk of furnishings on-line and its dimensions are given in inches, however you’ve got measured the area in centimeters. You possibly can enter this:
=CONVERT(B2,$B$1,$C$1)
the place B2 incorporates the worth in inches, B1 incorporates the beginning unit, and C1 incorporates the goal unit.
Excel handles the conversion for you, and since the unit codes are in cells, you possibly can change them with out rewriting the formulation. CONVERT helps models for distance, weight, temperature, time, and a number of other different measurements. It is significantly useful for DIY tasks, recipes, journey, or any scenario the place that you must swap between models.
ROMAN: Generate Roman numerals routinely
Even Excel can get slightly theatrical
ROMAN converts extraordinary numbers into Roman numerals. Think about you are making a doc or presentation define and need a listing numbered I, II, III, and so forth as a substitute of 1, 2, 3. The formulation is simple:
=ROMAN(SEQUENCE(10))
SEQUENCE generates the numbers, whereas ROMAN converts them, providing you with a spilled listing of Roman numerals routinely.
You possibly can use the identical trick for sections, phases, or some other numbered listing the place you need one thing slightly extra distinctive. You may not use ROMAN day-after-day, however combining it with SEQUENCE turns an obscure perform right into a surprisingly neat little trick.
REPT: Create mini charts inside cells
Who says a chart wants its personal chart object?
REPT repeats a chunk of textual content as many occasions as you specify. Sure, this sounds mundane, however think about you’ve got made a easy spreadsheet to trace your progress towards a financial savings objective, and also you need every row to indicate a visible indicator with out inserting a chart. If B2 incorporates the goal quantity and C2 incorporates the quantity you’ve got saved up to now, you should utilize this formulation in D2:
=REPT("♥",MIN(10,ROUND(C2/B2*10,0)))&REPT("♡",MAX(0,10-ROUND(C2/B2*10,0)))
This calculates what share of the goal you’ve got reached, caps it at 100%, and turns it right into a row of 10 hearts, utilizing stuffed hearts for the finished portion and empty hearts for the remaining.
You need to use the identical concept for behavior trackers, rankings, or different private tasks the place a easy indicator makes issues simpler to visualise. As a result of the end result lives inside a cell, it stays alongside the info and would not want a separate chart object. Change the underlying numbers and the indicator modifications with them.
Conditional formatting information bars are one other method to visualize numbers, however REPT enables you to select the character you need to repeat.
ABS: See how far a quantity is from one other
Generally the path of the distinction would not matter
ABS returns absolutely the worth of a quantity, eradicating its adverse signal. Suppose you are chopping a number of items for a DIY undertaking, and each has a goal measurement with slightly room for error. This is what you possibly can enter:
=ABS(C2-B2)<=D2
This calculates the distinction between the measurement in C2 and the goal in B2, removes its path, and checks whether or not that distinction is throughout the tolerance in D2. Whether it is, the formulation returns TRUE (which checks the field), and if it is not, it returns FALSE (which leaves the field unchecked).
That is helpful everytime you care concerning the measurement of a distinction relatively than whether or not you are above or under the goal. The identical method works for cooking, health targets, or some other scenario with an appropriate margin of error.
You possibly can additionally use AND to check whether or not a worth falls between two limits, however ABS enables you to calculate the dimensions of the distinction from the goal first, which might be helpful if you wish to use that distinction in an extra calculation.
TRUNC: Discover what number of full issues you possibly can have
Excel cannot provide you with a part of a field
TRUNC removes the fractional a part of a quantity with out rounding it. Say you will have $47 to spend, and every merchandise prices $6. A normal division offers you 7.83 objects, however you possibly can solely purchase seven. As an alternative, use TRUNC:
=TRUNC(A3/B3)
TRUNC returns 7, providing you with the variety of full objects you possibly can even have. The identical concept works for full containers, entire weeks, full groups, or some other amount the place the fraction is not helpful.
Not like FLOOR and CEILING, which spherical to a specified a number of, TRUNC merely removes the fractional portion. Excel has a number of different rounding features too, together with ROUNDUP, ROUNDDOWN, and INT. Whereas a few of these produce the identical end result for constructive numbers, they behave in another way with adverse numbers or whenever you’re rounding to a particular a number of.
MAXIFS: Discover the very best worth that meets your standards
Discovering the largest quantity will get extra fascinating whenever you add situations
MAXIFS finds the most important worth in a spread whereas making use of a number of standards. Think about you’ve got organized your purchases in an Excel sheet with columns for Date, Quantity, Retailer, and Class. Now you need to discover the most important quantity you’ve got spent on the class typed into F2:
=MAXIFS(B2:B11,D2:D11,F2)
the place B2:B11 incorporates the values to judge, D2:D11 incorporates the factors vary, and F2 incorporates the class you are in search of.
You may add one other situation to ask a extra particular query: What is the largest buy you’ve got made in a selected class (F2) at a selected retailer (G2)?
=MAXIFS(B2:B11,D2:D11,F2,C2:C11,G2)
This technique enables you to change the factors cells to analyze totally different mixtures with out filtering your information. And if that you must discover the smallest worth that meets the identical sort of situations, MINIFS works in precisely the identical approach.
Bought an issue? There’s most likely an Excel perform for that
You need not memorize each Excel perform. What issues extra is understanding that Excel most likely has a perform for the oddly particular downside you’ve got simply encountered. The subsequent time you are about to construct a sophisticated workaround, it is likely to be price looking out the perform listing first. You could discover that Excel has already executed the onerous be just right for you.