I'm working on an evaluating spreadsheet where I give for several items a rate of 1 to 3 and at the end it calculates an average rate. The average rate would range from 1 to 3 as well and their meaning is:
1 - poor
2 - average
3 - good
How can I work on the formula so when the result is equal 1, 2 or 3 it automatically gives in the cell right beside the related legend?
In this file i did paste special values so the links are no longer there, but in my original file alot of the values you see in the cells are pulled from other worksheets ( thats just so you know to lookin values of the cells and not the formula ).
I basically need row 18 starting from Column O to self populate.
Its based off of which has 1's in the Column. I have the completed here manually just to show you what i am looking for . I usually have to go to row 3 and Enter the data manually depending on which 1's are filled in for the column.
If i could have a method of having row 18 starting with Column O to self populate based off those 1's
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
I created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
View 2 Replies View RelatedI know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
View 9 Replies View Relatedhow can I add an legend entry that has been removed by error?
View 3 Replies View RelatedI am creating a chart where the number of data series used on the chart will change every time the code runs depending on what information people are looking for. That part works fine. I need a legend that shows which data series were added to the chart. Is there a way to get a dynamic legend on the chart that matches the data series, without manually adding it?
View 6 Replies View RelatedI'm running Excel 2003 and I'm getting a #REF error in the legend of a chart! It displays the #REF and then the label. Anyone any idea what causes this - I can't find anything in Help.
View 2 Replies View RelatedFor Instance i might have a cell with the value 30KDPA
the one right below this needs to be 30KDPB
then 30KDPC
basically the last letter is going through the alphabetical order. Once it hits Z it should go to AA, AB, AC . Just like an excel file.
If i select the first cell and just drag it down it copies that exact same cell value.
I want to include a legend to a bubble chart in the folowing format: the smallest bubble in this legend should represent values between A and B, the second (somewhat bigger bubble) should represent values between B and C,and so on. Is it possible to produce a standard legend in a bubble chart?
View 2 Replies View RelatedI have a chart graphing different series, which includes a legend to label each series. The labels are linked to particular cells in the worksheet.
Most of the chart has Arial font formatting, but I need to change the formatting of a few characters in the legend:
I know that i can change the formatting of the whole legend, and even different entries in the legend. Is it possible to change the font of just a couple of characters in one legend entry?
Elements I need to change are:
Font type (eg from arial to symbol)
Size (varying from 8 to 14)
Bolding etc.
So i deleted a chart legend - is there a way to get this back or do you have to start all over again?
View 4 Replies View RelatedHow do i change a title in legend on a chart. i.e. I want the label to be MGRS 2%
Job Category
Category %
EE Total
Mgrs
2%
15
Supvr
6%
51
[code]....
I have made charts in VBA charts that needs to be placed on specific position and have specific sizes on a chartsheet. I use plotarea (top, left, width, height) for that. Unfortunately when I set these values they keep chaning, there is some scaling going on in Excel and they won't get the specified size. I have been looking on the internet and finally I found a piece of code which works, but still not good enough, because the legend is also not good positioned
De data for resizing the new charts (ChtNew), the legends and the charttitle I get from other charts (ChtOrig). The legend.legendposition of ChtOrig does not have a value but it still have a top,left,height en width which I use.
I show you my code, the part with the for-next is taken from the internet.
I am trying to find a solution now for 3 days and now I only dream of huge charts (which is not good). So please can someone help me with this? This is the last forum, that I can try, I didn't get reactions from others.
with ChtNew.legend
.Top = ChtOrig.Legend.Top
.Height = ChtOrig.Legend.Height
.Left = ChtOrig.Legend.Left
.Width = ChtOrig.Legend.Width '* 1.1
.Top = ChtOrig.Legend.Top + ChtOrig.ChartTitle.Top
end with
With ChtNew.Plotarea
.Top = ChtOrig.PlotArea.Top
.Height = ChtOrig.PlotArea.Height
.Width = ChtOrig.PlotArea.Width
.Left = ChtOrig.PlotArea.Left
For i = 1 To ChtOrig.PlotArea.Top........
I have a 2003 vs. 2007 problem that I've spent a week or so trying to resolve, to no avail. I have created a fairly complex "dictator application" in Excel 2003. The application performs a bunch of calculations, and stores this inforamtion in a template workbook. The template itself contains one worksheet (to hold the data), and twelve empty "chart-sheets" which I later use to create plots. Of course, everything works fine and life is good in Excel 2003.
Flash forward two years, and half of my company is running 2007. When a 2007 user fires the plot creation routine, he is greated with a lovely error message (# 2147467259): "Method 'Position' of object 'Legend' failed".
Here's the full procedure I'm running to create the plots and position the legends:
Function bChartSumFileData(ByVal lTotalPlots As Long)
Const sSOURCE As String = "bChartSumFileData" 'Procedure Name (error-handling)
Dim bReturn As Boolean
Dim i As Long, j As Long, lNumSeries As Long
Dim sTemp As String, sPlotSetting As String
Dim chtTemp As Chart
Dim wksData As Worksheet, wksInfo As Worksheet
Dim serTemp As Series
Dim uSettings As CHART_SETTINGS
Dim vaPlotInfo As Variant
Obviously, lots of custom functions here; I'm also using a custom "type" to store chart settings (which I read from a *.txt file in a separate function). However, as far as the Legend goes, it shouldn't make a difference with the above code. When I stop execution after the error and look at the template workbook, the chart is created just fine, and the legend is visible, so I'm not sure why the error is occuring. Even better, the number of charts created prior to erroring-out is not fixed: sometimes the error occurs on the first plot, sometimes on the third, etc.
I need a basic code that accomplishes the following:
1) The user can define a legend for which he can format the cells in that legend however he wishes
2) Cells elsewhere in the sheet that have the same value as that in the legend will format exactly how they are in the legend
3) Default if not in the legend
I found this code that is close...but apparently it relies on the user selecting the cells he wants to format according to the legend FIRST...my code needs an Intersect command or something like that.
Also im not sure if this code actually takes into consideration font+border+shading formats...my code would need to take those into consideration.
example of the desired output is attached! ....
I have a s/s which is built on IF functions and references other sheets and I have used the s/s in the past without issue. However now I want to ammend the formula a little and excel will only display the formula itself, not the result of the formula. I have tried CTRL ~ to turn on/off the show formula function but this makes no difference.
My reason for changeing the current formula is that I need to turn a 2 (numeric) to 02 (which can be either numeric or text). This is the new formula.
=IF($A4="","",IF(VLOOKUP($A4,HCGTH1_DATA,22)=2,"02",VLOOKUP($a4,HCGTH1_DATA,22)))
Is there an issue with the formula or some setting in excel?
I have 10 graphs with more than 20 legend entries. However, each graph only needs 3-4 elements out of the 20 legend entries in the graph. Is there any way to force Excel to only show those legend entries that have a value? (without deleting them manually)
View 12 Replies View RelatedI am using bar chart to display top 5 units sold each year to my Excel file. In the data table, I have listed all the units and its number (quantity) sold. The problem is, the legend of the chart displays all the units instead of just displaying units that has values (top 5 units each year) which makes the legend hard to read (see attachment) How do I get Excel to select legends only if there is a value for it?
View 2 Replies View RelatedI have a chart with 20 series labels that are series 1, series 2.... etc. I want to label them with 20 corresponding cells but can't find how to do this in Excel 2010 (I seem to remember this being easy to do in older versions). At the moment I am having to click on source data and edit each label individually... Is there a quicker way?
View 2 Replies View RelatedI have a standard bar graph with 2 sets of data: 1 dataset on the primary axis and 1 on the secondary axis. I have defined the legend entries fairly specifically so the descriptions are long. I have also inlcluded a 'rank' value into the graph which doesn't appear on the graph as the values on either axis aren't at a range that allows it to display. However, I want to display the rank value in a table below the graph. However, when I add the table, the legend entries are so long, the graph gets 'scrunched' and looks terrible.
Is there a way to either:
1.) Only show the rank data in the data table below the graph?
or
2.) Show only the data in the data table below the graph and not the legend entries?
or
3.) Another option to display the rank data on the graph?
is there a way to change the legend keys (aesthetically) in 2010?
I created a pie chart using a template but I don't want to keep the defualt legend keys. I tried changing the theme but it appears that the legend keys that come up are the same no matter what theme I change it to.
The Chart is a 3d Pie chart, I think that matters because the regular square legend keys show when the chart isn't 3D.
I need a way to make a customized legend for a scatter plot, is there any way to do that through VBA? I have a scatter plot with some points highlighted green, and some points grey depending on the user selection and need to be able to create a legend based on what the user selects (e.g. Green - Category B, Grey - Category A, etc.).
For some reason I can't get Excel to rename points on the scatter, so I feel like there is only one option: create custom images and align them correctly with labels on the plot.
I have charts that needs to be placed on specific position and have specific sizes on a chartsheet. I use chartobjects(i).plotarea (top, left, width, height) for that.
Unfortunately when I set these values they still change and get antoher value, there is some scaling going on in Excel and I do not know where that comes from. I have been looking on the internet and finally I found a piece of code which works, but still not good enough, because the legend is also not good positioned.
De data for resizing the charts (ChtNew), the legends and the charttitle I get from other charts (ChtOrig). The legend.legendposition of ChtOrig does not have a value but it still have a top,left,height en width which I use.
I show you my code, the part with the for-next is taken from the internet.
With ChtNew.legend
.Top = ChtOrig.Legend.Top
.Height = ChtOrig.Legend.Height
.Left = ChtOrig.Legend.Left
.Width = ChtOrig.Legend.Width '* 1.1
.Top = ChtOrig.Legend.Top + ChtOrig.ChartTitle.Top
End With
I think looking at the attachment might make more sense, but here is a brief description of what I want to do. I am trying to change the colour of a cell based on looking up the value contained in that cell in a predefined list (which in my example I have called a legend). I need to do this in VBa but my VBA is not at all good.
View 4 Replies View RelatedI am applying for a job as an executive assistant and before the interview I have to complete a "test." I am having trouble with one direction. Here is the question:
"10.Apply series name and legend key in data labels to the Star data series. "
I have included what I think is the correct answer, but I am not sure. It is under the column chart sheet.
see this example of a chart: see Image1.gif (the right thumbnail) as you can see 2 different types of graphs are combined: "lines with markers" and "clustered column".
in the legend one can see that the order inside the legend is clustered by the two types. within a type i can change the order easily (right click on a line or column - format data series - series order - up or down).
question: i would like to change the order freely. how to do this? for the order inside the legend i actually do not care at all, whether a representation of the data is a line or a column. i only want to make it as readable as possible. example what i'd like to get (made by handmodifing the picture with a drawing program): see Image3.gif (the left thumbnail)
I have a workbook consisting of 4 worksheets. Cell B5 in each of three of these sheets has the exact same formula (copy/pasted) referring to data in the other worksheet. In one of the three similar sheets cell B5 shows up as a blank. In the other two, this cell shows up as a zero.
Since the formula in each of these cells is identical, I figured the difference in the way the result showed up was a matter of cell formatting.
The cell format for each of the three is indicated as ‘general’. However, with one of these, the ‘sample’ shown when you view the cell format shows as a blank while the other two show the ‘sample’ as a zero (0). How can I go about making all three cells appear the same, namely a blank?
I have already an existing formula to get only the amount from the previous cell. From a6 to a30 it contains the word "11/05/09 press release $100", so in b6-b30 this is the formula. =IF(ISNUMBER(SEARCH("press",A6)),RIGHT(A6,LEN(A6)-FIND("$",A6)),"") until b30
the problem is in b6:b30 the 100's were not a number you have to copy and paste to other cells using paste special then values then click the smart tag and convert as a number to get all the total from b6 until b30.
Is it possible to use the IF formula and if the result is true, to run another formula and if the result is false put a "0" in the cell???
Here is an example...
In cell A1, I have an amount of money.
In cell B1, I have a quantity.
In cell C1, I want to use the following IF formula:
=IF(B1>0;((A1)*(B1)*0.15);0)
At the present time, when I do this, when the result is true, I see ((A1)*(B1)*0.15) in the cell instead of what that formula should give.