Pie Chart Style Status Icons

Today a visitor stopped by the blog and sent me this message via my MeeboMe chat window:

[14:40] meeboguest######: I'm trying to insert a 4 section circle icon that is shaded in different ways. I'm using it to track progress on different projects. So I need a total of five different icons. completely unshaded, 1/4 shaded, 1/2 shaded, 3/4 shaded, and 4/4 shaded. where do i find these icons?

Sadly, I was away from my computer at the time. Here's my suggestion anyway:

I don't know of a good set, but these are nice to have for your dashboards and status reports. I say just create your own. Here's a simple set I created in 5 minutes using Microsoft Paint. I'm sure others among you are far more creative, so feel free to comment and share your icon collections.

Combine these images with my post on Pulling Images in Excel, and you could have a pretty slick status report. Be sure to remember the work-around when using Excel 2007.

Enjoy...

Excel_Geek

Pull numeric value from string

The other day I had an interesting request for help via my little MeeboMe chat window. James wanted a formula to pull a numeric value from a string contained in another cell. The string was something like this:

"Nylon rope 300Ft"

He had a whole column of this type of data and wanted to pull out the number of feet of each item. The "Ft" would always be immediately after the numeric value desired, and a space would always immediately precede it, but here's the kicker: there could be any number of other spaces before the numeric value and there could also be other number before the numeric value desired.

It took me a bit to get where I needed to be, but here's the logic built into a pretty long, nasty, nested formula:

1. First get the string that contains everything up to and including the "Ft" using LEFT and FIND.


2. Count the number of spaces in the string identified in #1 by comparing the length of that string versus that string if all the spaces are replaced with nothing (""), using LEN and SUBSTITUTE.


3. Replace the last space found in the string identified in #1 with an odd character not likely to be in any of the data (I used a tilde ~) using SUBSTITUTE.


4. Pull out the numeric value, which is the remainder of the string after the tilde using MID, FIND, and LEN.

I put together a sample spreadsheet of this process both broken out into parts and also all nested together in a single formula.

Excel_Geek Insiders, enjoy!

Later,

Excel_Geek

Chat Support RE: Custom Number Formats

Here's a short one from today:

[15:39] meeboguest######: hi, Im trying to import 11 digit UPC numbers into my software, but can't find the correct format to keep the digits at 11. All my UPC numbers that start with a 0 are cut off and made 10 digits every time I try to import
[15:39] meeboguest######: I tryed formatting the columns into text and it still shows 10 digits if it start with 0.
[16:15] excel_geek: go to Format --> Cells...
[16:15] excel_geek: from the Number tab select Custom
[16:16] excel_geek: then replace where it says "General" with 11 zeros.
[16:16] excel_geek: tada!



Gotta love customer number formats.

Chat Support RE: Counting in Pivot Tables

Here's another one from yesterday:

[15:40] meeboguest######: hi excel_geek - looking for a cont function in a pivot table... can you help?
[15:40] excel_geek: cont?
[15:40] excel_geek: count?
[15:40] meeboguest######: oups.. "count"
[15:42] meeboguest######: have multiple entries by various "agents" and wnat to find how many agents are ther in total... regardless of the entries
[15:42] excel_geek: sure
[15:42] excel_geek: right click in the table detail part
[15:42] excel_geek: and pick field options
[15:42] excel_geek: then select "count"
[15:44] excel_geek: follow?
[15:44] meeboguest######: ok
[15:46] meeboguest######: it gives me the total count of entries, not count of unique individuals... (1 individual = multiple entries)...
[15:47] excel_geek: hmmm
[15:47] excel_geek: maybe i should back up
[15:48] excel_geek: so drag the agents field into the left part of the pivot table
[15:48] excel_geek: then drag any other column into the right (detail) part
[15:48] excel_geek: the right click the field you put in the right part and field settings to "count"
[15:50] meeboguest######: ok... i think i figured it out!
[15:50] meeboguest######: thx a bunch :)
[15:50] excel_geek: np
[15:50] excel_geek: thanks for stopping by
[15:50] meeboguest######: :D

10 Minutes to understanding. Not too bad. Some of that pivot table stuff is unintuitive.

Chat Support RE: =INDIRECT() Function

More and more I'm helping folks via the MeeboMe chat window I've embedded in the blog. I thought it'd be interesting for you all to see the sorts of conversations I'm having this way:

[14:36] meeboguest######: eric!
[14:36] meeboguest######: do you have a sec?
[14:36] XLgeke: for u?
[14:36] XLgeke: always
[14:36] XLgeke: and now it's over... ;)
[14:36] XLgeke: what's up
[14:36] meeboguest######: excel question
[14:36] meeboguest######: umm
[14:36] meeboguest######: trying to think how to explain
[14:37] XLgeke: reboot
[14:37] XLgeke: that should do it
[14:37] meeboguest######:
[14:37] meeboguest######: ok, I'm wondering if there's a way to dynamically reference a cell in a formula
[14:37] XLgeke: yes
[14:38] meeboguest######: so that if I was to reference cell B
[14:38] XLgeke: right
[14:38] XLgeke: use =INDIRECT()
[14:38] meeboguest######: and that number is a value generated in another cell
[14:38] XLgeke: so like
[14:38] meeboguest######: oooh
[14:38] XLgeke: =INDIRECT("B"&B1")
[14:38] meeboguest######: woah
[14:39] meeboguest######: nice
[14:39] XLgeke: in B1 you'd change the number for BX
[14:39] meeboguest######: right
[14:39] meeboguest######: I'll give that a shot
[14:39] meeboguest######: thanks!
[14:39] XLgeke: np

And that was it...less than 3 minutes and issue solved. This happens scores of times each month.