Connect with us

There’s a lot going on in these formulaic conditional rules in Microsoft Excel that highlight the smallest and largest values within a period of years.

Image: iStock/Rawpixel

Excel’s MINIFS() and MAXIFS() functions identify the lowest and highest values within a range, respectively, depending on one or more conditions. If the condition happens to have conditions of its own, these functions take on a new level of difficulty. In this article, I’ll show you two conditional format rules that highlight the minimum and maximum number within a range of years. We’re not looking for a single year as the condition! Rather, it can be any number of years inclusive on the first and last years in the range.

SEE: 60 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. The browser edition will support these functions. For your convenience, you can download the .xlsx demonstration file.

About MINIFS() and MAXIFS()

These two functions are super easy to use, taking the simple form

MINIFS(minrange,criteriarange1,criteria1[,criteriarange2,criteria2],…)

MAXIFS(maxrange,criteriarange1,criteria1[,criteriarange2,criteria2],…)

In a nutshell, these functions return either the minimum or maximum value within a range where criteria returns true. When a function has multiple criteria arguments, all must return true. Both functions work with values and dates, and the values don’t need to be sorted beforehand.

SEE: How to highlight unique values in Excel (TechRepublic)

The criteria

Now let’s look at the data we want to evaluate in Figure A. The dates in column D are from five years. Even without functions it’s easy to discern that 2020 is the most recent year, and 2010 is the least recent. We want to highlight the minimum and maximum value in column D within a period of years (the criteria).

Figure A

excelminmaxyear-a.jpg

  We’ll use the MINIFS() and MAXIFS() functions to set a conditional format.

If you follow my articles, you know that I like to break things down into helper functions. You don’t have to use them but doing so is easy and it helps visualize how everything works together. Our first step is to create input cells for the first and last years in the year range: These are in D1:D2. The criteria, or condition, will include all the years inclusive of both years. Because there’s so much going on, we’ll break things down into simpler expressions and then combine them to create the conditional format rules.

SEE: How to use shortcuts to sort in Microsoft Excel (TechRepublic)

The expressions

The first expression returns TRUE or FALSE. When TRUE, the corresponding date in column C falls within the conditional period of years; FALSE means the date doesn’t. We’re using an AND operator that uses equality operators to determine whether each year fits the condition or not.

Enter the first expression

=AND(YEAR($C4)>=$D$1,YEAR($C4)<=$D$2)

into F4 and copy to F17. Pay close attention to the relative and absolute references—they matter. If the year in column C is equal to or greater than the Begin date in D1 and that same year is equal to or less than the End date in D2, this function returns TRUE and FALSE if not. In Figure B, you can see that there are seven years in 2019 or 2020. 

Figure B

excelminmaxyear-b.jpg

  Seven dates are in 2019 or 2020.

We now know which dates fulfill the year period criteria—the TRUE values in column F tell us that. Next we need to know which value in column D is the highest or lowest, but only evaluating the values when the corresponding values in column F are TRUE. 

SEE: 3 ways to suppress zero in Excel (TechRepublic)

The next two expressions, shown in Figure C, return the highest and lowest values inclusive of the two dates, respectively:

=MAXIFS($D$4:$D$17,$F$4:$F$17,TRUE)

=MINIFS($D$4:$D$17,$F$4:$F$17,TRUE)

Figure C

excelminmaxyear-c.jpg

  The functions in columns G and H return the highest and lowest values inclusive of the dates.

The criteria range is the AND operator expression in column F; the criteria is the TRUE value. In a nutshell, the MAXIFS() and MINIFS() functions evaluate only those values in column D where the value in column F is TRUE.

At this point, you have the conditional rules:

=$D4=MAXIFS($D$4:$D$17,$F$4:$F$17,TRUE)

=$D4=MINIFS($D$4:$D$17,$F$4:$F$17,TRUE)

You could hide column F, or not. You no longer need the functions in columns G and H—we just worked through those so you could visually work through the logic. Leave them for now so you can watch them update in the next section. 

SEE: How to easily include dynamic dates in a Word doc using Excel (TechRepublic)

The conditional rule

Now that we have our two formulaic rules, let’s enter them and see how they work. To get started, select B4:D17 and then do the following:

  1. On the Home tab, click Conditional Formatting in the Styles group.
  2. Choose New Rule from the dropdown.
  3. In the resulting dialog, select the last option, Use a Formula to…, in the top pane
  4. In the lower pane, enter the expression:
    =$D4=MAXIFS($D$4:$D$17,$F$4:$F$17,TRUE)
  5. Click Format, choose a light blue fill color, and click OK.
  6. Figure D shows the expression and the fill format. Click OK.

Figure D

excelminmaxyear-d.jpg

  Enter the formulaic rule.

As you can see in Figure E, the record with the largest value in column D that falls within the years 2017 and 2018 is in row 6. The MAXIFS() function in column G verifies it. To enter the second rule, repeat the instructions above, entering the expression

=$D4=MINIFS($D$4:$D$17,$F$4:$F$17,TRUE)

during step 4. Figure F shows both rules in place. Now, spend some time, entering different years in the input cells D1 and D2. You can use the updating values in columns G and H to confirm that the rule is working.

Figure E

excelminmaxyear-e.jpg

  The first rule in place. 

Figure F

excelminmaxyear-f.jpg

If you enter a year that isn’t represented by a date value in column C, the rules continue to work. If neither date is represented, everything continues to work, but you might not realize why. Specifically, neither rule will be satisfied, so no record will be highlighted.

Also see

Source link

0
Continue Reading

Technology

Apple TV+ Free Trial Subscription to Be Extended Till July for Eligible Customers: Report

apple tv plus 1610803950685

Apple TV+ free trial subscription will reportedly be extended once again for existing free trial users. All Apple TV+ users whose one-year free trial was going to expire sometime before June, will now instead be able to enjoy free access till July 2021, a new report suggests. The one-year Apple TV+ free trials were offered with new purchases of the iPhone, iPad, Mac, and Apple TV. All free trials of users who initially subscribed to the service when it launched, were set to expire on November 1, 2020. This deadline was extended to February 2021 last year, and now Apple has further extended it till July.

9to5Mac reports that the delay for this extension could be due to postponement of shooting Apple TV+ originals due to the pandemic. Customers who availed this free trial when the service launched, will now enjoy additional nine months of free access to Apple TV+. This new additional six month of extension is reportedly done by Apple to introduce new series in its content catalogue and increase the value proposition, before it begins to ask for a fee.

As mentioned, this is the second extension announced by Apple of the free trial that was slated to end last year in November. The report says that all eligible customers will be notified of this extension via email in the next couple of weeks. Existing paying subscribers will reportedly be compensated with store credit refunds to offset the cost of subscription.

Apple TV+ upcoming titles include Cherry starring Tom Holland, season two of popular series like For All Mankind, The Morning Show, and even See. All of these should release some time this year, after inevitable production delays last year due to pandemic restrictions.

Even now, Apple TV+ is offered for free for one year when you purchase an Apple device and redeem the offer within 90 days. Monthly subscription of Apple TV+ in India is Rs. 99 per month. It is also bundled with an Apple One subscription.


Is HomePod mini the best smart speaker under Rs. 10,000? We discussed this on Orbital, our weekly technology podcast, which you can subscribe to via Apple Podcasts, Google Podcasts, or RSS, download the episode, or just hit the play button below.

Source link

0
Continue Reading

Technology

The 8 best microphones to help you sound better in your next video meeting

elgatoe

There’s plenty of technology available to help improve the quality of our virtual calls. A top-of-the-line microphone is a great start.

In recent weeks, there’s been a surge in video conferencing as many teams operate remotely due to the coronavirus pandemic. Needless to say, it only takes a few Zoom calls to realize that a standard laptop microphone simply cannot deliver high-quality audio. While some companies have provided employees with a stipend to upgrade their home office setups, many were not as lucky.

Fortunately, there’s plenty of tech to improve the quality of these virtual conferences and an aftermarket microphone is a smart way to immediately give your audio a healthy boost. Ranging from high-end broadcast-style microphones to lightning port audio accessories for recordings on the go, there’s certainly no shortage of models to choose from. Without further ado, here are some of the best microphones for the home studio.





Image: Amazon

When it comes to top-notch microphones, Blue is one of the heavyweights in the market. The Blue Yeti USB microphone is one of the manufacturer’s more versatile devices. This model comes with four different pattern modes from optimal sound quality in a host of situations. For example, those who are recording music or simply tuning in for a conference call may prefer the cardioid mode to capture the audio produced immediately in front of the unit. To more aptly record conversations between two people in the same room, the bi-directional mode captures audio from the front- and back-side of the microphone. Anyone in the market for a high-end microphone for the home studio should give this well-rounded, multipurpose mic a long look.


$130 at Amazon



Elgato.jpg

Image: Elgato

The Elgato Wave:3 is a versatile microphone for remote workers, gamers, and musicians alike. The microphone has a steel external grill that protects the internal components and uses a cardioid polar pattern to capture audio. The back of the device features a USB Type-C port and a headphone output. The front-facing dial adjusts headphone volume, input, and more. The stand features a u-mount for easy adjustments, and the padded base keeps the unit firmly in place. A mute feature allows you to cut the audio as needed, and Elgato offers a pop filter (sold separately) to further minimize audio disturbances.


$138 at Amazon



hyperx.jpg

Image: HyperX

The HyperX SoloCast USB microphone is a great option for frequent Zoom conference attendees; especially those who enjoy gaming and streaming after the workday. To prevent audio mishaps, the model features a dedicated mute button as well as an LED indicator to ensure the mic is muted or unmuted as intended. For added functionality, the model is also compatible with PS4 and popular streaming platforms.


$60 at Best Buy



microphone, best microphones

IMAGE: B&H

With a steel body, matte chrome finish, and zinc die-cast elements, the HEiL Sound PR-40 certainly looks the part of a first-rate microphone. Also, the internal technology backs up the image. The cardioid pickup pattern is ideal for broadcast-style audio capturing sound directly in front and to the immediate sides of the microphone. A built-in Sorbothane shock mount reduces the risk of interference and this design includes a pair of mesh screens to improve sound quality. For additional peace of mind, this particular model comes with a limited three-year warranty.


$322 at B&H



microphone

While a more rudimentary microphone with minimal audio accouterments might fly for some, others might want to go all-in on a full home studio microphone setup. This FIFINE model includes a quality mic as well as many standard accessories for under $100. The home studio kit comes with an adjustable scissor arm for added versatility and precision placement. The included double pop filter is designed to reduce airflow immediately toward the instrument minimizing audio “pops” during recording. The package also comes with a microphone tripod stand for those so inclined.


$62 at Amazon



microphone.jpg

Image: Amazon

The Shure MV5 is a solid compact microphone packaged in a vintage master of the airwaves build. Three separate preset modes (instrument, flat, and vocals) provide optimal sound quality based on the task at hand. The microphone itself easily detaches from the aluminum mount for a more low-profile tabletop fit. As is the case with other MOTIV products, this microphone also comes with the ShurePlus MOTIV app enabling users to more precisely fine-tune their recording quality and share these files.


$100 at Amazon



microphone-5.jpg

Image: Amazon

Not everyone in the market for a high-quality USB microphone is looking for a personal home studio. This JUNIVO model acts as an excellent no-frills microphone with plenty of thoughtful design touches. The adjustable gooseneck mic body provides excellent maneuverability and the included noise-cancellation technology keeps audio crisp and clear. A central LED-equipped mute button along the base allows you to quickly cut the mic without searching from the digital button in the Zoom room. This is the perfect feature for those with pets roaming the home office.
At just four inches in diameter, the model is also appreciatively compact and ideal for desktops with limited space.


$26 at Amazon



microphone-9.jpg

IMAGE: Shure

There are many situations where we need to record an audio clip on our portable devices. In fact, some professions depend on leveraging a cell phone as a modern dictaphone. Unfortunately, the low-quality onboard microphones on these devices can make transcription difficult and render live musical performances painfully inaudible. The MV88 is an exceptional microphone option for devices with Lightning ports and an adjustable joint along the mount allows users to focus the microphone closer to the sheets of sound. The aforementioned ShurePlus MOTIVE Audio apps grants users greater control over these audio files including trimming, sharing, and fine-tuning clips.


$150 at Amazon

Source link

0
Continue Reading

Technology

Studying Chaos with One of the World’s Fastest Cameras

Optical Chaos 01sm.max 250x250 1

There are things in life that can be predicted reasonably well. The tides rise and fall. The moon waxes and wanes. A billiard ball bounces around a table according to orderly geometry.

And then there are things that defy easy prediction: The hurricane that changes direction without warning. The splashing of water in a fountain. The graceful disorder of branches growing from a tree.

Studying Chaos with One of the Worlds Fastest Cameras

A so-called chaotic optical cavity is designed in such a way that a beam of light reflecting off its interior surfaces will never follow the same path twice. Image credit: Caltech

These phenomena and others like them can be described as chaotic systems, and are notable for exhibiting behaviour that is predictable at first but grows increasingly random with time.

Because of the large role that chaotic systems play in the world around us, scientists and mathematicians have long sought to better understand them. Now, Caltech’s Lihong Wang, the Bren Professor in the Andrew and Peggy Cherng Department of Medical Engineering, has developed a new tool that might help in this quest.

In the latest issue of Science Advances, Wang describes how he has used an ultrafast camera of his own design that recorded video at one billion frames per second to observe the movement of laser light in a chamber specially designed to induce chaotic reflections.

“Some cavities are non-chaotic, so the path the light takes is predictable,” Wang says. But in the current work, he and his colleagues have used that ultrafast camera as a tool to study a chaotic cavity, “in which the light takes a different path every time we repeat the experiment.”

The camera makes use of a technology called compressed ultrafast photography (CUP), which Wang has demonstrated in other research to be capable of speeds as fast as 70 trillion frames per second. The speed at which a CUP camera takes video makes it capable of seeing light—the fastest thing in the universe—as it travels.

But CUP cameras have another feature that makes them uniquely suited for studying chaotic systems. Unlike a traditional camera that shoots one frame of video at a time, a CUP camera essentially shoots all of its frames at once. This allows the camera to capture the entirety of a laser beam’s chaotic path through the chamber all in one go.

That matters because, in a chaotic system, the behaviour is different every time. If the camera only captured part of the action, the behaviour that was not recorded could never be studied, because it would never occur in exactly the same way again. It would be like trying to photograph a bird, but with a camera that can only capture one body part at a time; furthermore, every time the bird landed near you, it would be a different species. Although you could try to assemble all your photos into one composite bird image, that cobbled-together bird would have the beak of a crow, the neck of a stork, the wings of a duck, the tail of a hawk, and the legs of a chicken. Not exactly useful.

Wang says that the ability of his CUP camera to capture the chaotic movement of light may breathe new life into the study of optical chaos, which has applications in physics, communications, and cryptography.

“It was a really hot field some time ago, but it’s died down, maybe because we didn’t have the tools we needed,” he says. “The experimentalists lost interest because they couldn’t do the experiments, and the theoreticians lost interest because they couldn’t validate their theories experimentally. This was a fun demonstration to show people in that field that they finally have an experimental tool.”

Source: Caltech




Source link

0
Continue Reading

Trending