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!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Bar Graph in a cell 2
- Thread starter SandorC
- Start date
- Thread starter
- #3
I have do show something like that...
Name Values Value Deviation
==================== ================ ===== -9===0===+9
<Name of someone> 1,2,3,4,... etc 111 XX|
<second name> 2,3,4,5,6... 222 |XXX
The "Deviation" is a small horizontal bar whose length depends of a calculation an could range from -9 to +9.
Maybe now is a little bit clearer...
Regards
Sandor
Name Values Value Deviation
==================== ================ ===== -9===0===+9
<Name of someone> 1,2,3,4,... etc 111 XX|
<second name> 2,3,4,5,6... 222 |XXX
The "Deviation" is a small horizontal bar whose length depends of a calculation an could range from -9 to +9.
Maybe now is a little bit clearer...
Regards
Sandor
Didn't quite understand your example, but I've done something similar before, using the REPT function.
=REPT("X",[function that returns length req'd])
will put a number of X's in the cell. You could then concatenate this with your vertical bar, and a number of X's on the other side of the bar. So, if you want, say, XXX¦XXXX, your formula will be
=REPT("X",3)&"¦"&REPT("X",4)
=REPT("X",[function that returns length req'd])
will put a number of X's in the cell. You could then concatenate this with your vertical bar, and a number of X's on the other side of the bar. So, if you want, say, XXX¦XXXX, your formula will be
=REPT("X",3)&"¦"&REPT("X",4)
-
1
- #5
DaleWatson123321
Programmer
Hi Sandor,
I've just now developed a model that uses Conditional Formatting to achieve what I expect is what you're seeking. However, I need to know specifically whether the result will be EITHER a MINUS or a POSITIVE number, OR will it "span between".
Either you can post the answer here, or you could email me the answer, or both. If you email me, I can modify and return the example file.
I hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I've just now developed a model that uses Conditional Formatting to achieve what I expect is what you're seeking. However, I need to know specifically whether the result will be EITHER a MINUS or a POSITIVE number, OR will it "span between".
Either you can post the answer here, or you could email me the answer, or both. If you email me, I can modify and return the example file.
I hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
-
1
- #6
What you need to do is:
For example if all your data is in Columns A to D,rows 2 to 10, and the data you want plot is in column D. Set Columns E & G to a width you desire (say, 30). Set column F width to the widest entry for your X axis.
Format cells E2:E10 and G2:G10 to a background color (say, Cyan). Set font color of E2:E10 to Red and G2:g10 to Dark Blue. Set the font for E2:E10 and G2:G10 to Wingdings. Set background color for F1:F10 to Black and set the Font color to White.
In cell E2 type the following formula and copy down to E10
=IF(D2<0,REPT("n",-ROUND(D2,0)),""
In cell G2 type the formula below and copy down to G10
=IF(D2>0,REPT("n",-ROUND(-D2,0)),""
HTH
Indu
For example if all your data is in Columns A to D,rows 2 to 10, and the data you want plot is in column D. Set Columns E & G to a width you desire (say, 30). Set column F width to the widest entry for your X axis.
Format cells E2:E10 and G2:G10 to a background color (say, Cyan). Set font color of E2:E10 to Red and G2:g10 to Dark Blue. Set the font for E2:E10 and G2:G10 to Wingdings. Set background color for F1:F10 to Black and set the Font color to White.
In cell E2 type the following formula and copy down to E10
=IF(D2<0,REPT("n",-ROUND(D2,0)),""
In cell G2 type the formula below and copy down to G10
=IF(D2>0,REPT("n",-ROUND(-D2,0)),""
HTH
Indu
DaleWatson123321
Programmer
Indu,
Thanks for your contribution. This "rivalry" is certainly "bringing out the best in all of us".
I checked your file out and it seems reasonable.
HOWEVER, given Sandor's example: -9===0===+9 - your model does NOT meet this requirement. i.e. your negative numbers start at the far left. For example -1 is placed in the -9 position.
Am I reading this right or wrong ?
=====================================
Sandor,
Given the following:
a) The model I developed was specifically for you.
b) It does EXACTLY as your example specifies.
c) It looks nice.
d) It will give you a GOOD example of how Contitional Formatting can be utilized, for this and OTHER situations.
e) After all my hard work. P-)
Can you please at least take a look at the file ???
Just email me, and I'll send the file via return email.
Regards, ...Dale Watson
WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca
Thanks for your contribution. This "rivalry" is certainly "bringing out the best in all of us".
I checked your file out and it seems reasonable.
HOWEVER, given Sandor's example: -9===0===+9 - your model does NOT meet this requirement. i.e. your negative numbers start at the far left. For example -1 is placed in the -9 position.
Am I reading this right or wrong ?
=====================================
Sandor,
Given the following:
a) The model I developed was specifically for you.
b) It does EXACTLY as your example specifies.
c) It looks nice.
d) It will give you a GOOD example of how Contitional Formatting can be utilized, for this and OTHER situations.
e) After all my hard work. P-)
Can you please at least take a look at the file ???
Just email me, and I'll send the file via return email.
Regards, ...Dale Watson
WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca
Hi, Dale!
I can send you my example file tomorrow, if you like.
I believe that perhaps you and I posted around the same time for this question. What happens with me is that sometimes I am between calls when I am writing my posts and takes time for me to finish and it may seem like I posted after someone else did. I do not want to foster any rivalry; as a matter of fact, most times if someone has answered a question well than I prefer staying away from it. Once in a while if I feel there may be a shorter or easier solution, then I add to the post.
Thanks.
Indu
I can send you my example file tomorrow, if you like.
I believe that perhaps you and I posted around the same time for this question. What happens with me is that sometimes I am between calls when I am writing my posts and takes time for me to finish and it may seem like I posted after someone else did. I do not want to foster any rivalry; as a matter of fact, most times if someone has answered a question well than I prefer staying away from it. Once in a while if I feel there may be a shorter or easier solution, then I add to the post.
Thanks.
Indu
DaleWatson123321
Programmer
Hi Indu,
My friend, you seem to have mistaken my honest and sincere point - that I "firmly" believe "no one" has a monopoly on having the best solution. I believe EVERYONE benefits from others "chipping in" with their own "different version".
I would suggest that Tek-Tips members NOT hold back from contributing. DIFFERENT perspectives and solutions should ALWAYS be welcome - and especially YOURS because we all have come to realize that your contributions are GREAT.
My ONLY concern here, was that I thought it unfortunate that Sandor had not opted to look at ALL the options presented - especially when my model was already created.
Yes, please email me your file, and I'll do the same.
While I did follow your instructions, perhaps your actual file will reveal something I might have missed.
Best Regards, Indu. ...Dale Watson
WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca
My friend, you seem to have mistaken my honest and sincere point - that I "firmly" believe "no one" has a monopoly on having the best solution. I believe EVERYONE benefits from others "chipping in" with their own "different version".
I would suggest that Tek-Tips members NOT hold back from contributing. DIFFERENT perspectives and solutions should ALWAYS be welcome - and especially YOURS because we all have come to realize that your contributions are GREAT.
My ONLY concern here, was that I thought it unfortunate that Sandor had not opted to look at ALL the options presented - especially when my model was already created.
Yes, please email me your file, and I'll do the same.
While I did follow your instructions, perhaps your actual file will reveal something I might have missed.
Best Regards, Indu.
...Dale WatsonWORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca
- Thread starter
- #10
Indu, Dale!
Thank you for your answer.
Dale, how can I find your file?
I solved the problem, using Indu´s solution in 2 columns (1 for negative values right aligned and other for positive values left aligned). I am using 13 characters in each column showing values from 0 to 6.5, values above are clipped. On the top, I put a figure showing the scale.
Some fonts showed well on the worksheet, but when printed were out of position but with Webdings ´g´ size=5, are OK.
I did not answered before because had to do the report.
The user liked the result.(if the user is happy, everybody is happy)
Thanks again!
Best regards
Sandor
Thank you for your answer.
Dale, how can I find your file?
I solved the problem, using Indu´s solution in 2 columns (1 for negative values right aligned and other for positive values left aligned). I am using 13 characters in each column showing values from 0 to 6.5, values above are clipped. On the top, I put a figure showing the scale.
Some fonts showed well on the worksheet, but when printed were out of position but with Webdings ´g´ size=5, are OK.
I did not answered before because had to do the report.
The user liked the result.(if the user is happy, everybody is happy)
Thanks again!
Best regards
Sandor
DaleWatson123321
Programmer
Hi Sandor,
You asked: "how can I find your file?"
The answer is: email me - at both of the addresses below, and I'll then send you the file by "return email".
I believe you should DEFINITELY see the file, because it will give you a GOOD EXAMPLE of how "Conditional Formatting" can be used.
And, in my humble opinion, you will see that it affords MORE options in terms of "formatting".
And, according to YOUR specifications, the scale goes from:
-9,-8,-7,-6,-5,-4,-2,-1,0,1,2,3,4,5,6,7,8,9.
The scale in Indu's model, if I followed his instructions correctly, goes from:
-1,-2,-3,-4,-5,-6,-7,-8,-9,0,1,2,3,4,5,6,7,8,9.
PLEASE EMAIL ME, so I can send you the file.
Regards, ...Dale Watson
WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca
You asked: "how can I find your file?"
The answer is: email me - at both of the addresses below, and I'll then send you the file by "return email".
I believe you should DEFINITELY see the file, because it will give you a GOOD EXAMPLE of how "Conditional Formatting" can be used.
And, in my humble opinion, you will see that it affords MORE options in terms of "formatting".
And, according to YOUR specifications, the scale goes from:
-9,-8,-7,-6,-5,-4,-2,-1,0,1,2,3,4,5,6,7,8,9.
The scale in Indu's model, if I followed his instructions correctly, goes from:
-1,-2,-3,-4,-5,-6,-7,-8,-9,0,1,2,3,4,5,6,7,8,9.
PLEASE EMAIL ME, so I can send you the file.
Regards, ...Dale Watson
WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca
rmikesmith
Technical User
Dale,
Thanks for sending your workbook. It's amazing what you can do with some of Excel's built-in functionality. A very nice demonstration of conditional formatting, and simple to implement!
Regards,
Mike
Thanks for sending your workbook. It's amazing what you can do with some of Excel's built-in functionality. A very nice demonstration of conditional formatting, and simple to implement!
Regards,
Mike
- Status
Similar threads
- Locked
- Question
- Locked
- Question