formula for checking multiple values

Hi I'm new to the forum, hope I put this I the right place!

I need some help with a small excel project I'm working on and was hoping some of you could help me on my way!

The project I'm working on is for checking the measured Valve clearances on 4 cylinder motorbikes
They have 16 valves in all and I had made a spread sheet which has cells to enter the Min and Max Valve clearances for 8 exhaust and 8 Inlet valves they have different tolerances.

I have been able to do this part so I am at the point where once the valve gap min and max are entered for the exhaust and inlet valves on Cylinder 1 this changes all the min max gaps for all the other inlet and exhaust valves.

And once I have measured the gaps and entered the actual valve gap the sheet then tells me if the measured value is in or outside the valve clearance tolerance. And this is all working very well!

Now the part I'm having the trouble with.
To save money on buying new shims which is what is used to adjust the valve clearance I swap as many shims that are out of spec on the inlet side to the Exhaust side and vice verse that will bring the valve back into spec.

Once a shim is found out of spec it must be removed, it's measured and that measurement is entered into the sheet and the sheet then tells me what size shim to replace it with to bring the valve gap back to as close to the centre of the tolerance as possible.
What I won't the sheet to do that I can't work out how is once all the out of spec shims have been entered I want it to check and tell me which shims can be moved to which valves to bring them back in spec! All valves are numbered from 1 to 16 and cylinder number exhaust and inlet valves.

Can anyone help me with this

Sorry for such a long 1st post but I wanted you to understands where I and and what I am trying to do!

Cheers and thanks in advance for any help!

I will try and post one when I get home I'm at work now and don't have the file on my phone.

I will worn you I am not that great with all this programming stuff no formal training just been trial and error with a bit of web searching to find some formulas that suit my purpose so please be kind when you see how I've done what I've done as I'm sure there is a better way of doing it.

Thanks for the help!

I'm more thinking what I've done is very simple as I've said really not sure what I'm doing but so far it's working but this next function I want I just don't know where to start with it!

Ok here it is the comment boxes around Cylinder 1 show show what all the Acronyms mean and where what data needs to be entered.

I have entered some dummy valve gaps and Shim sizes on cylinder 1 Exhaust valves and cylinder 1 Inlet valves to show what is working so far.

If you look at cell C28 I have input a formula here to check that 1. that valve EC1V1 is out of Spec and 2. That the removed Shim size from cell C14 is a match for valve in Cell C29 which is the shim size needed to put valve INC1V9 back into spec.

The formula is working if any of the above checks are not met Cell28 prints None meaning no out of spec shims are the right size needed.
I want this to check all the out of spec valves and measured Removed Shims on both inlet and Exhaust valves and print the valve number which will bring the valve gap closest to the target valve Gap.
and then continue on to match all out of spec valves to replace each other where possible.

What's VBA sorry but I'm very new to all this!

Originally Posted by AnthK7

Ok here it is the comment boxes around Cylinder 1 show show what all the Acronyms mean and where what data needs to be entered.

I have entered some dummy valve gaps and Shim sizes on cylinder 1 Exhaust valves and cylinder 1 Inlet valves to show what is working so far.

If you look at cell C28 I have input a formula here to check that 1. that valve EC1V1 is out of Spec and 2. That the removed Shim size from cell C14 is a match for valve in Cell C29 which is the shim size needed to put valve INC1V9 back into spec.

The formula is working if any of the above checks are not met Cell28 prints None meaning no out of spec shims are the right size needed.
I want this to check all the out of spec valves and measured Removed Shims on both inlet and Exhaust valves and print the valve number which will bring the valve gap closest to the target valve Gap.
and then continue on to match all out of spec valves to replace each other where possible.

I've got a bit of an idea for this just want to ran it past you all!

As I have it working for one valve if I do the same thing for all the valves using the same formula but have all valve shims that come back positive for a one given valve print into a hidden group of cells which I can then create a sum that will pick the closest shim to the target shim gap and print it in the valve that needs it.
Does this sound like it could work!
Or is there a way I can use a sum to just check all the shim sizes and print the one that's closest to the target shim needed.
Also going to need a way that once a shim has been printed as a replacement for an out of spec shim that it is not selected again for another valve that it could be used for I have no idea how I could do that but going to need that!
Any ideas?

I don't throw away old shims, I keep them in case I need them the next time.

In only swap shims from valves that are out of spec anyway normally if an inlet valve is out and an exhaust valve is out I can sometime get them both back in spec buy swapping them but if the valves that are still in spec are only in buy .01 or .02mm and I have to change other shim anyway I'm better off bringing all blab gaps back to the middle of the max min range as I have to remove the cam shafts anyway so then I may as well swap any shims around to get as meany valves as I can as close to the target gap as I can! And then only buy the shim that I need.

The way I want it to work is if I enter a value for a removed shim then I'm not happy with the gap on that valve so that shim can be used to bring another valve that out back into spec.
If there's no removed shim measurement in any of the valves it means that valve is in spec and I'm happy with the gap so I haven't removed the shim from the engine to measure it.
I only have to measure the Shim if when I measure valve gap it out of spec then the shim on thy valve mast be removed and measured so I can workout what size shim needs to go back in to bring the valve gap back into spec.

I have know idea what a VBA is?

I could fill in the whole sheet in but most valves are going to be the same or only .1 - .15 difference

No that's the gap my manual states for the inlet valves they are a different gap to the exhaust valves

I want all the valves that are out of spec to check the shim size of all the other out of spec valves and print the the name of the out of spec valve like I have done on the 1st exhaust valve.
that will get the say the 1st exhaust valve gap as close to the target gap as possible and then go on doing the same for each out of spec valve.

Bsalv.

The reason for the rounding is that the shims don't come in all sizes the ones I buy only come in increments of 0.05mm that is why I rounded up so after the formula calculates the shims needed it then prints a actual shim size I can buy or that which would be in the bike!

Originally Posted by bsalv

ok, that's a practical reason.
Otherwise is the formula above correct ? (for a moment forget the rounding).
I think the + and the - must be reversed
the min thickness = actual thickness - actual clearance + min clearance
the max thickness = actual thickness - actual clearance + max clearance

What I done on the sheet I posted goes C17(this is the actual anwser before rounding) = Measured Valve Clearance - max clearance + Removed shim thickness. C8-C12+C14

C17 is then divided buy 0.05 D17 rounded up to the nearest 0.05 D18 and then * by .05 C16

the in or out of spec is worked out by a separate formula
=IF(AND(C8>=C11, C8<=F11), "IN SPEC","OUT Of SPEC")

and sorry the inlet valve spec was right but I had not done the formula for the target Gap yet.

This macro is way over my head mate and I'm having trouble trying to follow it!