Posts by Bubbis Thedog

    Hello, all.


    Is is possible to embed checkboxes in a chart (such that, when checked, the chart shows only certain data; eg: click the 'Florida' checkbox to display Floridas data, or click the 'Georgia' checkbox to display Georgia's data, or click both to display both series of data...), or do I have to use the control toolbox outside the chart area, and then write the code, and then drag them over the chart area?


    Thanks for any help that you can provide?


    Bubbis Thedog

    Thanks, Andy. You're right: My notion was to write a formula for the text box (which was sort of knuckleheaded of me) because, when that text box was selected, I was able to write a formula in the formula bar. I've got everything done correctly now by putting the calculation in a worksheet and then linking the data label to it.


    Thanks for taking the time to help, buddy.


    Bubbis Thedog

    Thanks so much for your response, Andy. But, intuitively, I tried clicking the data label (it is now selected and resembles a text box), typing = (it does indeed show up in the formula bar), and then writing a formula. But I get an error message, even if i just type =sum(3,4) in the formula bar. Do you know why this is happening?


    Bubbis Thedog

    Ranger, thanks for taking the time to help me. The code works perfectly, buddy, but it doesn't work when I 'Enable Macros' before entering the workbook contents. I don't think I'm understanding what you mean when you explain, "The code should be in the WorkbookOpen event (Double click the ThisWorkbook Module and Select the WorkbookOpen event.)"


    I copied the code from the Module, double-clicked 'ThisWorkbook' under Microsoft Excel Objects, then just pasted the code into the workspace. i think I'm not understanding "...Select the WorkbookOpen event" bit.


    Sorry to seem dense, buddy, I'm trying hard to get better.


    Thanks again,


    Bubbis Thedog

    Is this how I would enter the code into the module:


    PHP
    Private Sub Threshold_AlertForm()
    If Worksheets("WeeklySummaries").Range("B2").Value < Worksheets("Settings").Range("C4").Value Then Threshhold_Alert.Show
    End Sub


    Thanks so much for your help, Ranger. I almost had, I think; but I forgot the parentheses around the worksheet names. I'll get better, daggonit!


    Bubbis Thedog

    Hello, all:


    What I'm trying to do is, upon opening this workbook, write a macro that, before the user can do anything, compares two values in separate worksheets. Specifically, I'm comparing one percentage value in one cell (B2) of a 'Weekly Summaries' worksheet with one (C4) in a 'Settings' worksheet. If cell B2 is less than C4, then I've made a UserForm that I want to appear, giving the user choices of what to do next. If cell B2 is >= C4, then all is well and the macro returns nothing.


    I've tried to write the code myself, but am not quite experienced enough to get it to work. I am also having problems getting the buttons on my UserForm to perform the functions that I want them to perform.


    Any help would be greatly appreciated.


    Bubbis Thedog

    Hello, all, I think I may have an interesting question for those of you who have some statistics or consulting background. Here goes...


    Group One received 20994 incoming calls, including 1820 abandoned calls (calls not answered) (a ratio of 11.54/1).


    The abandon time (how long a customer will wait on an agent before hanging up) for Group One is 59 seconds.


    Group Two received 2648 incoming calls, including 290 abandoned calls (a ratio of 9.13/1).


    The abandon time for Group Two is 114 seconds.


    Now, Group One received 7.93 times the incoming calls as Group Two (20994/2648). I need a 'leveraged' average of abandon time between Group One and Group Two. That is, if I were to simply average 114 seconds with 59 seconds, the result would not take into account that Group One received nearly 8 times the phone calls as Group Two. For example, If Group One had 100 incoming calls with an average abandon time of 1 second, and Group Two had 10 incoming calls with and average abandon time of 20 secs, then averaging the two times would not yield an accurate result.


    Thanks for any help on this!


    Bubbis Thedog

    Hey there, Robath.


    You could rotate the X-axis names/values more toward 90 degrees (counterclockwise). This may not retain the exact size of the names/values, but should allow you more flexibility while resizing in Word.


    Hope that helps.


    Bubbis Thedog

    Here's a manual way to do it:


    Single- (or double-) click any cell outside the columns in question, and type "=COUNT(" (without the quotes) in it; highlight the entire column (either one, so long as they have equal amounts of rows); type ")" following the last character in the formula bar, then hit Enter. This will tell you how many rows you have. Now, go to the cell below the "=COUNT" cell, and single- (or double-) click it; type "=", then single-click the "=COUNT" cell above it; go to the formula bar and type "/3"; hit Enter. There's how many rows will be in three groupings of your data. Now just highlight the amount of rows as prescribed by the cell below the "=COUNT" cell, starting at the bottom of the column, then cut/paste the selection a couple of columns over. Repeat process again. Then just sort by the left-most column in each grouping (if necessary).


    Hope that helps!


    Bubbis Thedog

    Thanks for the code, Derk. I'm so new to ;) ;) VBA, it's going to take me a little time to see how you did what you did, but I'm sure I'll figure it out soon enough. May I ask how you approached that problem? I looked at it in terms of what range in Column E each Column A value would fit in, and it seems as if you've done something different maybe? Thanks in advance for any reply; if I can learn how to generally approach a problem, I think I can pick this up a little quicker.


    Bubbis Thedog


    P.S. I didn't make a reference to that Excel/Word thread because no one had replied to the original poster, and I quoted what I thought belonged on Excel/VBA. I'll do it from now on though, no matter what. ;)

    From a recent post on the Excel/Word board:


    [QUOTE]I’d like to paste into one column a set of data e.g.
    F43
    E54
    R22
    F33
    and then enter the number of duplications, e.g. 3, in to a cell which would produce a new column containing for this example,
    F43
    F43
    F43
    E54
    E54
    E54
    R22 etc.
    ...[QUOTE]


    I'm very curious about how this would be accomplished. If this is possible in VBA, could one way to do it be...


    [Let unique values exist in Column A. Let Cell C2 = i. Column E will display the results.]


    A[N] --> E([N-1]i + 1):E(Ni), where N=row position, and "-->" is defined as "is the cell whose value will be input into the range" (or “maps into the range”)


    Example:


    Let i = 7. Then


    A1 --> E1:E7
    A2 --> E8:14

    A(N) --> E([N-1]i + 1) :E(Ni)


    I’m not schooled enough at VBA to get a row position to return, much less write that program. Is it possible to approach this problem like this, or are loops (and the like) necessary?

    To add a series to a graph:


    Highlight the data (from that link you posted) in Columns A and B --> click the Chart Wizard button on the toolbar --> Select what type of graph you want to use (depends on whether you want a side-by-side comparison bar graph, or a scatter plot that displays the range that you wanted) --> Click the Series tab at the top of the dialog box --> Click Add (a second series ("Series 2") will appear in the list above [this will stand for Monday])--> Type in the Values box "=Sheet1!$D$1:D$15" and in the Category (X) Axis labels box "=Sheet1!$C$1:$C$15" ==> repeat the process beginning at Add, adding two letters after the preceding series column reference for the cell range (=Sheet1!$F$1:F$15 and =Sheet1!$E$1:E$15 for Tuesday, for example). You should have 7 Series when you're finished (provided you want a series listing sales vs. times for each day of the week). Then just click Next or Finish.


    Now, since you've input no data for the other days of the week, test it out. Input some times in column C and corresponding dollar amounts in Column D, and they should appear on the chart after you exit each cell. Just be sure to rename the series in the key as you go, according to what day of the week each one is.


    Hope that helps!


    Bubbis Thedog

    Hey, there; glad you found a solution. Actually, you can use series to display data pertaining to different days of the week on a line graph, bar graph, scatter, etc. Sunday's data would be in one series (comprised of a independent values (Xs in column A) and dependent values (Ys in column B)); Monday's data is in another (Xs in column C, Ys in column D); and so on. The good thing about using series is that all of the info that you'll need will appear on one graph.


    If you want to compare only two days of the week, then I'd think a bar graph would work best; however, using a scatter plot would would display the range that you desired. I've mostly used scatter plots for regression analysis, not side-by-side comparison.


    Hope it all works out for you!


    Bubbis Thedog

    To display the amount of sales per day of the week using a line graph (I'd use a bar graph, if I were you), you need to set up each day in the Chart Wizard as a series. After selecting the line graph (try bar) format in the Wizard, click the Series tab --> click Add (a second series will appear in the box above [this will be Monday])--> type in the Values box "=Sheet1!$D$1:D$15" and in the Category Axis labels box "=Sheet1!$C$1:$C$15" ==> repeat the process beginning at Add, adding two letters after the preceding series column reference for the cell range (=Sheet1!$F$1:F$15 and =Sheet1!$E$1:E$15 for Tuesday, for example).


    How to make the X-axis range between 00:00 and 23:59 I'm not so sure right now; but I'll keep trying to find out. I don't think I've ever tried that since I wouldn't want to have extraneous values on my x-axis anyway. Hope that all helps a little.


    Bubbis Thedog