Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel charting alpha 3

Status
Not open for further replies.
cjbrown815

cjbrown815

IS-IT--Management
Mar 2, 2006
525
US
Hello, Excel 2007

I have values A,B,C,D,AA that are from a sediment test

I need to chart the percentage of each value entered from the beginning of the year to today. No clue where to start. Thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Sort by date Sort by votes



hi,

I see that you're on a 'sedimental journey.' ;-)

Is the question regarding how to 1) calculate the percentages or 2) how to plot the results, in a 100% Stacked Column Chart, I assume?

Please post some sample data.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Hi Skip, and thanks!!

Both,
We want to see if there has been a increase in the lower values since the beginning of the year. I have to present the percentage of each value in a chart.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote



...and your sample data?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
REG_ON DATE 01/03/2011
Product code 30320
Lot Number 01234567
Test Name SEDIMENT
Result A

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote


So your data is on the sheet EXACTLY like this, withour regard to column???
[tt]
REG_ON DATE 01/03/2011
Product code 30320
Lot Number 01234567
Test Name SEDIMENT
Result A
[/tt]
So how can we calculate ANY percentage with this data?

Please help YOURSELF out by posting CLEAR, CONCISE and COMPLETE information that can aid someone trying to aid YOU.


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Sorry, they are columns not rows



REG_ON DATE Product Lot TEST RESULT
01/03/2011 30320 00301411 SEDIMENT A 01/10/2011
01/04/2011 00302411 SEDIMENT A 01/10/2011
01/04/2011 00303411 SEDIMENT A 01/10/2011
01/04/2011 00304411 SEDIMENT A 01/10/2011
01/04/2011 00305411 SEDIMENT A 01/10/2011
01/03/2011 30200 00321411 SEDIMENT A 01/10/2011
01/03/2011 00322411 SEDIMENT A 01/10/2011
01/04/2011 00323411 SEDIMENT A 01/10/2011
01/04/2011 00324411 SEDIMENT A 01/10/2011
01/04/2011 00326411 SEDIMENT A 01/10/2011
01/04/2011 00325411 SEDIMENT A 01/10/2011
01/03/2011 30110 00341411 SEDIMENT A 01/10/2011
01/03/2011 00342411 SEDIMENT A 01/10/2011
01/03/2011 00343411 SEDIMENT A 01/10/2011
01/03/2011 00344411 SEDIMENT A 01/10/2011
01/03/2011 00345411 SEDIMENT A 01/10/2011
01/02/2011 00256411 SEDIMENT A 01/10/2011
01/02/2011 00257411 SEDIMENT A 01/10/2011
01/02/2011 00258411 SEDIMENT A 01/10/2011
01/02/2011 00259411 SEDIMENT A 01/10/2011
01/02/2011 00260411 SEDIMENT A 01/10/2011
01/02/2011 00261411 SEDIMENT A 01/10/2011
01/02/2011 00262411 SEDIMENT A 01/10/2011
01/02/2011 00263411 SEDIMENT A 01/10/2011
01/02/2011 00264411 SEDIMENT A 01/10/2011
01/03/2011 00265411 SEDIMENT A 01/10/2011
01/03/2011 00266411 SEDIMENT A 01/10/2011


-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote


Your posted data is ambiguous! Please use the TT TGML Tag.

FIRST: Is this wnat you intended?
[tt]
REG_ON DATE Product Lot TEST RESULT
1/3/2011 30320 301411 SEDIMENT A 1/10/2011
1/4/2011 30320 302411 SEDIMENT A 1/10/2011
1/4/2011 30320 303411 SEDIMENT A 1/10/2011
1/4/2011 30320 304411 SEDIMENT A 1/10/2011
1/4/2011 30320 305411 SEDIMENT A 1/10/2011
.....
[/tt]
SECOND: You have to be much more explicit regarding "the percentage of each value" Do you want COUNTS to be the basis for the percentage and if so based on which column values.

Please answer each of these questions.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Sorry about that. I don't know how to use the TT TGML tag.

I need to count total number of lots and break out what percent of lots are A, what percent is B, what percent is C and what percent is D. I didn't mean to add the date on the far right. I don't need it.


REG_ON DATE Product Lot TEST RESULT
1/3/2011 30320 301411 SEDIMENT A
1/4/2011 30320 302411 SEDIMENT A
1/4/2011 30320 303411 SEDIMENT A
1/4/2011 30320 304411 SEDIMENT A
1/4/2011 30320 305411 SEDIMENT A

Thank you for your patience

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote

Please!

You did not post data that has the values that support your previously stated requirement. So in the last posted example, the result for A would be 100%, correct?

Based on that assumption:
[tt]
first convert your source data table to a Structure Table using Insert > Tables > Table. The Default Table Name will be Table1.

List your RESULT values uniquely in a table on a new sheet for the chart data...
[/tt]
[tt]
RESULT PCT
A =COUNTIF(Table1[RESULT],A2)/COUNTA(Table1[RESULT])
B
C
D
[/tt]
Copy formula down as required.

Does that work so far?


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote

how to use the TT TGML tag

If you type:
[tt]
[[]tt][/tt]
REG_ON DATE Product Lot TEST RESULT
1/3/2011 30320 301411 SEDIMENT A
1/4/2011 30320 302411 SEDIMENT A
1/4/2011 30320 303411 SEDIMENT A
1/4/2011 30320 304411 SEDIMENT A
1/4/2011 30320 305411 SEDIMENT A
[tt][[]/tt][/tt]

you get:
[tt]
REG_ON DATE Product Lot TEST RESULT
1/3/2011 30320 301411 SEDIMENT A
1/4/2011 30320 302411 SEDIMENT A
1/4/2011 30320 303411 SEDIMENT A
1/4/2011 30320 304411 SEDIMENT A
1/4/2011 30320 305411 SEDIMENT A
[/tt]

Have fun.

---- Andy
 
Upvote 0 Downvote
Hi Skip. Yes the data would be, A = 100%

I did as you instructed and have the following table on a new worksheet

[tt]
Column1 Column2
AA FALSE
A FALSE
B FALSE
C TRUE
D TRUE
[/tt]

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote
I swear I did the TT TGML tag as shown. It didn't format properly

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote


I did the TT TGML tag as shown. It didn't format properly
Click to expand...
You must ALSO, DELETE the invisible TAB character between columns and replace with the appropriate number of SPACES to align properly.

PLEASE post your FORMULA exactly as it is in your sheet.


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I entered the following formulas in B2 - B6


=COUNTIF(Table1[RESULT],A2)/COUNTA(Table1[RESULT])
=COUNTIF(Table1[RESULT],A3)/COUNTA(Table1[RESULT])
=COUNTIF(Table1[RESULT],A4)/COUNTA(Table1[RESULT])
=COUNTIF(Table1[RESULT],A5)/COUNTA(Table1[RESULT])
=COUNTIF(Table1[RESULT],A6)/COUNTA(Table1[RESULT])


-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote


1) Did you convert your table to a Structured Table with the Tabel Name of Table1?

2) What VALUES are in A2:A6?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I have a tab called SEDIMENT that has a structured table called table1 containing all the data. I have a new tab called Sheet1 taht has a small structured table with two columns RESULT and Pct (VALUES are in A2:A6)(AA,A,B,C,D)

[tt]

RESULT Pct
AA FALSE
A FALSE
B FALSE
C TRUE
D TRUE
[tt]

that has the formulas in the Pct column

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote


Your True/False results are strange, indeed, as the COUNTIF() & COUNTA() functions return a NUMERIC VALUE and the formula is not an equality that could return a True/False result.

In the FORMULA BAR, select the COUNTIF() function ONLY and then hit F9 -- observe the value and then hit ESC.

What VALUE did you observe?

Do the Same for the COUNTA() function. and report back with the values for both tests.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote

If your returned True/False, your formula would be something like this...
[tt]
=COUNTIF(Table1[RESULT],A2)[red] =[/red] COUNTA(Table1[RESULT])
[/tt]


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
the values are correct when I do the test you recommend. the first part of the formula (COUNTIF) returned 17 and the second returned 3582 which is the total number of results (very cool BTW) :)

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Swi
  • Question
Excel Webservice
Replies
2
Views
311
mintjulep
M
Kennelbloke
  • Solved
Excel Unique-Filter issue
Replies
3
Views
200
SkipVought
S
renigar
  • Locked
  • Question
Having trouble with Excel Lookup
Replies
5
Views
624
renigar
renigar
Bijugate
  • Locked
  • Question
Excel update question
Replies
0
Views
530
Bijugate
Bijugate
Pirouette
  • Locked
  • Question
Excel workbook
Replies
1
Views
443
combo
combo

Part and Inventory Search

Sponsor

Back
Top