Posts by davecurtis

  • Join/Concatenate Text With Numbers & Round To X Decimal Places

    Hi,


    With help fro several people on this forum, I've managed to automate a lot of ous spreadsheets.
    The current one has an annoying glitch which has me stumped.


    Given that A1 = 572.326 and B1 = 0.998


    I basically need to combine some text with a couple of numbers, as:


    ="The Certified Value is "&TEXT(ROUND(A1,1),"0.0")&" ± "&TEXT(ROUNDUP(B1,1),"0.0")&" mg/100 mL"


    which comes out as I wanted with 1 decimal place as:


    The Certified Value is 572.3 ± 1.0 mg/100 mL


    I then modified it with an extra condition to force a minimum uncertainty of 0.6, as


    ="The Certified Value is "&TEXT(ROUND(A1,1),"0.0")&" ± "&MAX(TEXT(ROUNDUP(B1,1),"0.0"),0.6)&" mg/100 mL"


    The Certified Value is 572.3 ± 1 mg/100 mL


    But now I've lost the decimal place in the uncertainty figure.


    I'm obviously missing something here, but I'm stumped.


    Any suggestions please?


    Dave

  • Label Chart Elements

    Re: Label Chart Elements


    Hi Andy,


    I'm not quite sure what you mean. I'm trying to label Series 16, which doesn't have any #N/A's. what series are you referring to?
    I've just had a response from Rob, who says he doesn't get the problem, and has labelled Series 16 ok. He suggested downloading the latest version, which mine already was, but I did so anyway.
    If I click on the series in the workbook he posted back to me, I still get the same problem.


    I've attached his workbook.



    Dave

  • Label Chart Elements

    Re: Label Chart Elements


    Hi Andy,


    Rob Bovey has asked me to mail him the workbook, which I have done. I'll post back his comments when he gets back to me.
    For your info, it is a panel chart given to me by a colleague, which he wanted me to label. I attempted to label the dummy series (purple dots), and got the error message. I've used it in similar situations many times with no problem.


    I've attached a truncated version here as well.


    Regards


    Dave

  • Separate Text String With No Delimiter

    Re: Separate Text String With No Delimiter


    Hi,


    If your long number is in cell A1, enter the following formula in B1


    =MID($A$1,(COLUMN()-1)^2,2*COLUMN()-1)


    and drag across.


    This will give you the first 3 digits in B1, the next 5 digits in C1, the next 7 digits in D1 etc etc.


    Dave

  • Label Chart Elements

    Hi,


    I've used Rob Bovey's Chart Labeller a lot recently, but this morning when trying to add some labels, I get the error message


    "Unable to get the Name property of the Series class (bGetSelectionIndexes())"


    Is there a limit to the number of series it will work with?


    Dave

  • Count Longest Run Of Numbers Above Mean

    Hi,


    I'm attempting to summarise several hundred control charts.
    One thing I'd like to do is be able to put in a formula to count the maximum number of successive entries that are all the same side of the mean.
    Another related thing is to be able to count the longest run where successive values are the same.


    Could anyone suggest a couple of formulas, please?


    Thanks


    Dave

  • Duplicate Values For Area Step Chart

    Re: Creating Duplicate Values For Area Step Charts


    Thanks Andy, very clever.
    I'd spent ages trying to stop the ROW() function from returning non-integer numbers, not realising that was what I needed.
    I hadn't realised that INDEX would work like that.
    Out of interest, why does it return the value 1 three times, but the others only twice?


    Cheers,



    Dave

  • Duplicate Values For Area Step Chart

    Hi,


    I'm trying to produce step charts like Andy Pope does on his web site using the area chart method.


    http://www.andypope.info/charts/stepchart.htm


    This involves duplicating each of the values in each data series, which is tedious if there are a lot of points. Plus the fact that the area series is offset by 1 from the axis series. Can anyone think of a formula to do this?


    There are plenty of posts here about deleting duplicates, but I couldn't find much about creating duplicates. I can duplicate row numbers using


    =CEILING(ROW(),2)/2


    but thats as far as I got.


    Thanks


    Dave

  • Shape With Hollow Centre

    Re: Shape With Hollow Centre


    Hi,


    You could draw four rectangles in the outer cells of B3:D5, ie don't include C4. Make sure snap to grid is on.


    Select the four rectangles, group them, and apply a fill. Result - a rectangle with a hole.


    Dave

  • Change Default Text/Font Color

    Re: Default Text Color


    You can change it in Windows, Display Properties, Appearance, Window Text, but that affects everything, not just Excel.
    Otherwise, I think you'll need a macro.


    Dave



    Added by admin


    Ctrl+a, Change the font color, save as a Template.

  • Extend Autoshape Arrow As Data Added

    Re: Extend Autoshape Arrow As Data Added


    I wanted to do something similar a while back, but as we have a no VBA policy, I had to make do with formulas.


    Assuming the list starts in A7, then in B7, enter


    =IF(ROW()=ROW(INDEX(A:A,COUNT(A:A)+6)),"▼",IF(ISBLANK(A7),"","│"))


    and copy down as far as necessary.


    Adjust font and or row height to make it look ok.


    Unfortunately it doesn't cope with blank rows in the data.


    Example attached.


    Dave

  • Minimum Value For Spinner Dependent On Another Spinner

    Re: Minimum Value For Spinner Dependent On Another Spinner


    Thanks Andy, very neat.


    I think I overcomplicated things by trying to use spinners. I should have left it with just 2 cells for the user to enter numbers for the upper and lower thresholds, with data validation to prevent invalid numbers from being entered.


    Thanks again


    Dave