Conditional formula for "pie wedge" icon set

=IF(ISTEXT('Sheet1'!A1), "*", IF(ISBLANK('Sheet1'!A1),'Sheet1'!A1, IF('Sheet1'!A1 >= 10, IMAGE("https://docs.google.com/drawings/d/1zvwx-srTZlBYQgO1KNdx9-aB1Bw-GRXCfByBPmW0X_M/pub?w=120&h=90"), IF(AND('Sheet1'!A1 < 10,'Sheet1'!A1 >= 7.5), IMAGE("https://docs.google.com/drawings/d/1YlsedUl2v81KzJjGrmDu90f7Kvc-C4sMQ_EnM40Eei4/pub?w=120&h=90"), IF(AND('Sheet1'!A1 < 7.5,'Sheet1'!A1 >= 5), IMAGE("https://docs.google.com/drawings/d/1IFZiKmFa8zv_7XYxBkIrbJOb_QthS5aNP_VPs3sPbx4/pub?w=120&h=90"), IF(AND('Sheet1'!A1 < 5, 'Sheet1'!A1 >= 2.5),IMAGE("https://docs.google.com/drawings/d/1P0ZZ6bbx1xnhaIDKU34XY_rGzfZIcDbN-dycneMlZ1k/pub?w=120&h=90"),IF('Sheet1'!A1 < 2.5,IMAGE("https://docs.google.com/drawings/d/1gTEKIQuY29SB1HCrcDiT31D0ZsaWaUVMBW_ZW5sjGow/pub?w=120&h=90"),'Sheet1'!A1)))))))

NOTE: "Sheet1" should be replaced with whatever the target sheet name actually is in your Google Sheets document

Steps for adding conditional pie "icon set" to Google Sheets

BACKGROUND: Google Sheets is a very capable replacement for Excel, especially for ehanced collaboration around spreadsheet data. One feature Google Sheets lacks, though, is support for conditional "icon sets" (pie wedges, stop lights, etc). Fortunately, with a little extra work, you can achieve this effect in Google Sheets using images and some basic IF logic.

STEP 1:

Create the images you'll use for your icon set. In the case of "pie" wedges, you need 5 images: Full Pie, 3/4 Pie, 1/2 Pie, 1/4 Pie and Empty Pie. For simple icons, you can create and publish these images directly from Google Drawings. Here are images I created in Google Drawings for pie wedges:

STEP 2:

In Google Sheets, you'll need at least two sheets: one for your actual data, the other to display icon set visualization. The basic idea is that one sheet will serve as the underlying data that powers the more "visual", read-only icon set sheet. This is required since Google Sheets does not yet support showing an icon in the same cell that holds a value (like Excel). Alternatively, instead of using two sheets, the use of hidden columns to hold values to power the icon cells can be employed. The primary drawback to this approach is that it is harder to update/change the values in the hidden columns.

You should have:

Sheet 1: Your data
Sheet 2: Nothing yet...but it will soon display icons based on data in Sheet 1

STEP 3:

Create the forumla that will determine when to display the proper icon. There are many ways to customize this formula, but the concept is to use IF statements to display the desired icon using Google Sheets IMAGE function. When a value from Sheet 1 satisfies the IF conditions, the corresponding icon will be displayed in Sheet 2.

Example for Pie Wedge icons:

=IF(ISTEXT('Sheet1'!A1), 'Sheet1'!A1, 
IF(ISBLANK('Sheet1'!A1), "", 
IF('Sheet1'!A1 >= 10, IMAGE(ImageURL1), 
IF(AND('Sheet1'!A1 < 10,'Sheet1'!A1 >= 7.5), IMAGE(ImageURL2), 
IF(AND('Sheet1'!A1 < 7.5,'Sheet1'!A1 >= 5), IMAGE(ImageURL3), 
IF(AND('Sheet1'!A1 < 5, 'Sheet1'!A1 >= 2.5),IMAGE(ImageURL4),
IF('Sheet1'!A1 < 2.5,IMAGE(ImageURL5),'Sheet1'!A1)))))))

In this example, if data in 'Sheet1' cell A1 is...

Text...the raw text is displayed in Sheet2, A1
Blank...the cell is left blank in Sheet2, A1
= 10...the "full pie" wedge is displayed in Sheet2, A1
< 10 and >= 7.5...the "3/4 pie" wedge is displayed in Sheet2, A1
< 7.5 and >= 5...the "1/2 pie" wedge is displayed in Sheet2, A1
And so on...

This formula can be altered to work with different ranges, to trigger on specific values or any number of variations to match the conditional rules needed to support your icon set. In each case, the IMAGE function is used to load and display the desired image in a cell when the condition is met.

STEP 4:

In Sheet 2, drag and apply the conditional formula to all cells where you have underlying data in Sheet 1. The formula should automatically update to refer to the correct cell in Sheet 1 (as formulas do in Excel, too).

If you've done everything correctly, Sheet 2 should now display a conditional icon in each cell based on data in Sheet 1.

STEP 5:

To reduce the liklihood of mistakingly editing the cells in Sheet 2, it is a good idea to "protect" Sheet 2 so all edits are made to the source data in Sheet 1. In Google Sheets, use the menu to go to "Tools > Protect Sheet..." In the dialog that opens, give the permissions a name, and then click "Set Permissions." This will help ensure the formulas in Sheet 2 are not broken, and that Sheet 1 remains the source for all underlying data.

Enjoy your conditional icon set in Google Sheets!

(Maybe one day Google Sheets will make this a built-in feature so we don't have to jump through these hoops to replicate Excel.)

toddanglin/Conditional Formula.md