Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Xy Scatter Graph Data Labelling

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I've down loaded XY data labelling tools that work a treat but I'm still having one glitch.

When I filter the data in my table - I'm auto-popualting a risk matrix with risks that are still "Open" - the data labels do not correspond to the XY point that is plotted they just follow their own logical order.

******** ******************** ************************************************************************> Microsoft Excel - Testing John's Risk template.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout E13 F13 E14 F14 E15 F15 E16 F16 E17 F17 E18 F18 E19 F19 E20 F20 =
B C D E F G 11 Ref. Probability*(P) Impact*(I) * Risk*Rating * 12 * Status 13 1 1 2 L Low Closed 14 2 4 4 VH Very*High Closed 15 3 3 3 H High Closed 16 4 2 5 H High Open 17 5 1 4 H High Closed 18 6 2 2 L Low Open 19 7 2 3 M Medium Open 20 8 3 1 L Low Open Risk Log (2) *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

From this table the points on the sctatter graph will be plotted in the correct place for Refs. 4, 6, 7 & 8 but they will be labelled as 1, 2, 3 & 4. I need them to maintain their corrrect reference numbers on the chart once filtered.

Can anyone help? I'm so near, yet so far with this, I was actually thinking about spreadsheets in bed last night... that can't be good!

View Answers     

Similar Excel Tutorials

How to Create and Manage a Chart in Excel
In this tutorial I am going to introduce you to creating and managing charts in Excel. Before you create a chart yo ...
Create a Column Chart with a Macro in Excel
This macro adds a column chart to Excel. This is an easy to use macro that allows you to quickly chart and graph a ...
Format Any Element of a Chart in Excel
In this tutorial I am going to go through the Format tab in more detail and show you how to Format every element of ...
Sort Data that Doesn't Have Headers in Ascending Order in Excel
Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that ...

Helpful Excel Macros

Open a Word Document from Excel
- This Microsoft Excel macro will open a Microsoft Word document from excel. With this macro, you can open any word docume
Open a PowerPoint Presentation from Excel
- This free macro for Microsoft Excel allows you to open any PowerPoint presentation from excel. You can change the file
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
Open Microsoft Word from Excel
- This free macro will open the Microsoft Word program on your computer. You do need to have this program first. This wi
Open Microsoft PowerPoint from Excel
- This free macro will open the Microsoft PowerPoint program on your computer. You do need to have this program first. T

Similar Topics







I've got a risk matrix / log that rates risks by impact against probability: it then plots them on a 5 x 5 grid. Each risk has its own unique reference, to get Excel to plot the unique refence number against the point on the chart I've loaded in J-Walk's chart tools which works fine.

Problem is that when presenting to a Project Board it is usually only necessary to show risks that are open. When I filter the my list to show "Open" risks the problem occurs.

The risks remain plotted in the correct place in the Risk Map but the unique reference numbers get a bit screwed up.

******** ******************** ************************************************************************> Microsoft Excel - Testing John's Risk template.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout E13 F13 E14 F14 E15 F15 E16 F16 E17 F17 E18 F18 E19 F19 E20 F20 E21 F21 E22 F22 E23 F23 E24 F24 E25 F25 E26 F26 E27 F27 E28 F28 E29 F29 E30 F30 E31 F31 =
B C D E F G 11 Ref. Probability*(P) Impact*(I) * Risk*Rating * 12 * Status 13 T01 1 2 L Low Open 14 T02 4 4 VH Very*High Closed 15 T03 3 3 H High Closed 16 T04 2 5 H High Open 17 R01 1 4 H High Closed 18 R02 2 2 L Low Open 19 R03 2 3 M Medium Open 20 R04 3 1 L Low Open 21 P01 4 2 M Medium Closed 22 P02 5 5 VH Very*High Closed 23 P03 4 1 L Low Open 24 P04 2 3 M Medium Closed 25 X01 2 5 H High Open 26 X99 1 1 L Low Closed 27 X98 1 5 H High Open 28 X97 5 4 VH Very*High Closed 29 S55 3 2 M Medium Open 30 S63 3 2 M Medium Closed 31 S44 2 4 H High Open Risk Log *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.




The first one is correct which in the example is T01, however the next "Open" risk is T04, but on my chart this is plotted as T02, even though it is in T04's position - there is no problem with where the points are plotted it is just their reference numbers. So the next "Open" risk R02 appears in its correct position but is labelled T03 etc. etc. S44 appears as T02

I've tried Tools > Options > Chart > Plot visible cells checking and un-checking the box and also putting an apostrophe in front of each entry in the Reference column but to no avail.

[/i] I can't include the chart as we can't access image sharing sites at work so I can't give a URL for you to go view it at. Also I don't have Excel at home so I can't take the file home and post it from there. Just try and imagine a 5 x 5 grid that plots points in each box according to their scores (they don't overlap as I've use RANDBETWEEN to subtract between 0.25 and 0.75 off each whole number so the points sit in the middle of each box and not on the intersection).

Any help would be greatly appreciated particularly in relation to risk analysis.

Thanks.


I have the following set of data that my boss is wanting to see if it can have a "boxplot" line chart created from this set of data. I have also included an image of the desired final result (manually created the chart)
******** ******************** ************************************************************************> Microsoft Excel - Book2 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout J19 =
A B C D E F G H I J 1 PRODUCT EXPERIENCE REPORT NOT MEETING SPEC RISK LEVEL PRODUCT RETURNED PRODUCT NOT RETURNED RISK ASSESSMENT-1 RISK ASSESSMENT-2 OBSERVED OCCURRENCE 2 Issue Resolved PPRC 1 3 CAPA 1 4 Mfg Issue PPRC 2 5 CAPA 2 6 High Level PPRC 3 7 CAPA 3 8 Med/Low Level PPRC 6 9 CAPA 4 10 Returned PPRC 7 11 CAPA 5 12 Not Returned PPRC 9 13 CAPA 6 14 Patient Risk PPRC 11 15 CAPA 7 16 Physician Dissat PPRC 13 17 CAPA 8 18 Obs Occ ? FMEA PPRC 15 19 CAPA 10 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Here is the image:
http://img15.imgspot.com/u/07/73/11/...1173976649.JPG

Any suggestions/help

[Edited by admin~ changed image to link]


Here is my data, the left table is a raw data drop the right would be a dashboard on another sheet.

I need to get the average days to resolve by Resolution Type

Some can be open without a resololution or with a resolution

I have a weekNum column as well, not sure how to use the AverageIF on data that has gaps. I have other data as well if it will help averageif the data.





******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout I12 =
A B C D E F G H I 1 Incident Number Open/Closed Resolved Reason Days to Resolve   Dashboard 2 223423   Reboot 2   Resolved Reason Reboot Reset Replace 3 23423 Open  Reset 4   Avg Days to Resolve 4.33 5 5.5 4 456645 Open                5 645365 Closed               6 457456                 7 67676 Closed Reboot 6           8 345347 Open Replace 7           9 567   Reset             10 34534 Open Reboot 5           11 234 Open Reset 6           12 454 Closed Replace 4           Sheet1  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


I hope my data example below is clear enough. I'm trying to accomplish the following. With Vlookup I'm trying to identify new issue. Therefor I suggest to use the ID in column a. I only want to check issues with open in column b. If there is a new issue the output should be the data in column c. But now the difficult part. When the ID is in both data sets but the status in column b is open then the output should be also column c. For example see ID 4.

kolom a kolom b kolom c
kolom a kolom b kolom c

Output 1 open low
1 open low
1
2 closed medium
2 closed medium
2
3 closed high
3 closed high
3
4 closed low
4 open low
4 low 5 open medium
5 open medium
5




6 open low
6 low



7 open medium
7 medium



8 open high
8 high



9 open low
9 low



10 open medium
10 medium

I hope somebody can help me out here!

Thanks!



Hi I want to select 2 inputs from 2 drop down lists (done) on a risk matrix ie "possbility" against "severity" and have another cell automaticaly input the HIGH MEDIUM or LOW from my existing excel 4 x 5 matrix in the same colours.

Column4 Column5 Column6
Very Serious Rare


Rare Unlikely Possible Likely Almost Certain
Major MEDIUM MEDIUM HIGH HIGH HIGH
Very Serious LOW MEDIUM HIGH HIGH HIGH
Serious LOW MEDIUM HIGH HIGH HIGH
Significant LOW LOW MEDIUM MEDIUM MEDIUM
Slight LOW LOW LOW LOW MEDIUM
No Impact LOW LOW LOW LOW LOW




Appreciate any help for a novice user

Best regards


I have this table of data that will grow as time passes (new months data will be added)
******** ******************** ************************************************************************> Microsoft Excel - Fiscal Forecasting Template v2.2mikesupdate.xlsx ___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout E1 F1 G1 H1 C2 D2 E2 F2 G2 H2 C3 D3 E3 F3 G3 H3 C4 D4 E4 F4 G4 H4 C5 D5 E5 F5 G5 H5 C6 D6 E6 F6 G6 H6 C7 D7 E7 F7 G7 H7 C8 D8 E8 F8 G8 H8 C9 D9 E9 F9 G9 H9 C10 D10 E10 F10 G10 H10 C11 D11 E11 F11 G11 H11 C12 D12 E12 F12 G12 H12 C13 D13 E13 F13 G13 H13 =
C D E F G H 1 Average 2005 2006 2007 2008 2009 2 46,177 41,993 52,767 47,662 42,284 35403 3 46,724 40,657 53,470 49,205 43,565 36672 4 45,608 41,287 52,915 46,775 41,454 34801 5 44,548 40,716 50,669 45,052 41,755 0 6 42,248 39,786 49,314 41,216 38,676 0 7 43,126 38,630 49,989 43,675 40,209 0 8 41,285 39,421 47,692 40,104 37,921 0 9 48,363 53,979 51,587 45,925 41,959 0 10 48,826 54,049 51,295 47,605 42,355 0 11 48,476 56,055 50,400 45,380 42,070 0 12 42,431 50,254 43,415 39,269 36,786 0 13 39,997 48,853 41,840 36,284 33,009 0 Monthly
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



I have this formulas to give me a rolling 6 and 12 month variance what I have to do now if update them by hand as new data is added anyone know of a way to make them Dynamic so as new data is added the old one drops off and the new data takes it place

I am open to a formula or VBA

Thanks in advance


******** ******************** ************************************************************************> Microsoft Excel - Fiscal Forecasting Template v2.2mikesupdate.xlsx ___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout E31 E32 =
B C D E 31 Rolling 6 Month Growth -4.88% 32 Rolling 12 Month Growth -9.19% Monthly
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.




I was wondering if someone could help me with an average function using high, medium, and low as values. What I have is a row of five different risks that have a value of either high, medium, or low and I need to average the five in a separate cell. Not sure if I should assign the text a value in a vlookup table or not? The table is setup like this:RiskA in cell A1, RiskB in cell B1, Risk C in Cell C1, Risk D in Cell D1, Risk E in Cell E1, and countermeasure in Cell F1. To complicate the function I need to subtract a countermeasure column that will also have a value of high, medium, or low before the average is calculated. In other words the value of the counter measure would need to be subtracted from the five risks before figuring the average which should be displayed as text (High, Medium, or Low). Any help greatly appreciated!

I have data for some projects like this -
******** ******************** ************************************************************************> Microsoft Excel - Book2.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D E F G H I 1 * Design Coding Testing Deploy 2 * Start*Date End*date Start*Date End*date Start*Date End*date Start*Date End*date 3 Project*A 16-Mar-07 18-Mar-07 21-Mar-07 20-Apr-07 25-Apr-07 15-May-07 20-May-07 20-Jun-07 4 Project*B 15-Mar-07 28-Mar-07 30-Mar-07 15-Apr-07 16-Apr-07 30-Apr-07 1-May-07 10-May-07 Sheet1 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I want to create a chart OR fill cells in excel sheet corresponding to data above, such that the output is similar to:
******** ******************** ************************************************************************> Microsoft Excel - Book2.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D E F G H I J K L M N O P Q R S 1 * 3-Mar 10-Mar 17-Mar 24-Mar 31-Mar 7-Apr 14-Apr 21-Apr 28-Apr 5-May 12-May 19-May 26-May 2-Jun 9-Jun 16-Jun 23-Jun 30-Jun 2 Project*A * * * * * * * * * * * * * * * * * * 3 Project*B * * * * * * * * * * * * * * * * * * Sheet2 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Any suggestions? Thanks


Sum if returning values when it should be 0?

I am using this formula to sum data biased on a number criteria (weeknum) the issue is it is giving me data when I should have a 0 Does any one have any idea why or a fix?
=SUMIF('Interval P_Hours'!$B$4:$IV$55,L2,'Interval P_Hours'!$B$54:$IV$54)

Issue Higlited in yellow
******** ******************** ************************************************************************> Microsoft Excel - Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout L1 M1 N1 O1 P1 L2 M2 N2 O2 P2 L4 M4 N4 O4 P4 L5 M5 N5 O5 P5 L6 M6 N6 O6 P6 L10 M10 N10 O10 P10 =
L M N O P 1 10 10 10 10 10 2 40 41 42 43 44 3 October (Preliminary) 4 10/1/2007 10/8/2007 10/15/2007 10/22/2007 10/29/2007 5 233 229 224 220 215 6 229 224 220 215 211 7 40 40 40 8 9 10 50 0 10 12 95 11 Summary
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Interval P_Hours Sheet saple data
******** ******************** ************************************************************************> Microsoft Excel - Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout B4 C4 D4 E4 =
B C D E 2 Mon Tue Wed Thurs 3 7/30 7/31 8/1 8/2 4 31 31 31 31 5 6 24 33 17 22 7 20 28 16 18 8 18 23 14 15 9 15 18 11 12 10 12 13 9 10 11 9 9 7 7 12 7 8 5 6 13 7 7 4 5 14 6 6 4 5 15 6 7 4 5 Interval P_Hours
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Interval P_Hours Sheet with Criteria 40 I would expect it to return a 0
******** ******************** ************************************************************************> Microsoft Excel - Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout BM4 =
BM 2 Mon 3 10/1 4 40 Interval P_Hours
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ************************************************************************> Microsoft Excel - Ramp Analysis Expedia 2007 and Ramp Plan_I_org_lock (2) (2).xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout BM54 BN54 BO54 BP54 BQ54 BR54 BS54 =
BM BN BO BP BQ BR BS 54 0 0 0 0 0 0 0 Interval P_Hours
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Thanks in advance


Hi,
Firstly,
I have a very limited knowledge of Excel functions, know nothing of vb except how to cut & paste code to the worksheet but do know how to use Excel basically and some things like conditional formatting ( 3 colours only)... but will try anything until I understand it.

I have a column which contains a vlookup function returning a text value in the same column. (The vlookup references a table on the previous worksheet)
The spreadsheet is a Risk matrix.
The vlookup checks for "likelihood " and "consequence' and returns two columns of results ; Category &Risk rating eg: Cat 1(24) or Cat 1(19) etc and another column of the Description eg: High Risk, Low Risk etc.

This is what happens now:
The column that the vlookup formula is in, is the same column that returns the text values "Extrememly High Risk" " High Risk ", "Low Risk " " Just Tolerable Risk"

This is what i would like to happen:
When the vlookup returns...

"Extrememly High Risk" -colour the cell background Red
" High Risk " - colour the cell background Blue
"Just Tolerable Risk" - colour the cell background Yellow
"Low Risk " - colour the cell background Green"

Could you kindly help me with this, either with extended conditional formatiing, vb code or by changing the vlookup formula.

Rgrds,
Hthr


I am sure this is possible but not sure how to do it and can't seem to find an answer.
I have multiple sheets set out in the form of a log book.
Each sheet has standard text values in column A such as RED, GREEN, BLUE, BROWN, etc. These are in random order and can be duplicated. Each one has a value associated with it in column B.
What I need is a formula that will total the column B values for each item in column A across all sheets.
I would prefer to have all the totals on a seperate sheet.
Example
Sheet 1
******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 RED 1 2 RED 1.1 3 GREEN 3.5 4 BLUE 2 5 RED 1 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Sheet 2
******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 GREEN 5 2 RED 4.5 3 BLUE 2.3 4 BLUE 6 5 BLUE 1 Sheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Summary Sheet
******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 RED 7.6 2 GREEN 8.5 3 BLUE 11.3 4 BROWN 0 Sheet3
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Thanks -Rob-


I keep track of risks. Risks have two parmameters; probability of occurrence and business impact if it occurs. Both of these factors are usually rated in a L-M-H manner. I often sort by these factors so I've used numbers to facilitate sorting, e.g., 1=High, 2=Med, and 3=Low and is the same for both parameters.

What I'm trying to do is create a composite measure of each risk so that all 9 permutations can result in a unique number. This way I can prioritize the risks based on their overall "risk rating." My intent is to show the coun't of risks in a 3x3 grid. The problem is my math ability. How can I come up with a calculation that results in 9 unique values based on this? For example, since 1*2 is the same as 2*1, I cannot easily distinquish a low probability risk with medium impact from a medium probability risk with a low impact, which would result in a different placement on the grid.

Since nested IF statements can only handle 7 situations, and I have 9, I can't take that approach. I thought about a sumproduct approach from within each square on the grid, but that gets messy. I figure a single number will make it quite easy to count.

Ideas?

Thanks in advance. Wayne


In a cell one of the following results will be returned (based on calcs in other columns);
Very Low Risk
Low Risk
Below Average Risk
Above Average Risk
High Risk
Maximum Risk
Not Scored

These results may change over any given month (eg customer moves from low to high)

In the adjacent cell I need to return the following value dependent on the result returned

1 = Very Low Risk
2 = Low Risk
3 = Below Average Risk
6 = Above Average Risk
8 = High Risk
10 = Maximum Risk
99 = Not Scored

Can you let me know how to write this so that every time the text result changes so will the value ... (preferably through an IF function [rather than a macro] becuase I'll need to continually edit eg add new result, change value etc).

Many thanks


Hello everybody,

I want to plot a graph for a risk management model which has 3 pieces of information: the probability, the impact and the risk ID. Please see the attachment to see what I mean. How do I get the ID to be displayed with its respective data point, i.e. get a 2 next to the top right data point?

Regards,
Edward




Hello,
I am a beginner user of Excel. I need to create a chart with 3 sets of data:
different risk category for cancer: high, medium-high, medium-low, low
then segregate the risk category for gender: Women vs Men
also by country, women&men in USA and women&men in Canada.

Basically, I want to create a stackable bar chart showing the risk categories on the X, men vs women on the Y, as well as the country. How can I combine botthese 2 tables together in 1 chart?
Thank you!

USA CANADA
Women Men Women Men
HIGH 37 20 31 21
MED-HIGH 18 12 24 13
MED-LOW 0 11 0 11
LOW 14 44 14 42
Total 69 87 69 87


USA CANADA
HIGH 57 52
MED-HIGH 30 37
MED-LOW 11 11
LOW 58 56

Will someone please tell me what is wrong with the formula in AO39/ It returns #Value, but the data it's using tells me they're values. Cells AJ36 & AK36 are returning False for = ISTEXT.




******** ******************** ************************************************************************> Microsoft Excel - CASH 3 1ST DIGIT.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout AJ36 AK36 AK39 AL39 AM39 AN39 AO39 AP39 AQ39 AR39 AS39 AT39 AU39 =
AJ AK AL AM AN AO AP AQ AR AS AT AU 36 FALSE FALSE 37 COL-0 38 SKIPS MED MAX AVG 0 1 2 3 4 5 6 7 39 0 ###### #### 1.36 0 #### #### ##### #### #### ##### #### WNH
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.







******** ******************** ************************************************************************> Microsoft Excel - CASH 3 1ST DIGIT.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A24 =
A B C D 24 06/06/06 0 0 0 25 06/05/06 0 1 0 26 06/04/06 0 0 0 27 06/03/06 0 1 0 28 06/02/06 0 0 0 29 06/01/06 0 0 0 30 05/31/06 0 0 0 31 05/30/06 0 1 1 32 05/29/06 0 0 0 33 05/28/06 0 0 0 WNH
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


I would like to have a list of the top 5 issues only meeting all following criteria:

- issues with the status "open" and
- issues with the priority "high" and
- PLS FIRST: ALL issues that should already be completed before today()
- AND THAN: issues that are due soon (nearest to today)


The table looks like:

Feuil1
A B C D E F G 1 Due on Issue Priority Open
Date Est. Close Date Act. Close
Date Status 2 03.03.2010 A1 medium 15.02.2010 25.02.2010 16.02.2010 closed 3 03.03.2010 A2 high 15.02.2010 25.02.2010
open 4 04.03.2010 A3 low 01.02.2010 11.02.2010
open 5 05.03.2010 A4 high 12.02.2010 22.02.2010
open 6 09.03.2010 A5 high 13.02.2010 23.02.2010
closed 7 10.03.2010 A6 high 18.01.2010 28.01.2010
open 8 11.03.2010 A7 low 28.01.2010 07.02.2010
open 9 12.03.2010 A8 medium 16.02.2010 26.02.2010 18.02.2010 closed 10 15.03.2010 A9 high 14.02.2010 24.02.2010

11 16.03.2010 A10 high 12.02.2010 22.02.2010




******** ******************** ************************************************************************> Microsoft Excel - Copy of ahtnew1.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A21 B21 C21 D21 E21 F21 G21 A22 B22 C22 D22 E22 F22 G22 A23 B23 C23 D23 E23 F23 G23 A24 B24 C24 D24 E24 F24 G24 A25 B25 C25 D25 E25 F25 G25 =
A B C D E F G 21 6/23/09 7:00 PM postpaid_vqs 0 0 0 0 0 22 6/23/09 8:00 PM postpaid_vqs 1271 1166 568 9 84 23 6/23/09 9:00 PM postpaid_vqs 0 0 0 0 0 24 6/23/09 10:00 PM postpaid_vqs 0 0 0 0 0 25 6/23/09 11:00 PM postpaid_vqs 442 416 410 0 1 Universal
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ************************************************************************> Microsoft Excel - Copy of ahtnew1.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A21 =
A B C D E F G 21 6/23/09 7:00 PM postpaid_vqs 1135 1108 977 0 13 22 6/23/09 8:00 PM postpaid_vqs 1271 1166 568 9 84 23 6/23/09 9:00 PM postpaid_vqs 1208 1189 1144 3 4 24 6/23/09 10:00 PM postpaid_vqs 888 845 821 0 1 25 6/23/09 11:00 PM postpaid_vqs 442 416 410 0 1 Sheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Why some interval my sumproduct formula didnt captured the data
kindly assist. TQ


Hi,

I am trying to write an array formula that will work out how many minutes of tickets are open within a particular month. I have a formula shown on the first table which calculates how many minutes a ticket is open for within a particular month, but this only works for an individual ticket, Basically I neet to populate the 2nd table with an array formula that will calculate the number of minutes within the month that numerous tickets are open, effectively a SUMPRODUCT but I can't get it to work.

The right hand side of Table one doesn't exist in my main file, I am trying to populate the 2nd table using only the first four columns within Table 1.

******** ******************** ************************************************************************> Microsoft Excel - My God.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout F3 G3 H3 F4 G4 H4 F5 G5 H5 F6 G6 H6 F7 G7 H7 F8 G8 H8 F9 G9 H9 F10 G10 H10 F11 G11 H11 F12 G12 H12 F13 G13 H13 F14 G14 H14 F15 G15 H15 F16 G16 H16 =
A B C D E F G H 1     01/02/2008     Jan 08 Feb 08 Mar 08 2           31 29 31 3 W000002 20/01/2008 17:33:26 29/02/2008 02:36:54 56037   16,035.85 40,001.15 - 4 W000001 27/01/2008 01:05:46 20/02/2008 03:17:52 200   41.13 158.87 - 5 W000001 22/03/2008 09:06:18 24/03/2008 08:33:57 789   - - 789.00 6 W000001 24/03/2008 17:56:01 28/03/2008 13:23:42 1,966   - - 1,966.00 7 W000001 07/06/2008 20:27:18 09/06/2008 06:42:22 214   - - - 8 W000001 24/06/2008 17:14:39 10/07/2008 07:19:43 2,882   - - - 9 W000001 13/08/2008 06:29:36 16/08/2008 08:14:53 2,344   - - - 10 W000001 27/09/2008 15:49:53 28/09/2008 09:26:05 1,026   - - - 11 W000001 24/10/2008 20:33:09 26/10/2008 10:04:30 13   - - - 12 W000001 20/11/2008 02:03:28 26/11/2008 15:38:23 6645   - - - 13 W000001 20/11/2008 08:18:32 24/11/2008 11:26:38 1073   - - - 14 W000001 15/12/2008 12:20:59 18/01/2009 15:54:22 3,445   - - - 15 W000001 18/01/2009 16:32:47 19/01/2009 12:50:47 317   - - - 16 W000001 26/12/2007 14:24:00 24/03/2008 08:33:57 127810   44,640.00 41,760.00 33,633.95 Summary  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



******** ******************** ************************************************************************> Microsoft Excel - My God.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout D20 =
A B C D 20   Jan 08 Feb 08 Mar 08 21 W000001 ??? ??? ??? 22 W000002 ??? ??? ??? Summary  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I know this is as clear as mud guys but would really appreciate some help with this.


hello,
I have a sheet that is very simple, it is 2 dates and then the days remaining until that date.

My question is: Why if i open this sheet in the morning, before noon, does it calculate the correct days left, but then if I open the same sheet after noon, it gives me the result on 1 day less????

any help??? anyone?
thanks.

******** ******************** ************************************************************************> Microsoft Excel - birthday countdown!.xls ___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout C2 D2 C3 D3 C4 D4 =
B C D E 1 Event Today Days*Left * 2 03/24/03 03/20/03 3* * 3 04/02/03 03/20/03 12* * 4 04/09/03 03/20/03 19* * Sheet1 *
[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.




Hi all, I am trying to make a spreadsheet that has the following:

| Col A | Col B | Column C | Column D | Column E | Column F |
| Name | Identifier | Size of lesion (a,b,c) | Risk (high/low) | Date of service | Date of Follow-up |

What I am looking for is if I put in:

size a and low risk, date of f/u = None needed
size a and high risk, date of f/u = <date of service + 365> (one year)
size b and low risk, date of f/u = <DOS + 365>
size b and high risk, date of f/u = <DOS + 180> to <DOS + 365>
size c and low risk, date of f/u = <DOS + 180> to <DOS +365> AND <DOS + 545> to <DOS + 730>
size c and high risk, date of f/u = <DOS + 90> to <DOS + 180> AND <DOS + 270> to <DOS + 365>

I hope that makes sense and someone can help me out on this.
Thanks,
Criss (Sifubear)

Everyone:

I currently have a data table that I would like to summarize using a pivot table. An example of my data is below. Following this, I provide an example of what my basic pivot table should look like. Any suggestions would be appreciated. If there is a different solution that would work better, I am open to that too.

Thanks.

Raw Data:
******** ******************** ************************************************************************> Microsoft Excel - Book15.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D E 1 Lot A_Start*to*Concrete B_Concrete*to*Insulation C_Insulation*to*Cabinets D_Cabinets*to*Final 2 1 On*Time On*Time On*Time On*Time 3 2 On*Time On*Time Very*Late Very*Late 4 3 On*Time On*Time Very*Late Very*Late 5 4 On*Time On*Time On*Time On*Time 6 5 On*Time On*Time On*Time On*Time 7 6 On*Time Very*Late Very*Late Very*Late 8 7 Early Late Very*Late Very*Late 9 8 On*Time On*Time Early Very*Late 10 9 On*Time Late Very*Late Very*Late 11 10 On*Time Very*Late Very*Late Very*Late 12 11 On*Time On*Time On*Time On*Time 13 12 Early Late Late Late Sheet1 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

This is what the pivot table (or other solution) should look like:
******** ******************** ************************************************************************> Microsoft Excel - Book15.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D E F G 1 * Very*Early Early On*Time Late Very*Late TOTAL 2 Start*to*Concrete * 2 10 * * 12 3 Concrete*to*Insulation * * 7 3 2 12 4 Insulation*to*Cabinets * 1 4 1 6 12 5 Cabinets*to*End * * 4 1 7 12 6 TOTAL * 3 25 5 15 48 Sheet1 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Dear,

I need a pivot table. But there are two data range (in two sheet), each have exactly same headers. However, after I create a pivot table by multi consolidation range, I cannot find the filedname such as name, unit price, etc. instead there is field named row or column and etc, It is totally different and not the idea i want. Can anyone help me how to create a pivot table covering differnt range, but styil have the normal column headers (not th nameof column or row etc)? th reason i need to cover two range because the data already exceed one sheet can take (65536 rows), i need to set two range for pivot table.

******** ******************** ************************************************************************> Microsoft Excel - excel pivot test.xls ___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout D5 =
A B C D 1 Model Unit*price Qty Sales*Amount 2 A 1 100 100 3 B 2 100 200 4 C 3 100 300 5 D 4 100 400 USA *
[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ************************************************************************> Microsoft Excel - excel pivot test.xls ___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout D5 =
A B C D 1 Model Unit*price Qty Sales*Amount 2 E 1 100 100 3 F 2 100 200 4 G 3 100 300 5 H 4 100 400 Asia *
[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ************************************************************************> Microsoft Excel - excel pivot test.xls ___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D E 1 Page1 (All) * * * 2 * * * * * 3 Sum*of*Value Column * * * 4 Row Qty Sales*Amount Unit*price Grand*Total 5 A 100 100 1 201 6 B 100 200 2 302 7 C 100 300 3 403 8 D 100 400 4 504 9 E 100 100 1 201 10 F 100 200 2 302 11 G 100 300 3 403 12 H 100 400 4 504 13 Grand*Total 800 2000 20 2820 Multi-coso pivot *
[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


******** ******************** ************************************************************************> Microsoft Excel - excel pivot test.xls ___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 Unit*price (All) * * 2 Qty (All) * * 3 * * * * 4 Sum*of*Sales*Amount * * * 5 Model Total * * 6 A 100 * * 7 B 200 * * 8 C 300 * * 9 D 400 * * 10 E 100 * * 11 F 200 * * 12 G 300 * * 13 H 400 * * 14 Grand*Total 2000 * * normal pivot *
[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Hello,
I would like to compare the values of one column in each worksheet in a workbook (typically 3 worksheets are involved).

If there is a match across all three worksheets, then the fill color of that matching value would be 'green'. If not a match, then the fill color would be something else, say 'pink'.

An example using fruit in which 'pear' is the only value in all three worksheets is below. Also, the columns to compare (Sheet1-A, Sheet2-F, and Sheet3-C) are typical, but I can change.

XLML

Sheet1-Column A
******** ******************** ************************************************************************> Microsoft Excel - COMPARE.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 apple * * * 2 strawberry * * * 3 pear * * * Sheet1 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Sheet2 - Column F
******** ******************** ************************************************************************> Microsoft Excel - COMPARE.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout F1 =
F G H I 1 pear * * * 2 blueberry * * * Sheet2 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Sheet3 - ColumnC
******** ******************** ************************************************************************> Microsoft Excel - COMPARE.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout C1 =
C D E F 1 strawberry * * * 2 pear * * * 3 apricot * * * 4 blueberry * * * Sheet3 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Hi there,
I've been struggling this morning to create a summary sheet that autocalculates, though i can't say my knowledge of Excel is superb.
I have a summary sheet that i want to auto sum data from another sheet.
So the raw data is entered with the date (week number) and then number of A,B and C items and then the summary sheet summarizes by week.
I've tried fiddling with IF conditions with no luck. Is there a way to have the formula in the summary sheet cell B4 say something like IF CELL A?(in the week sheet= WEEK 1, SUM B?

Please let me know if i've not explained very well.
Thanks,
******** language="JavaScript" ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 SUMMARY 2 WK TOTAL A TOTAL B TOTAL C 3 1 4 2 5 3 6 4 7 5 summary
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** language="JavaScript" ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout A1 =
A B C D 1 WK A B C 2 1 10 5 1 3 2 20 10 2 4 2 30 15 3 5 3 20 20 4 6 3 20 25 5 7 3 10 30 6 8 4 45 35 7 9 5 25 40 8 10 5 75 45 9 11 5 0 50 10 week
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.