I have a report that is 216 lines long. I have one column that uses 3 colors to sort the information quickly. I would like to make a pie chart that shows, how much of the column is yellow, how much is green, and how much is blue. It sounds simple, but I seem to be having difficulty with making the chart. I hope someone can make this simple.
I have been able to do a custom sort of the colum and segregate the information by color, but now I want to put all that into one pie chart.
Last edited by strikinglyfamiliar; 06-22-2012 at 05:01 PM.
Wouldn't it make more sense to create the pie chart (or even sort) by the criteria you used to determine which cell has which color? You wouldn't need to use custom anything.
The custom sort was used just to put the colored cells together in groupings. I have (since my post) manually calculated how many yellow, how many green, and how many blue cells there are. I then put those numbers into three cells at the bottom of the sheet, and used those three cells to make the chart. I just think there would be an easier way... but then again this whole project has been a nightmare, but is getting close to the end
I would recommend adding a column to your data that calculates a value (e.g. 1, 2 or 3) based on the criteria you used to color your cells. You could then use that to group the cells as well. The column could be hidden so it's not in view afterward. (Hopefully you're using Conditional Formatting to color your cells, too.)
okay, you got me.. I did not use conditional formatting to color the cell, as I was having problems on getting it to work. (if column e is less than column f and column g then color cell d4 yellow) I have never done if/then statements. But I did sort the columns from largest to smallest, and colored the cells accordingly.
Using conditional formatting you could select the cells in column D (e.g. D1:D20) and click Conditional Formatting > New Rule... > Use formula to determine... and set the formula to:
=AND(E4<F4,E4<G4)
Set the fill color to yellow.
To create a formula in a cell you could use a similar approach. For example, say you wanted:
"If E4 is less than F4 and also less than G4, return 1. If F4 is less than E4 and also less than G4, return 2. If G4 is less than E4 and also less than F4, return 3. Otherwise return 4."
You could use:
=IF(AND(E4<F4,E4<G4),1,IF(AND(F4<E4,F4<G4),2,IF(AND(G4<E4,G4<F4),3,4)))
It would be simple enough from there to count the 1's, 2's, 3's and 4's (using COUNTIF, perhaps) and create your pie chart.
Can I use that formula to also return a value when there is a #N/A?
The whole project is an attempt to see what products I can purchase at the best price from any of three vendors, and also what products I have to get from an original equipment manufacturer. So, I have pricing from all three sources, but there are many of the items I have bought in the last 6 months, that I can only get from the OEM.
What scenario would you have where an N/A either exists or would be the result? I'm sure a formula can account for it, but a bit more information is needed here.
When the OEM has parts that are not in the other two databases, the VLOOKUP returns a "#N/A" value. The goal at this point is to count, and see what percentage of the parts are only available from the OEM
I am now up to three reports where the #N/A result is very common. This is returned when the aftermarket doesn't have the original vendor's part number in their database. I need to find a formula that will account for that in the conditional formating, so that I can include those lines in the total. That way when I end up with a pie chart it will not only compare which of two vendors is less expensive, but also what I have to get from an OEM. So what is the formula that brings a return in conditional formatting when the VLOOKUP gives #N/A
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks