Email:      Pass:    Pass?
Advertisements


Free Excel Forum

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!


Similar Excel Video Tutorials

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
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
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.


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"


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!



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.


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.


******** ******************** ************************************************************************> 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.


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.


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