IF Function in Excel – IFS Function in ZeeMaps

A type of IF function in Excel is the IFS function in which you can input multiple conditions to determine cell values. The function is checked from left to right for the multiple conditions, and the value set for the cell is determined by the first condition that is met. IFS is a much easier way of testing for multiple conditions instead of nesting multiple IF functions.

This statement is extremely helpful when we want to make one field value dependent on another. For example, if the score is => 80, then the Grade is B; if the score is =>90, then Grade is A.

In this post we will show you how to assign different colors to pins or highlighted regions when you upload a spreadsheet. We will use a spreadsheet that has zip codes in the US with a column for number of dealers in that zip code. The sample spreadsheet is attached to the bottom of this post and has just two columns, ‘zip code’ and ‘dealers’. We’ll add a third column – ‘colors’.

Find the Color Name

First, for this task we need to take a look at the color options in ZeeMaps, which are listed here. All ZeeMaps users have access to 32 colors and users on our Enterprise plan have access to 72 colors. To use the extended color palette, please see our blog post.

Color Name for Excel IF Function

For our IF Function, we will need to assign different color values based on values in a given column of our spreadsheet, e.g., number of dealers, grades, etc. For this purpose, note the Color Name in the color list. Use this as the color value in your expression.

If Function Excel Color Choices
IF Function in Excel Color Choice Example

How to write the Excel IF Function

Generally, the syntax for the Excel IFS function is:

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

The Excel IF Function allows you to test up to 127 different conditions.

Note that the conditions need to be entered in the correct order, and can be very difficult to build, test and update if you have entered a large number of conditions.

The Expression – Excel IF Function

We entered the following Excel IF Function expression for our example below. Since our spreadsheet column B contains the number of dealers, we’ll enter an IF Function expression in cell C2 as follows:

=IFS(B2 <= 5, “Green”, B2 <= 10, “Light Yellow”, B2 <= 15, “Yellow”, B2 <= 20, “Red”)

If Function Excel Expression
Excel IF Function Spreadsheet Example

Then, we copy the formula to the rest of the column C and voila, we have colors for each of the zip codes! You can find our example spreadsheet at the end of this tutorial

A look at our IF Function Map

For more info on IF Function Excel visit Microsoft’s detailed instructions for some helpful tips.

Example Data Download

Click the download button below to view our example data excel spreadsheet

Zip Code Look up & Other Region Search Tools

In this blog, we show you how to perform a quick zip code look up in your map, if you have pins representing the zip codes and areas highlighted for your search geography. Start by adding pins and highlighted area(s) on your map.

There are many useful applications for looking up points that fall within a zip code or region.

For instance, you could look up all your customers within a zip code or other boundary outline. Alternatively, you could look up how many service stations are in your state or zip code. In the end, it comes down selecting and summarizing map points inside of a boundary. Essentially, any point feature and any boundary will work. You can even draw your own!

In an earlier post, we had detailed the ZeeMaps search feature that allows you to perform extensive searches for the pins on your map.

The detailed search dialog allows you to search for points based on field values. As well as, combine them with distance or geographical search. The geographical search includes searching within a highlighted region.

We’ve now added the ability to quickly do a look up search within a region by simply right-clicking on the region.

To start the search, just right-click on any highlighted region (boundary) and the display will limit itself to the points that fall within regions of the same color. To reset the search, right-click on the region again.

Once you have started the search, you can get a list of points that match your search by doing a Map -> Save as CSV.

For example, we are going to work with look up for a sales territory map that shows the sales territory zip code for three salespersons of a company. Each sales person covers roughly 2-3 zip codes. Each sales persons territory highlighted in different color.

As an example, we are going to work with a look up sales territory map that shows sales zip codes for three salespersons of a company. Each sales person covers roughly 2-3 zip codes.

We’ve added a list of potential customers to this map, with different customer types in different colors. For example, customers not contacted are red, while those contacted are green.

To get an overview of all the customers that have already signed-up, we can use the legend at the bottom of the map to view just the customers who are shown with green pins. But, how do we see all the green customers for Sales Person A?

Of course, that is where the new feature comes in handy. Now, simply right click on a colored territory area (zip code) for Sales Person A (pink). Notice the look up view of pins is limited to customers that fall within that sales person’s territory. Now, right click again to undo.

Simply right click on a colored zip code for Sales Person A and the look up view of pins is now limited to the customers that fall within that sales person's territory

Moreover, we can also get a list of the customers by exporting a CSV file from Map -> Save As CSV.

we can also get a list of the customers by exporting a CSV file from Map -> Save As CSV for zip code look up

If you have any issues opening the .csv in excel try following these steps.

Trip Planner in ZeeMaps for multi-point routes

Trip Planner Overview

ZeeMaps can used as a trip planner to plan your routes visiting multiple points marked on your map. Routes, an ordered list of destinations, can be created on any map, even maps not your own. Some highlights of trip planning with ZeeMaps routes are:

  • The ZeeMaps trip planner can optionally optimize your route for distance or time.
  • Routes are associated with your account for each map, so you’ll need to sign-in to create a route.
  • The trip planner allows you to print your routes, with a map showing the overall route and each stop labelled and listed on the print page.
  • Routes are available when you access the map from your Smart Phone, so you can easily get directions to your next stop from where you are.

Using the ZeeMaps Trip Planner

To create a new route on a map, use the menu item Additions -> Route. This will show you a dialog containing the route. For example, here’s a route on our Used Computer Stores map for a few stores in San Jose and Santa Clara, California.

Trip Planner: Create a route
Trip Planner: Create a route

Start clicking on pins on the map to add to the route. In the route dialog, you can select the route to be either automatically optimized for Distance or for Time. If not, you can leave it to be manual. If the route is in manual mode, you can drag-and-drop the destinations in the route to re-order your route. With a route dialog, you can do the following:

  • List All: list all the routes that you have defined on this map
  • Print: get a map print-out of your route, with each stop marked on the map and listed
  • Reset: clear your current changes
  • Show: show the routes between the destinations
  • Save As: Save your route for future reference
  • Close: dismiss the route dialog

Routes in Smart Phone

After you’ve defined your routes on a map, visit the map from your Smart Phone. To see the routes, click on the menu item and click on the Routes option. This will bring up a list of your routes, so you can click on the one that you want. Once you are on the route, you can click on any destination to get directions to that destination.

Trip Planner: Routes in Smart Phone
Trip Planner: Routes in Smart Phone

Heat Map Visualization in ZeeMaps

Heat Map Overview

Heat map, a density visualization technique, shows areas of high and low concentration on a map. While, a point map with 10’s of thousands of locations can become cluttered, a heat map visualization of the same data can quickly show areas of dense population.

For example, if you plot the locations of all your customers as a heat map, you can quickly see where there are pockets of low or high number of customers.

ZeeMaps provides support for a these maps in both the interactive and print versions. Currently, we look at the frequency, or number of points at a given location, to determine the density. Over time we’ll extend this to allow for the density value to be determined through one of the the marker fields.
Notice, you can combine this feature with the Zip Code Finder tool in ZeeMaps.  Finally, a great way to quickly determine the zip codes with high density of points on a map!

Example Map

Here’s an example of a heat map of locations of all the Used Computer Stores in the US. First, with the map, one can quickly see that Boston and New York are the most dense areas. While, other major cities like San Francisco, Dallas, etc., have some notable presence as well.

Now, click on the icon at the top right of the map. Notice, the display will change to the regular clustered point map.

We can also get an image of the same map, using the menu item Print or Share -> PDF/PNG Image. In the resulting dialog, we check the option for “Heat Map” for the pin style.

Heat Map of Used Computer Stores in the US
Map of Used Computer Stores in the US

Setting a Heat Map in ZeeMaps

Turn on the this feature for your map. First, go to the settings page (menu Map -> Settings). Second, click on the Map Display tab. Third, check the box for “Enable Heat Map” and hit Save Changes. Last, go back to the map to view the change. Finally, there will be a small icon at the top right of the map (see interactive map above). You can click on that icon to toggle the display from a heat to a regular point map.

Enable Heat Map in ZeeMaps
Enable in ZeeMaps

US Map – Get Map of USA Images using ZeeMaps

You can get US Map images from ZeeMaps in different sizes and resolutions, e.g., to print out a wall-poster, or include in a PowerPoint slide.
On your interactive map in ZeeMaps, click the Print or Share -> PDF/PNG Image menu item to get the dialog to customize your image.

Print Image menu item and customization dialog
Dialog to customize your print image in ZeeMaps

The first option in the customization dialog is for selecting the “Geography” of your image. This allows you to choose from one of the following options:

  • Cover Markers – include all the pins or markers that are placed on the map
  • Map View – use whatever is visible in the map window currently
  • Select Area – select an area of the map using a rectangle
  • US Map- make a US Map (possibly with Hawaii, Puerto Rico and Alaska included)
  • World Map – map of the world

US Map of Used Computer Stores

Select the option for “US Map” to get an image of a US Map with all your customizations overlay’d on it. For example, here’s an image generated from our Used Computer Stores map, with Alaska and Hawaii excluded:

US Map of Used Computer Stores
US Map of Used Computer Stores

We can also generate a US Map image with Alaska and Hawaii included:
US Map of Used Computer Stores with Alaska and Hawaii
US Map of Used Computer Stores with Alaska and Hawaii

The images are available as a political map with state boundaries, or as a satellite map:
US Map of Used Computer Stores, Satellite Version
US Map of Used Computer Stores, Satellite Version

Finally, if you want, you can highlight certain regions on the map, e.g., below we highlight California and Texas:
Map of USA with States Highlighted
Map of USA with States Highlighted

In this post, we’ve shown a limited set of samples of US Map generated with ZeeMaps. The image customization dialog contains a whole other set of customizations, e.g., selecting the type of markers on the map, the size and resolution of the image, whether you want the markers labeled or not. We invite you to experiment with the myriad of options and contact us in case you need some assistance.