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

A radius map showing UK office locations and their reach

Radius map: Upload circles on a map from spreadsheets

We thought it was time to add the function to upload a radius map. ZeeMaps allows you to upload spreadsheets with pins, or with highlighted geographical areas like US zip codes or counties. Now you can highlight circles on the map with different radii, using spreadsheet uploads.

To upload a spreadsheet of data as circles, add a column called ‘Radius’ to your spreadsheet. Specify the radius in miles or kilometers for each of the rows. On rows where you do not have a radius specified, you can define a default radius. Therefore, if you want all your circles to be of the same radius, you do not need to create a radius column – just specify a default radius.

How to upload a spreadsheet to create a radius map

To get started uploading a spreadsheet with circle markers, you can do one of two things:

  1. Select the ‘Additions’ > ‘Add (Upload) Multiple Markers’ menu item on your map, or
  2. Click the ‘Map My Spreadsheet’ button.

You will be presented with a dialog to pick a file to upload. Along with the file, there is an option to specify what kind of markers you would like to add. Make sure you select the option that says ‘Circles’, as below.

This image shows the upload data page and how to upload as circles.
Select the option to upload circles on a map

After you have selected a file and hit submit, the next dialog will give you the option to select your radius column, choose the default radius, and select whether the radius values are in miles or kilometers. Even if you are not using the default radius, and have specified radius values for each of your spreadsheet rows, make sure you check the appropriate box for miles or kilometers, depending on how your radius values are specified.

This image shows the default radius option and also the circle radius column.
Circle radius column and default radius

Just like any other spreadsheet upload, the circle markers can have different colors and additional information in them that will be visible once you click on the circle.

The benefits of circle markers

Circle markers can be helpful if you have multiple service persons and jobs spread throughout a geographical area. They can help you to quickly understand which service person to assign to which job, depending on distances.

US state map using extended colors

We encourage you to read this blog post fully, as there is a link to a free US State Map at the end!
ZeeMaps has more than doubled the colors available to users with an Enterprise Plan. It means you can group markers into 75 different colors instead of just 32. This comes in handy when you are coloring a US state map, or other maps with multiple regions. The six palettes contain shades from teal to princess perfume, sunglow to cyber grape.
You can view the different color palettes in the tabs at the top of this page. All ZeeMaps users have access to the Classic palette. Only users who have signed up for the Enterprise plan have access to the next five palettes (Red; Yellow-Orange; Green; Blue; and Violet).

Color your US state map with extended color palettes
Color your US state map with extended color palettes

Use the extended palette on your US state map

To use the extended palette, you must set it up for each map. How? Go to Map > Settings and select the ‘Markers Display’ tab. Set the option for ‘Use Extended 75 Colors’ and hit ‘Save Changes.’ This will enable you to see the extended colors on that particular map.
For additions, go to Additions > Add Marker (Simple) or Add Marker (Detailed). Whether you select Simple or Detailed, both will show the color options based on the map settings. With extended colors, you’ll be able to chose from among 75 colors.

Upload a spreadsheet with multiple colors

To upload a spreadsheet with multiple colors, set a column for ‘Color’ or ‘Category’ on your spreadsheet. If you use ‘Color’, choose the color names listed on this page. If you set a column for ‘Category’, you can specify different values in the rows and ZeeMaps will assign a different color to each different value –  starting with the shade that you select for ‘Default Color.’ Once you have finished your spreadsheet, go to Additions > Add (Upload) Multiple Markers.
As an illustration, we will use the following spreadsheet to create a multi-colored US state map:

Spreadsheet for US State Map
Spreadsheet for US state map

US State Map with 50 Colors

Based on a well known mathematical four color theorem, you need only four different colors for coloring a map, in order that no two adjacent regions have the same color. Although it is technically feasible to limit ourselves to four colors, that is not necessarily appealing  – especially if different shades are used to indicate variations in some indicator, e.g., population density. Here’s an interactive map that we created with 50 colors for US states.

You can use the ZeeMaps Print or Share capability to get an image of your map. In the Print or Share menu, select the option for PDF/PNG Image. In the resulting dialog, use the setting ‘US Map’ for Geography and make sure it is a ‘Simple’ US map and not a ‘Colored’ one.

US State Map
US state map with extended colors

We are giving away a high-resolution poster-sized image of this US state map, so if you like what you see you can download it here for free. This image was created with ZeeMaps, by setting the size of the image to poster and resolution to 300 DPI.
We hope you enjoy using your more colorful ZeeMaps!
 
 

Counties: Map with Name or FIPS code

ZeeMaps provides easy mechanisms to highlight various geographies (e.g., countries, states, cities, zip codes) and geometries (polygons, circles) on a map. These highlighted areas or “regions” are helpful in marking out territories or service areas. One of the areas that is specially useful to highlight is US Counties, a first level administrative sub-division in the United States after the state. County boundaries can be useful to analyze customer or sales data.
ZeeMaps allows you to highlight counties in two ways:

  1. You can highlight a county one at a time, using the Additions -> Highlight Regions -> Counties on your map, and
  2. Upload a list of counties contained in an Excel spreadsheet or CSV file, using the Additions -> Add (Upload) Multiple markers menu item on your map.

The second option is helpful if you have hundreds or thousands of counties to highlight. This blog post reports on a new development for the second option of highlighting counties.

Improving the user experience

We are always looking for ways to improve the user experience – whether we are implementing changes we think would be useful or responding to feedback from our users. We are constantly working to improve existing user functions and to add new ones. That’s why we’ve been busy making US counties easier to work with.
One of the improvements has made highlighting US counties through spreadsheet upload far easier. Users can use a county name or FIPS code instead of specifying an address.

Highlight the top 10 and lowest populated Counties in the US

Previously, users had to specify an address or location to highlight a county. Let’s use our office in Cupertino, CA, Santa Clara, as an example. Earlier, we’d need a column for ‘City’ and ‘State’, or Zip Code in our spreadsheet, to identify a location within the county. So, even if you knew the county name, in this case Santa Clara, it was not usable. You had to figure out a location within the county and use that as input to identify the Santa Clara county, instead of giving its name. Now it’s possible to:

  • have a column called ‘County’ and specify ‘Santa Clara’, or
  • use ‘06085’ – the FIPS code for Santa Clara county

In the rest of this post, we’ll use the following map as an example. The purple colored counties are the ten most populated counties in the US, and the orange ones are the least. You can click on a highlighted county to get the population for that county and its name.

Formatting your spreadsheet

The following table shows the spreadsheet that we used to create this map. It contains five columns: County, State, Color, Population, and County Name. Here’s a bit more about them:

  1. County: we use this column to identify the county to be highlighted.
  2. State: we use this column to disambiguate counties with the same name. If you are using just FIPS codes, or if your county names are unique, then this may not be necessary.
  3. Color: we use this column for the color of the highlighted area. ZeeMaps supports up to 32 different colors for most users, and up to 75 colors for our Enterprise users. A complete list of supported colors can be found on our Colors page.
  4. Population: The last two columns will be used as information to be shown when a user clicks on one of the highlighted county regions. Since this spreadsheet is for the ten most and ten least populated counties in the US, we show the population count from a 2017 estimate.
  5. County Name: we use this column so that some of the counties that have the FIPS code can be easily identified by a regular user who may or may not be aware of FIPS codes.

Counties spreadsheet
Counties spreadsheet

You’ll see that the ‘County’ column (A) mixes county names and FIPS codes. To show the county name in the information bubble, there is a ‘County Name’ column (E) as well.

Uploading your spreadsheet

To upload your spreadsheet, open your map and select Additions > Add (Upload) Multiple Markers. In the dialog box that follows, select the ‘US Counties’ option for Upload As.

Counties spreadsheet upload - step 1
Counties spreadsheet upload – step 1

Then, submit your spreadsheet. ZeeMaps will read your spreadsheet and make an initial pass at understanding which column to use for what information, based on the column names that you specified in the spreadsheet. Double-check the column assignments in the confirmation step and make any changes as necessary. In the following image, we have highlighted some of the assignments that needed adjustments for this spreadsheet:
Counties spreadsheet upload - step 2
Counties spreadsheet upload – step 2

If you have any suggestions for improvements to ZeeMaps, please get in touch.

Spatial Data Aggregation

Use highlighted regions and data uploads in ZeeMaps to conduct quick spatial data aggregation. For example, you can analyze data for the different territories you have marked on your map.

Spatial Regions Highlighting

You can highlight areas on your map by zip codes, counties, cities, states or geometrical areas. Such as, circles and rectangles. An area of interest will often comprise of several smaller areas. For example, a sales territory map might divide the country into several regions, each containing multiple states.
The fastest way to highlight large number of US zip code areas or counties on your map is by uploading a spreadsheet with address and color information.

This is far quicker than highlighting regions on your map one at a time. This blog post explains how to do that. When you upload a spreadsheet to highlight zip codes or counties, your spreadsheet can contain additional columns of spatial data that will be taken as “fields” for that zip code or county.
For other areas like US States, or countries, you’ll need to highlight the areas using the Additions -> Highlight Regions menu on your map, and then upload your data using the Additions -> Add (Upload) Multiple markers, separately.

Spatial Data Analysis

Spatial Data Aggregate Map Menu

Now that you have different territories marked on your map, you can access the spatial aggregate data feature.
As an example, we are using the map of Las Vegas zip codes divided into five colored regions. Each highlighted zip code has population fields that describe populations in 2011 and 2012, and the median income. The map is shown below. Click on any of the highlighted zip code to see the details for that zip code.

When visiting the map on the ZeeMaps site, we select ‘View’ and ‘Aggregate data by region’ from your map menu.

Spatial Data Aggregate Map Menu

In the resulting dialog, choose your field values and whether you’d like to see results presented as sum, average or count.

Spatial Aggregate of Marker Field Data
Edit Marker Field Data
Spatial Aggregation Data Results


Hit ‘Submit’ to see your aggregate data results that will give you the sum of populations for 2011 and 2012, and the average of the median income in each of the five colored regions that we have defined on the map.

Aggregated spatial data results

This example shows the 2011 and 2012 population and median household incomes for zip codes in Las Vegas, NV. ZeeMaps allows you to define your own marker fields, so you can perform this spatial analysis on any of your fields.
At present, you can only spatially aggregate marker data by highlighted colours. In the future, we will be extending this so you can also aggregate data by geographical areas, e.g., get spatial analysis by countries, states, and so forth.
If you’d like to try out the aggregate spatial data by regions feature, you can use this sample map – the zip codes in Las Vegas, NV that we’ve been using as an example in this post. Let us know what you think.

To learn more about aggregating data click here