Excel Formula Question

[bostonman]

I have a spreadsheet I created and simplified it for purposes of this question.

Attached is a screen shot and I labeled row 1 to reflect the formulas in row 2 rather than attach the spreadsheet since Excel saves my personal information in the about section.

When I change the number in E7, column E2 should change, but it doesn't. I've managed to figure out that it's not happy including column B in the formula, so I think it's getting stuck with some sort of circle calculation, however, it's not giving any errors.

Can someone tell me what I'm doing wrong and how to get around this?

[T3sl4co1l]

To show contents, prefix with an apostrophe, or change the view mode to display equations.

The cells contain an assignment expression (prefix equals sign), right?

You can also use the Formulas / Trace Precedents/Dependents to follow relations around visually.

There's also an option to evaluate the whole sheet stepwise (which permits, but may still warn of, dependency loops), which, I forget where that's done.

Tim

[Veteran68]

Worked fine for me. I changed E7 from 0 to 5 and B2 updated to 10 as expected.



I think you've got an issue with the formula in B2. I'd delete it and re-enter it as "=5+E7"

[bostonman]

I didn't remember an option existed to show formulas.

[bostonman]

Worked fine for me. I changed E7 from 0 to 5 and B2 updated to 10 as expected.

E2 should change and it's not; B2 works fine.

[ejeffrey]

Check your math, E2 doesn't depend on E7.  The B2 terms cancel.

[bostonman]

I put 'change this number' because this is a number that manually gets changed.

Currently it's 0, but this gets changed manually.

Basically this spreadsheet does some calculations, and then I can manually add a 'fudge' number which is what E7 does.

[ejeffrey]

Well, that's just not what the formulas you wrote do.  E2 doesn't depend on E7.   Just write the formula out and expand it by hand.

[bostonman]

I'm confused because B2 changes as a result of (manually) changing E7, and then E2 takes B2 divides by D2, and multiples it by C2.

[ejeffrey]

It's because C2 *also* depends on B2.

E2 = C2 * B2 / D2 
-> (substitute C2 = E5 * A2 / B2)
E2 = E5 * (A2 / B2) * B2 / D2

The B2 terms cancel.

[bostonman]

That makes more sense the way you wrote it, just seems to not make sense when I stare at Excel.

I need to figure out a better formula, but thought the formulas I had fitted the purpose of my spreadsheet. Apparently I was wrong and managed to cancel terms.

[bostonman]

Just to give some insight on this spreadsheet. Long story short, it's being used to calculate the speed of a drill press that has three pulleys.

The motor pulley is one size, the idler pulley the motor connects to is another size, and the drill pulley is another size that the idler connects to.

So I take the ratio of the motor and idler pulley and multiply that by the motor speed to get the speed of the idler pulley. Then I take the ratio of the drill pulley and idler, and multiply that by the idler speed to calculate the speed of the drill.

Unless I'm thinking of this wrong, I need to use the calculated speed of the idler to calculate the speed of the drill pulley. Obviously from what you showed I'm forcing a cancellation, but not sure how else to perform the math.

The 'fudge' number is needed for a certain purpose and why E7 is there.

[newbrain]

So it's just a cascade of three gears/pulley?

Well, your formulas are correct, and - as you noticed - the speed of the final gear does not depend on the intermediate one, only on the ratio between the first and the last.
This is also true for any number of intermediate gears.

You should have played more with Lego or Meccano gears as a kid!

[bostonman]

Well, your formulas are correct, and - as you noticed - the speed of the final gear does not depend on the intermediate one, only on the ratio between the first and the last.
This is also true for any number of intermediate gears.

Is this what I've been doing wrong this entire time? The way I've been doing it:

I take the circumference of the motor pulley and divide it by the circumference of the idler pulley, and multiply it by the motor speed (1800RPMs). That gives me the speed of the idler. Then I take the circumference of the drill pulley, divide that by the idler pulley circumference, and multiply that by the idler speed.

This is the wrong way and why I'm getting cancelations?

How is it that I don't need to include the idler speed since that changes the drill speed since it's an intermediate driver?

[ejeffrey]

If the idler pulley is a single diameter -- not itself stepped with different diameters for the motor and drill belts -- then it's size doesn't affect the output speed.  One way to look at it is that the linear speed of both belts will always be the same.

[Ian.M]

I assume this is related to your previous drill press topic: https://www.eevblog.com/forum/mechanical-engineering/calculating-total-number-of-speeds-on-drill-press/

Its all about the ratios.

For each belt, divide the pitch diameter of the driving pulley by the pitch diameter of the driven pulley to get the factor it transforms the speed by.   Multiply the factors for both belts together to get the overall factor, then multiply the motor speed by it to get the spindle speed.

N.B. it is almost certain you have measured the pulley diameters wrong.  A V belt drives on its flanks.  Unless worn out, the inside flat surface across the tip of the V doesn't even touch the pulley.   The pitch (effective) diameter is approximately where the belt reinforcement sits in the V groove.  Measure how far in from the back of the belt it is (preferably on a new belt), then subtract twice that from the diameter of the pulley at the back of the belt when its held tightly round the pulley.   You can measure that straight off the larger pulley of any pair with the belt in place (assuming you've got large enough callipers), but for the smaller  pulley you'll need to slacken it enough to hold it in to get >180° wrap.

Finding the pitch diameter with a link belt will be significantly more difficult.  I'd probably measure it with a compatible profile V belt wrapped round each pully, then adjust the fudge factor (was double the thickness between the back of the belt and the reinforcement) you subtract from the measured diameters (over the belt) which should be the same for all,  till the measured spindle speed comes out right.

[IanB]

Measuring the gear ratio of a belt drive can be done more easily using the belt itself to help with the measurement. Make a mark on the belt lined up against a point on the first pulley. Now turn the pulley through one full revolution, and make a second mark on the belt. The distance between the two marks is the circumference of the pulley. Repeat the same procedure with the second pulley. Now the gear ratio is the ratio between the two measurements.

[Ian.M]

Noted for future use!  You've just removed the need for big callipers, and improved the accuracy as well as you will be measuring the circumference as the belt sits under tension.

Also, there's no need to convert to diameters, (as long as you use *all* circumferences) as the Pi drops out when you calculate the ratios.

[bostonman]

I assume this is related to your previous drill press topic:

Yes, you're correct. This wasn't meant to deviate towards repeating the drill press thread. This is what I've done that led me up to this thread. I measured speeds on every belt combination (on both the idler and chuck pulley along with the motor) using a meter and the majority are extremely different than the tag on the press (the high speed should be 2800 and I"m getting around 3900 - I don't have the numbers with me at the moment). Now, I went ahead and measured the circumference of each pulley by tightly wrapping a string around the base of each one and then measuring the length of string on a tape measure. This is the circumference, and, from that, I could also (obviously) get the diameter if I cared to use this or have a need to use it.

Trying to find the "pitch diameter" and/or measuring error factor, I entered all the measurements into Excel and created a "fudge" number cell that will allow me to enter a number that adds a larger (or smaller) circumference and all the cells will update accordingly. This will allow me to see if I can get all the calculated numbers equal to the numbers I measured.

As an example: if one pulley is 3" circumference, and I have all the cells calculating the ratios from the motor, speeds, pulley ratios, etc... Then in my "fudge" number cell I may enter 0.3" and see if that makes the drill press speed what I measured.

What led me to post on this thread is that my Excel formula wasn't working and I thought it was an Excel formatting issue caused by me. What I learned is that one of the ratios was canceling, and it wasn't a formatting issue in Excel that I caused, so the ideal mathematical way of calculating is still eluding me.

I don't have the numbers handy, but I can post them to the original drill press thread I began and cease this thread as it's pertained to Excel - which has been resolved - and confirmed it was something I was clearly doing wrong.