Help with VBA code for a correction to a basic line graph.

Hi guys,

I need a hand with a worksheet that we use.

We do a penetration test on soil, write down the values from the machine, then enter into excel and it plots a line graph.

Depending on the values, sometimes a correction is required. This is manually done at the moment.

I would like to have excel do it for us.

I have attached an example of a manual correction I have done as well as the excel calculation worksheet.

There are 2 results given (one at 2.5mm penetration & one at 5.0mm penetration), we calculate both, then use the highest result for the report.

the x value is a constant and the y is a variable.

Thanks very much to anyone who can help.

I appreciate your time.

Michael

Copy%20of%20Master%20WA%20CBR%20Worksheet%20Soaked(2).xls
see attached. It uses a linear interpolation to predict the correction factor by using the 1.5mm and 1.0 mm fiures then adds the correction factor onto 2.5mm and 5.0mm then interpoaltes the new corrected load for that.

Is this the right methodology? if so then you can use this.

If the correction value drops below 0.5 then the formulas will have to be adjusted becasue it will use the wrong figures for the linear interpoaltion. but if it's always between 0.5 and 1 then this should work for all occurances

Thanks for your input Crooza.

The correction line needs to be drawn in a straight line directly from the most vertical part of the graph line.

The correction can vary quite a lot. Anywhere from 0.1 to a max of about 3.0.

The hardest part is going to be extrapolating the straight line. Maybe you could find the steepest section and extrapolate that to 0. Would that work?

I don't think so as it needs to hold the same angle as the most vertical line.

I think we're on the same page. the steepest is the most vertical isn't it. I think I could work it such that it finds the steepest (most vertical) and then determines the correction from that angle. Leave it with me. Might be a day or so though before I get to it.

Sounds good to me. Thanks again for your help.

Burt0

Try this. In essence it finds the steepest part of the curve and projects that to the y = 0 (ie finds where it cuts the x axis). It uses this correction factor and adds to the 2.5mm and 5mm figures. It then uses the corrected figures and a 7th order polynomial fit to your data to calculate the new 2.5* and 5.0* corrected figures.

is 7th order polynomial over the top? maybe but I figured this would ensure a close fit to the curve.

It seems to work for the data set you gave me but can you try it on a few other data sets epecially ones where the correction factor changes to a higher number. I've left all my additions in yellow highlight so it's easier to see what I did. You can hide this later if you want. Just enter your new data into the table where you normally enter it and hopefully the formulas take care of the rest.




Copy%2520of%2520Master%2520WA%2520CBR%2520Worksheet%2520Soaked(2) v2.xls

Crooza, I've entered some different values and attached the result.

Does not seem to be working correctly.

Is there anyway to project the line directly from the steepest point? In the worksheet you modified (v2) the corrected curve still kicks up a bit at the bottom.

And with the latest values that I entered, it seems to be doing the opposite.

Cheers

Michael

Burt0

The graph doesn't change (yet). It will change with your figures of course but the straight line is only a mathematical calculation at the moment, I'll look at the graph when I know the numbers are working.

Is it correctly calculating the correction and the other figures on your spreadsheet? If so them I'll try to do something with the graph.

Copy%2520of%2520Master%2520WA%2520CBR%2520Worksheet%2520Soaked(2) EXAMPLE 1.xls
Copy%2520of%2520Master%2520WA%2520CBR%2520Worksheet%2520Soaked(2) EXAMPLE 2.xls

Burt0,

Here's two files using your two examples. I've fixed the graphs so they now display your original data AND the corrected data. Can you check that this is doing what you want.

Yep, I'll have a look now and get back to you.

Cheers

Copy%2520of%2520Master%2520WA%2520CBR%2520Worksheet%2520Soaked(2) EXAMPLE 1.xls
Copy%252520of%252520Master%252520WA%252520CBR%252520Worksheet%252520Soaked(2)%20EXAMPLE%202(1).xls

I can't replicate the circular reference. Are you only entering data into cells B47 to B62 (the green shaded cells)? B62 would be the cell relvant to 7.5 which you mentioned. I've tweaked a little bit to fix the graphs mainly so try these two examples and see ifyou still get the circular reference.

Can't thank you enough for helping me out Crooza.

B72 & B73 is where the circular reference is occurring.

It occurred when I typed "10" in cell B57.

I entered the numbers 1 - 9 in cells B48 - B56 before that.

Burt0

Yes I have now been able to replicate the circular reference. It appears to occur only when the curve is perfectly linear and the correction is therefore 0. The numbers you entered were 1 to 10 at equal spacing of 1 so the line passed through 0. The way I've written the code this will generate a circular reference. From my understanding though this an artifical occurance as you would never get these types of results. Is this right? If not or if you can generate the circular reference using some other combination of numbers that aren't perfectly linear let me know. I'm hoping this is a one off for this situation only.

I'll do some more testing with it today and let you know how i go.

Cheers Crooza!

Finally had time to enter some data.

I've entered some numbers from another result and I'm getting the circular reference because no correction is required.

The numbers are not perfectly linear.

We do come across quite a few that do not require a correction.

Is it possible to change the formula to ignore the correction if its not required?

Below is the values I entered.

Please Login or Register  to view this content.

Michael

Copy%252520of%252520Master%252520WA%252520CBR%252520Worksheet%252520Soaked(2)%20EXAMPLE%201(3).xls

I've used your latest data set and amended the formulas so as not to get the circular reference. I think this will work but feel free to let me know if you get further circular references.

Are the graphs and the results being calculated correctly other than the issue with the circular reference?

Yes if you don't want the corrected line we can remove it from the graph. You're on the right track with your if statement . Instead of setting the value to 0 though I'd set it to #n/a. Zeros will often still plot in the graph and you'll get a line along your x axis but the #n/a will not plot. I can have a look at it but it might be a few days as I'm not sure I'll get a chance to look at it immediately.

Copy%20of%20Master%20WA%20CBR%20Worksheet%20Soaked(4).xls

I had a quick look and changed the corrected data so that if the correction is zero then teh corrected line graph won't graph and only the raw dara graphs but if the correction isn't zero then both lines are plotted

There's one last thing I can't quite figure out.

The result has to be rounded to a certain value depending on the range the result is within.

for results < 5.0 --> nearest 0.1
between 5 - 20 --> nearest 1
between 20 - 50 --> nearest 5
over 50 --> nearest 10

I'll keep working on it to see if i can get it.

I've attached what i have done so far.

Thanks for your help.

Copy%20of%20Master%20WA%20CBR%20Worksheet%20Soaked(5)(1).xls

If you use the mround function and a little vlookup table with the limits and the rounding amounts you could do it this way. See attached

Thanks Crooza,

Everything looks pretty good now. You've taught me quite a lot.

Cant thank you enough for the help you've given me!

Great. I'm glad it worked and you picked up some skills along the way

Not sure if you're still around Crooza,

There's something else I've been trying to get to work.

It would be handy to have the graph scale automatically by using the max value then rounding up to the nearest 5 from the data I've entered.

So the highest value could be 6.77, then the top of the y axis would be 10.

Its working but every time I enter a value in the data cells it flickers from the main worksheet to the graph, then quickly back to the main worksheet.

Is there a way to have excel only calculate max values when the whole range is full?

I've tried using the IF statement combined with MAX but its still flickering.

Any ideas would be appreciated.

Below is the code I'm using.

It's probably a bit messy.

Please Login or Register  to view this content.

Burt0,

Excel tries to recalculate cells every time you make a change. You can turn this function off. I've never done it but here's something I found to explain it.

To control when Excel calculates your worksheet, you click the Calculation Options button on the Formulas tab of the Ribbon and then click the Manual option button or press Alt+MXM. After switching to manual recalculation, when you make a change in a value, formula, or name that would usually cause Excel to recalculate the worksheet, the program displays the message “Calculate” on the status bar.

When you’re ready to have Excel recalculate the worksheet, you then click the Calculate Now (F9) command button (the one with a picture of the handheld calculator) on the Ribbon’s FORMULAS tab or press F9 or Ctrl+=. This tells the program to recalculate all dependent formulas and open charts and makes the Calculate status indicator disappear from the status bar.

Maybe try this and you won't get the flickering.

Hey Crooza,

You still around?

I'm a bit stuck. I've found a bit of a problem in the data plots for the chart and the worksheet is calculating wrong results.

Below is the data for the x axis: each value is calculated from a formula you've entered (SMALL), therefore they're variable.

Please Login or Register  to view this content.

The chart needs to plot in constant values from 0.00 - 7.50 (in increments of 0.50) then 10.00 & 12.50.

Because the y data in the column next to the values above are also variables, I can't just enter the constant data (in cells somewhere else) and plot from that.

I've attached an image of the chart where one of the problems is.

chart problem.jpg

It's a bit hard to explain the problem but I hope you understand.

I've also attached the file.

Thanks in advance for any advice.

Thanks for having a look at it.

I'll test it out when I have a bit of time later on and let you know how I go.

Cheers Crooza!

Ok,

Can we incorporate an IF function to the formula in those cells so they can change automatically depending on the circumstances?

I would give it a go, but I don't quite understand what the current formulas are actually doing.

Crooza,

I've had time to test the worksheet.

Seems to be working perfectly.

Extremely grateful for the time you've taken to help me.

I can't thank you enough for that.

Cheers Mate!

Hi Crooza,

Hopefully you're still around as I have last small issue that I'm hoping you can help me with.

It only seems to happen every now and then but it does cause problems.

The values that I have highlighted on the attached worksheet are causing issues with the graph.

The 4 values resulting from your formulas have calculated to be exactly the same results (33.53) and therefor the graph draws a horizontal line from one to the other.

The outcome is a messed up graph that affects the final test result.

Any pointers would be really helpful.

I've been trying to figure it out without bothering you but I'm running out of ideas now.

Thanks.

Sounds good,

I'll have a look when I get time.

Makes sense, because after you created the formulas last time, I had to change the worksheet to include the last 2 increments (the ones that move in 2.5).

And I just copied your formulas which were created based on 0.5 increments.

Thanks for having a look!

Yeah, my mistake.

Thank you Crooza.