|
Xy Scatter Graph Data Labelling
|
|
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!
Similar Excel Video Tutorials
Chart Basics - Excel Charts
- The Excel Basics Series shows a systematic description of what Excel can do from beginning to end. #16 Video topics: 1)Link Chart labels to ...
Charts: Line & X Y Scatter
- Learn when to use the Line chart and when to use an X-Y Scatter diagram. See the difference between the Line and the X-Y Scatter diagram Charts. Line ...
Helpful Excel Macros
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
Similar Topics
When I carry out a standard filter on a list of data Excel is putting the incorrect labels on the chart that is being plotted.
In the list of data that is labelled 1 - 10, and 4, 6, 7 and 8 remain after a the filter, they will be plotted on the chart as 1, 2, 3 and 4.
******** ******************** ************************************************************************>
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
=
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
21
9
4
2
M
Medium
Closed
22
10
5
5
VH
Very*High
Closed
23
*
*
*
*
*
*
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.
Am I missing a link somewhere to stop this from happening?
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 am trying to write a nested if statement for my excel table. I calculated the betas of the stock portfolio. I would like to create an if statement that would do something like this:
if beta is less than 1 -- Below Average Risk
if beta is greater than 1 but less than 1.25 -- Average Risk
if beta is greater than 1.25 but less than 1.75 --Medium Risk
if beta is greater than 1.75 -- High Risk
Here is my table with the formulas and such.
******** ******************** src="*********>
*********>
Microsoft Excel - JessicaHumphreys_CCP.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
B6
C6
D6
E6
F6
G6
B7
C7
D7
E7
F7
G7
B8
C8
D8
E8
F8
G8
B9
C9
D9
E9
F9
G9
B10
C10
D10
E10
F10
G10
=
A
B
C
D
E
F
G
5
Investment Name
Purchase Price
Current Market Value
Gain/Loss ($)
Gain/Loss (%)
Beta
Riskiness
6
Portfolio 1
$ 99,783.35
$ 101,046.65
$ 1,263.30
1.27%
1.01
High
7
Portfolio 2
$ 99,910.50
$ 104,386.35
$ 4,475.85
4.48%
0.77
High
8
Portfolio 3
$ 99,940.06
$ 110,909.45
$ 10,969.39
10.98%
1.26
High
9
Portfolio 4
$ 99,867.25
$ 102,469.00
$ 2,601.75
2.61%
0.85
High
10
Portfolio 5
$ 99,998.70
$ 109,449.63
$ 9,450.93
9.45%
0.60
High
Portfolio Analysis
[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.
Hey there,
did a forum search and a quick google for an answer to this one, but nothing makes her click to date!
I've an open workbook with set of random cumulative numeric values in cloumn A.
EG:
0.000
0.154
0.362
0.743
1.403
1.403
3.457
3.802
3.939
4.786
8.473
8.531
8.624
etc
What I need to do is find a less than or equal to row from Column A on a second workbook.
EG:
A B C D
0.000 0.000 0 Start of a HIGH risk section
3.297 3.297 0 End of the HIGH risk section
3.298 0.001 0 Start of a LOW risk section
4.376 1.078 0 End of the LOW risk section
4.377 0.001 0 Start of a HIGH risk section
4.509 0.132 0 End of the HIGH risk section
4.510 0.001 0 Start of a LOW risk section
5.960 1.450 0 End of the LOW risk section
5.961 0.001 0 Start of a HIGH risk section
6.779 0.818 0 End of the HIGH risk section
6.780 0.001 0 Start of a MEDIUM risk section
7.074 0.294 0 End of the MEDIUM risk section
7.075 0.001 0 Start of a HIGH risk section
7.682 0.607 0 End of the HIGH risk section
7.683 0.001 0 Start of a LOW risk section
8.719 1.036 0 End of the LOW risk section
8.720 0.001 0 Start of a HIGH risk section
Once found I need to copy the corresponding LOW/MEDIUM/HIGH vaule back into the corresponding row, column D on the initial workbook.
I've started with:
Dim x As Long
Dim Milage As Integer
For x = 13 To Range("A65536").End(xlUp).Row
Milage = Cells(x, 1)
(??????)
If InStr(1, Cells(x, 4), "HIGH") Then
Cells(x, 4) = "High"
As you can see I've no idea how to initially select the corresponding row on the second workbook.
Any help/suggestions would be much appreciated!
Shane.
I have inherited a formula for color highlighting pivot table fields base on set criteria. We are adding a new "Critical" SLA that needs to be included in the color coding formula. The current formula in column J below is:
=IF(D13>VLOOKUP(B13,$G$5:$H$8,2,FALSE)+2, "red",IF(D13VLOOKUP(B13,$G$5:$H$8,2,FALSE), "yellow","")))
******** ******************** ************************************************************************>
Microsoft Excel - IPG_CLKM_Report_1.23.08.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
J12
J13
J14
J15
J16
J17
J18
J19
J20
J21
J22
J23
J24
J25
J26
=
A
B
C
D
E
F
G
H
I
J
K
1
Service Level Turnaround Times
2
3
Lifecycle State
(All)
Standard SLAs
4
Action Requested
(All)
Critical
1
day
<< New SLA category
5
Month Entered
(All)
Very High
1
day
6
Year Entered
(All)
High
3
days
7
Month Closed (or Report Update Date if not closed)
(All)
Medium
3
days
8
Year Closed (or Report Update Date if not closed)
(All)
Low
10
days
9
10
Data
11
Language
Feedback Priority
Item
coiunt
Days Open (Ave.)
12
Chinese (Simplified)
Critical
4
5.5
#N/A
<< New SLA category
13
High
92
6.8
red
14
Low
152
8.7
green
15
Medium
52
9.8
red
16
Chinese (Traditional)
High
16
11.9
red
17
Low
96
3.1
green
18
Medium
4
1.8
green
19
English (U.S.)
Critical
4
44.3
#N/A
20
High
339
51.0
red
21
Low
436
43.2
red
22
Medium
183
43.5
red
23
Very High
103
40.4
red
24
French
High
10
19.1
red
25
Low
2
17.0
red
<< New SLA category
26
Medium
1
18.0
red
IPG Monthly ClosedSummary
[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.
Thank you in advance for your help.
I am trying to create a formula that will look up an S&P debt rating (AAA thru BB) from the table below, by matching the business risk score (1-4) with a calculated financial ratio.
******** ******************** ************************************************************************>
Microsoft Excel - Match and 2 column vlookup.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
G3
I3
K3
M3
G4
I4
K4
M4
G5
I5
K5
M5
G6
I6
K6
M6
=
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
1
FF0 / Interest Rating
2
AAA
AAA-
AA+
AA
AA-
A+
A
A-
BBB+
BBB
BBB-
BB+
BB
3
Business Risk
1
6.00
3.50
3.00
2.75
2.50
2.25
2.00
1.75
1.50
1.25
1.00
0.75
0.50
4
2
6.00
4.50
4.00
3.50
3.00
2.75
2.50
2.25
2.00
1.50
1.00
0.75
0.50
5
3
7.00
5.00
4.50
4.00
3.50
3.25
3.00
2.75
2.50
2.13
1.75
1.50
1.00
6
4
7.00
5.50
5.00
4.60
4.20
4.00
3.80
3.65
3.50
3.08
2.65
2.50
1.50
7
8
Year
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
9
Co. Business Risk =
2
FFO/Interest
3.6x
1.1x
1.4x
1.8x
2.1x
2.5x
5.4x
5.4x
5.4x
5.4x
10
Rating
AA+
BBB
BBB
BBB+
A-
A
AAA
AAA
AAA
AAA
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.
In this example, the calculated ratios are in D9:M9. Looking at the first ratio, 3.6 will fall between AA+ and AA for a business risk score of 2. So, I would like a formula in cells D10:M10 that reflects the corresponding rating for changes in business risk and the calculated ratios. Can you please help?
Thanks,
I have three tables, and I am trying to pull the lowest, middle, and highest number for each item per set (as detailed below).
Each table has the same structure and the same Item names.
How would you recommend I do this?
Many thanks.
TABLE 1 - I want to populate this
******** ******************** ************************************************************************>
Microsoft Excel - tmp.xls
___Running: 11.0 : OS =
(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
Price breaks Per Unit
2
3
Item
1-10
11-25
26-50
51-100
101-150
151-200
201-500
4
a
Low
159
223
24
47
etc
etc
etc
5
a
Medium
598
523
102
105
6
a
High
745
687
304
207
7
b
Low
etc
8
b
Medium
9
b
High
10
c
Low
11
c
Medium
12
c
High
13
d
Low
14
d
Medium
15
d
High
16
e
Low
17
e
Medium
18
e
High
19
f
Low
20
f
Medium
21
f
High
22
g
Low
23
g
Medium
24
g
High
How do I do this
[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.
Data populates from the tables below that are in the same worksheet
Table 1.
******** ******************** ************************************************************************>
Microsoft Excel - tmp.xls
___Running: 11.0 : OS =
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F28
=
A
B
C
D
E
F
G
H
I
J
K
1
Item
Item code
Item Name
Price breaks Per Unit
2
3
1-10
11-25
26-50
51-100
101-150
151-200
201-500
4
a
1
dog
159
523
102
249
744
474
882
5
b
2
cat
329
167
255
313
555
681
686
6
c
3
banana
543
195
685
206
997
113
242
7
d
4
phone
859
390
225
480
288
635
21
8
e
5
car
207
182
435
884
400
264
275
9
f
6
house
407
411
316
557
69
649
521
10
g
7
watch
941
237
83
43
95
321
931
Data_Table1
[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.
Table 2:
******** ******************** ************************************************************************>
Microsoft Excel - tmp.xls
___Running: 11.0 : OS =
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
C13
=
A
B
C
D
E
F
G
H
I
J
K
1
Item
Item code
Item Name
Price breaks Per Unit
2
3
1-10
11-25
26-50
51-100
101-150
151-200
201-500
4
a
1
dog
598
687
24
105
938
428
560
5
b
2
cat
408
909
428
63
828
692
848
6
c
3
banana
112
595
21
934
722
940
274
7
d
4
phone
280
429
986
668
275
636
219
8
e
5
car
505
92
613
121
168
277
592
9
f
6
house
775
363
329
781
734
789
324
10
g
7
watch
655
198
492
641
138
963
756
Data_Table2
[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.
Table 3
******** ******************** ************************************************************************>
Microsoft Excel - tmp.xls
___Running: 11.0 : OS =
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
G30
=
A
B
C
D
E
F
G
H
I
J
K
1
Item
Item code
Item Name
Price breaks Per Unit
2
3
1-10
11-25
26-50
51-100
101-150
151-200
201-500
4
a
1
dog
745
223
304
47
48
1
613
5
b
2
cat
622
915
139
909
385
485
706
6
c
3
banana
537
372
544
34
831
454
692
7
d
4
phone
534
143
277
890
333
93
474
8
e
5
car
951
140
359
957
730
989
299
9
f
6
house
260
227
362
933
827
19
705
10
g
7
watch
232
382
676
656
73
107
343
Data_Table3
[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 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]
In this mockup of a sheet i need a macro that will on another sheet in columns list the people with the same status
******** ******************** ************************************************************************>
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
J2
=
A
B
C
D
E
F
G
H
I
J
1
fred
george
Harry
Ron
Dean
Neville
bob
john
sarah
lucy
2
high
low
average
perfect
average
average
low
low
high
not worked
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.
******** ******************** ************************************************************************>
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
E4
=
A
B
C
D
E
1
Not Worked
Low
Average
Perfect
HIGH
2
Lucy
George
Harry
Ron
Fred
3
Bob
Dean
Sarah
4
john
Neville
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.
Thanks
Here is my example:
******** ******************** ************************************************************************>
Microsoft Excel - Book1
___Running: xl2002 XP : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
D2
D3
D4
D5
=
A
B
C
D
E
F
G
H
1
SR#
Open
Closed
Days
*
1-30
31-60
61-90
2
a123
1/14/03
1/14/03
1
*
*
*
*
3
a234
1/14/03
2/3/03
14
*
*
*
*
4
a951
1/14/03
4/15/03
64
*
*
*
*
5
b753
1/14/03
5/25/03
92
*
*
*
*
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.
I'd like the number of research's to be summed in the corresponding number of days column....for instance in the above example, the "Days" column represents the number of days the Research was open before it was closed. Column F is a range of days open. 1-30. G, the same, 31-60, etc...how can I get the number of researches into the correct range of days open column? For instance, the number of research's that were open in between 1-30 days is 2, 31-60 is 0, 61-90 is 1, etc...
Any ideas??
Thanx,
Dave M.
hello, i am trying to pull the list of data to another sheet for simplistic reasons and i cannot figure out what i need to do to accomplish this. i have tried index, match, indirec, etc. what i would like to do is based on the date(s) and split/skill pull calls handled for a month. meaning if i choose 'S48 High Risk PG' for the month of january, get the total calls handled for that skill for the month. as you can see in the sample, there are many types with the same date.
******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>
Microsoft Excel - Marshall CMS data for Kenneth.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
A2
=
A
B
C
D
E
2
Date
Split/Skill
ASA
Avg Aban Time
Calls Handled
3
4
1/4/2010
S48 High Risk PG
63.69231
0
13
5
1/4/2010
S47 Open Enrollment
52.65
0
20
6
1/4/2010
S42 TX Broker Mbrshp
101
0
8
7
1/4/2010
S195 EDS PPO
27.4375
31
80
8
1/4/2010
S58_Oldcastle
57.67188
434.5
64
9
1/4/2010
S175 Agent Traker
51.54167
43
48
10
1/4/2010
S196 EDS CDHP
46.25
0
36
11
1/4/2010
S7 High Risk PG
18.5
0
2
12
1/4/2010
S10_TX A M_ SMU
81.81081
69.83334
74
13
1/4/2010
S4_Major Accounts
75.30811
139.3529
1269
14
1/4/2010
S19_TMAIT
39
0
3
15
1/4/2010
S18 Perf Guar
56.63088
100.7188
1425
16
1/4/2010
S11 Perf Guar II
56.32915
156.125
319
17
1/4/2010
S12 Tx Instruments
78.02752
108.25
109
All Other
[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 help you could provide would be appreciated.
I need a formula in Col's G:K that will give me the ranking of the digits in Col's B:F, from low to high. For example, the digits in B17:F17, 10,20,6,18,36, the ranking from low to high would be 2,4,1,3,5. If possible, I would like for the formula to return in this format 24135, that way I only use 1 column. Thanks for all suggestions.
******** ******************** ************************************************************************>
Microsoft Excel - F5 UNORDERED DRAWS.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
B17
=
B
C
D
E
F
G
H
I
J
K
17
10
20
6
18
36
2
4
1
3
5
18
11
2
27
14
26
2
1
5
3
4
19
1
28
33
22
24
1
4
5
2
3
20
18
10
13
24
19
21
31
29
15
14
12
LAST DIGIT
[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!
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 need a formula to cover the following, not sure how to construct.
******** ******************** ************************************************************************>
Microsoft Excel - Book3
___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
C5
=
C
D
E
F
G
5
Issue Date
Task
Priority
Due Date
Status
6
26/11/2007
Write Note
14/01/2008
CLOSED
7
14/01/2008
Sign Note
18/01/2008
OPEN
8
14/01/2008
Post Note
18/01/2008
CLOSED
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.
If the Task is "CLOSED" then the Priority (Column E) Should be "Green"
If the Task is "Open", and the due date has expired or is within 2 weeks from the date Today(), then Column E should be "RED"
If the Task is Open and is not due for more than 2 weeks, then the "Prioroty" (Column E)should show a as "Amber"
In the spreadsheet below, I need to create labels in the format "High Range: 114-220" in cells c12 thru c14. Column D contains the groups for which I need to create ranges. I need something like a MaxIf() and MinIf() formula that works like SumIf(). Any ideas?
Thanks
Dale.
******** ******************** ************************************************************************>
Microsoft Excel - MapData.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
B12
B13
B14
=
A
B
C
D
1
Widgits
Index
Index Group
2
Alabama
90
220
High
3
Arizona
178
53
Low
4
California
150
75
Low
5
Michigan
180
127
High
6
New York
130
90
Med
7
Ohio
95
90
Med
8
Oregon
110
114
High
9
Texas
125
100
Med
10
11
Range
12
High
380
13
Med
350
14
Low
328
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've tried to find a solution and this is as close as i could get, but i'm missing something.
Can anyone assist? I'm trying to return the value of the rating that corresponds.
thanks in advance.
******** ******************** ************************************************************************>
Microsoft Excel - New Evals 5-08.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
F13
=
E
F
G
H
13
2.50
#N/A
15%
Alb Dialer
[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 - New Evals 5-08.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
C39
C40
D40
C41
D41
C42
D42
D43
=
C
D
E
F
38
Points
Rating
39
3.8
100.0
1
40
3.2
3.7
2
41
2.6
3.1
3
42
2.0
2.5
4
43
0.0
1.9
5
Matrix
[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 - 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
E
F
G
H
1
2
Open
<< Status
3
Project
< 0
0 - 15
16-30
31-45
45-60
> 60
4
Proj1
2
3
0
1
4
0
5
6
Data Table
7
Projects
Status
Days
8
Proj1
Open
-2
9
Proj1
Open
-3
10
Proj1
Open
14
11
Proj1
Open
15
12
Proj1
Open
10
13
Proj1
Open
31
14
Proj1
Open
55
15
Proj1
Open
57
16
Proj1
Open
47
17
Proj1
Open
60
18
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 how my data looks like --- want to count all Proj1 in Status Open falling within the ranges
I want to make a formula on another sheet that looks for a draw number on this sheet and then returns the heading from the column.
eg. Sheet 1
if cell 47 = 3 then cell 49 (where formula is) will return LOW PERFORMERS.
Can anyone help with this. Thanks
******** ******************** ************************************************************************>
Microsoft Excel - Daily Und-Ovr.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
K6
=
A
B
C
D
E
F
G
H
I
J
K
1
Low Performers
Draw
Average Performers
Draw
High Performers
Draw
Perfect
Draw
2
LOW
46
AVERAGE
44
HIGH
12
Perfect
1
3
LOW
47
AVERAGE
45
HIGH
13
Perfect
2
4
LOW
48
AVERAGE
58
HIGH
14
Perfect
3
5
LOW
49
AVERAGE
59
HIGH
16
Perfect
4
6
LOW
50
AVERAGE
72
HIGH
17
Perfect
5
Performance
[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.
Alright, I have been playing with several formulas to get what I need and seem to be missing something or just not understanding how it works.
Here is a copy of what I need on a daily basis:
******** ******************** ************************************************************************>
Microsoft Excel - School Support Daily Dashboard.xlsm
___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
C4
D4
C5
D5
C6
D6
=
A
B
C
D
3
Date:
Weekday
Cases Closed
Cases Opened
4
06/01/08
Sun
0
0
5
06/02/08
Mon
353
0
6
06/03/08
Tue
389
#VALUE!
June
[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 need the amount of cases closed and created on each calendar day for a specific group of people. I've used this sumproduct formula in D6 before, but I'm not having luck with it now. Plus, I know this will make the sheet very slow to calculate because of the nature of sumproduct. As you can see, I've tried to use countifs in D5 but I'm just not having luck. Any help would be greatly appreciated.
Here is the data info:
******** ******************** ************************************************************************>
Microsoft Excel - School Support Daily Dashboard.xlsm
___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
A2
P2
A3
P3
A4
P4
A5
P5
A6
P6
=
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
1
Case Number
Opened Date
Closed Date
Account Name
Case Reason
Status
Specific issue
Open
Closed
Age (Days)
Region
High Touch
Case Owner
Case Created By
Actual Age Days
2
Jarrett HillClosed
2417
01/02/08
01/02/08
The Art Institute of Ohio - Cincinnati
Profile
Closed
-
0
1
0
National
1
Jarrett Hill
Jarrett Hill
0
3
Jacola SearsbrookClosed
2418
01/02/08
01/02/08
University of Detroit Mercy
Certification
Closed
Didn't receive
0
1
0
6
1
Jacola Searsbrook
Jacola Searsbrook
0
4
Tracy HillClosed
2419
01/02/08
01/07/08
Hodges University-Naples
Profile
Closed
-
0
1
5
4
0
Tracy Hill
Harriet Martin
3
5
Tracy HillClosed
2420
01/02/08
01/03/08
New College of Florida
Profile
Closed
Received new profile
0
1
1
4
0
Tracy Hill
Doris Pinex
1
6
Tracy HillClosed
2423
01/02/08
01/03/08
Florida Institute of Technology
Profile
Closed
Received new profile
0
1
1
4
0
Tracy Hill
Doris Pinex
1
Case Data
[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!
Hello,
I have a table of text data that I need to view in a summary matrix but I'm stumped how to do it...pivot tables are obviously no use as they are numerical , and I want to do some specific things...
I've never posted an html range before so apologies if I'm off-piste. the formatting isnt' coming through very well, but previews fine in IE
I'll post the output I need in a following mail...any help greatly appreciated...!
INPUT (the 'sub-projects' are outline headings, as are the 'components'):
******** ******************** ************************************************************************>
Microsoft Excel - scope table WIP.xls
___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
B7
=
B
C
D
E
F
G
7
sub-projects
component
feature
summary
risk
link
8
project*1
*
*
*
*
*
9
*
character
*
*
*
*
10
*
*
display*hi-res*character
blah
high
//wikipage
11
*
*
facial*animation
blah*blah
low
//wikipage
12
*
vehicle
*
*
*
*
13
*
*
display*hi-res*vehicle
blah
high
//wikipage
14
*
*
articulated*doors
*
low
//wikipage
15
project*2
*
*
*
*
*
16
*
character
*
*
*
*
17
*
*
display*lo-res*character
blah
low
//wikipage
18
*
vehicle
*
*
*
*
19
*
*
display*lo-res*vehicle
blah
low
//wikipage
20
project*3
*
*
*
*
*
21
*
character
*
*
*
*
22
*
*
*display*hi-res*character
blahdety
high
//wikipage
23
*
*
facial*animation
blah*blah
low
//wikipage
24
*
vehicle
*
*
*
*
25
*
*
display*lo-res*vehicle
blah
low
//wikipage
mr excel sample
*
[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 4 tabs with data, which are sub categories for account types, call them Attorney's, Doctor's, CPA's, Other. Each tab contains account information and a risk rating code of either low, medium or high.
I want a summary sheet to pull from all the other sheets based on the risk rating that I select. In other words, I would like to select high as the risk rating and all high risk accounts will populate on sheet 7.
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 the below info:
******** ******************** ************************************************************************>
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
A80
=
A
B
C
D
E
F
G
H
I
80
Completed Tasks - Summary
81
Ref
Task
Due Date
Priority
% Complete
Comments
82
83
84
85
86
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 need to summarise the data, so that the below table willselec t those tasks that are closed, and were Completed (due) in the last 30 days.
I have arrays to bring over "all" the closed items, but as the list grows each week/month, I want to summarise it so that it ONLY shows the tasks that were closed in the last 30 days or so:
******** ******************** ************************************************************************>
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
A80
=
A
B
C
D
E
F
G
H
I
80
Completed Tasks - Summary
81
Ref
Task
Due Date
Priority
% Complete
Comments
82
83
84
85
86
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.
******** ******************** ************************************************************************>
Microsoft Excel - Book1
___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
T1
=
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
1
Due
4Cast
Actual
Risk %
Due
4Cast
Actual
Risk %
Due
4Cast
Actual
Risk %
Action
Context
Overall %
BRAG
2
02-Feb-2007
13-Apr-2007
13-Apr-2007
0
27-Apr-2007
14-Sep-2007
0
25-May-2007
14-Sep-2007
0
Sample Actions Here
Sample Context Here
0
G
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.
Hi Guys
Above is an excerpt from a sheet that details baseline, forecast and actual delivery date information for a task along with an adjoining cell for a percentage of associated risk - each task has 3 milestones - hence 3 occurrences of baseline, 4cast, actual risk %.
I'm struggling with a formula in column S that averages the overall risk percentage for those milestones without a populated 'Actual' field. In the example posted - an average of columns I and O is needed because column C is polulated. However, if column C was empty I'd need an average of D, I and O.
I appreciate that this explanation may read in a rather convoluted way and apologise for that.
Any help appreciated.
|
|
