# Posts by davecurtis

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

Re: Join/Concatenate Text With Numbers &amp; Round To X Decimal Places

Thanks a lot, Eric,

So logical when you stand back and look at it!

Regards

Dave[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]And thanks to you as well, Andy.

Another useful tip to go with all the others

Dave

• ## 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.

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?
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

Re: Count Longest Run Of Numbers Above Mean

Thanks dll,

That's ideal. I don't quite understand how the whole formula works, but I'll investigate it.

Regards

Dave

• ## Count Longest Run Of Numbers Above Mean

Re: Count Longest Run Of Numbers Above Mean

Hi Robert,

Thanks for that, but doesn't quite do the job. It just counts all the points above a rounded mean. I really need something to count the longest "streak" one side of the mean.

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

Perfect.

Thanks Andy.

• ## 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

• ## Rank Data Array From Positive To Negative

Re: Rank Data Array From Positive To Negative

Hi Graham,

I had a couple of attempts at this, as in the attached worksheet.

The first involves plotting dummy axes and labelling them with Rob Boveys Chart Labeller.

The second involves ranking the data and transforming the rank with the trend function.

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

• ## Rank Data Array From Positive To Negative

Re: Rank Data Array From Positive To Negative

Hi,

Percentrank will give you numbers between 0 and 1. Could you explain how these relate to the numbers betwen -5 and +5?

Dave

• ## Conditional Data Point Colors For Scatter Plot Chart

Re: Conditional Data Point Colors For Scatter Plot Chart

Hi,

See the attached file. Create three new data series using IF formulas, one for each of your categories, plot them all against the x-axis data, and format each series appropriately.

Is this anything like what you wanted?

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

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

• ## 4 Axis Line Chart With Multiple Series

Re: Multiple Axis

It's probably a combination of the wrong type of chart, and your data not being laid out correctly.

Dave

• ## 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