Thread: Help to find equation for a set of related data

Hello I have a problem to solve. below is a problem for finding an equation/formula so that a program given the equation can find the appropiate TIP size when entering the known Pump pressure in BAR and the amount of water it pumps in litre per min LPM

so given a pressure of 150Bar and rated at 8.3lmp a TIP size of 030 is required. But what about a pump of 170Bar rated at 8.5LPM. Which TIP size is nearest fit.
TIP sizes are off the shelf parts and not variable number. but important to get nearest size for pump to work at best.

as per this image showing the data.

please note the first tip size should read "20"

I hope someone can see some fairly simple solution
I want to use the formula so I can input just BAR and LPM in Excel cell and get a tip size in decimal, then I can select which is nearest tip size to reccomend.
many thanks
Jon

Raw data:
mm
SIZE 50 90 100 110 120 130 140 150 200 250 300 400 500
1.00 02 3.2 4.2 4.5 4.7 4.8 5.0 5.3 5.4 6.3 7.0 7.7 8.9 9.9
1.10 025 4.0 5.3 5.6 5.9 6.1 6.4 6.6 6.9 8.0 9.0 9.9 11.4 12.7
1.18 03 4.8 6.3 6.8 7.1 7.4 7.7 8.0 8.3 9.6 10.7 11.8 13.5 15.1
1.30 035 5.5 7.4 7.8 8.2 8.6 8.9 9.2 9.5 11.0 12.3 13.8 15.5 17.8
1.35 04 6.6 8.9 9.4 9.8 10.3 10.7 11.1 11.5 13.3 14.8 16.3 18.7 20.9
1.40 045 7.1 9.6 10.2 10.5 10.9 11.4 11.8 12.2 14.1 15.8 17.4 19.9 22.3
1.55 05 8.0 10.7 11.3 11.8 12.4 12.9 13.4 13.8 16.0 17.9 19.7 22.6 25.3
1.60 055 8.7 11.8 12.4 13.0 13.6 14.1 14.7 15.2 17.5 19.6 21.7 25.0 28.0
1.72 06 9.6 12.8 13.6 14.3 14.9 15.5 16.0 16.7 19.2 21.5 23.7 27.1 30.3
1.75 065 10.4 14.0 14.7 15.4 16.1 16.8 17.4 18.0 20.8 23.2 25.6 29.3 32.7
1.80 07 11.2 15.0 15.8 16.6 17.3 18.0 18.7 19.3 22.3 25.0 27.1 31.3 35.0
1.90 075 12.0 16.1 16.9 17.7 18.5 19.3 20.0 20.7 23.9 26.7 29.4 33.7 37.7
2.05 08 12.7 17.1 18.0 18.9 19.7 20.5 21.3 22.0 25.5 28.5 31.4 35.9 40.2
2.08 085 13.5 18.1 19.1 20.0 20.9 21.8 22.6 23.4 27.0 30.2 34.5 39.8 44.5
2.10 09 14.3 19.2 20.2 21.2 22.1 23.0 23.9 24.7 28.6 31.9 35.1 40.2 45.0
2.30 10 16.0 21.4 22.5 23.6 24.6 25.6 26.6 27.6 31.8 35.6 39.2 44.9 50.2
2.42 11 17.3 23.3 24.5 25.7 26.9 28.0 29.1 30.1 34.7 38.8 43.4 50.1 56.0
2.50 12 18.9 25.4 26.8 28.1 29.4 30.6 31.7 32.8 37.9 42.4 46.7 53.4 59.8
2.55 13 20.5 27.5 29.0 30.4 31.8 33.1 34.4 35.6 41.1 45.9 50.5 57.8 64.7
2.60 14 22.1 29.6 31.3 32.8 34.2 35.6 37.0 38.3 44.3 49.4 55.0 63.5 71.0

edit: data table got all mashed up in html

Is it better to err on too small a tip size, or too large a tip size?

Originally Posted by Ackbeet

Is it better to err on too small a tip size, or too large a tip size?

thanks for looking at this problem.

I would say its better to err on smaller size than the larger. as smaller would maximise output pressure. Like when you have a spray water bottle. if you tighten the nozzel up to tight you only get a mist, too loose and you get a lousey spray with too big water droplets.

many thanks
Jon

Here's an idea: use the pressure to decide how much "weight" to give to the two adjacent columns (we can assume you're in-between two columns, since the problem becomes a lot simpler if you're exactly on a column). Find the two adjacent flows in the two adjacent columns. If they are the same two adjacent rows for the two adjacent columns, then you're done: pick the smaller adjacent tip size (row). If they're not, then you must perform a weighted average. Let me illustrate with your example.

The pressure is 170 bar, which is between 150 and 200. The flow is 8.5 lpm. On the 150 column, the adjacent rows are 8.3 (corresponding to 1.18mm tip size) and 9.5 (corresponding to 1.3mm tip size). On the 200 column, the adjacent rows are 8.0 (corresponding to 1.1mm tip size) and 9.6 (corresponding to 1.18mm tip size). Therefore, the two adjacent rows are not the same for the two pressure columns. So you must perform a weighted average. I'm thinking something like this: for the 150 Bar column, do a linear interpolation.



This answer makes sense, because it is closer to 1.18 than to 1.3, as we'd expect, since 8.5 is closer to 8.3 than to 9.5.

Similarly, for the 200 bar column, you could do this:



So now the question is, do you go with the 1.1 tip size, or the 1.18 tip size? Well, you could now do a weighted average of these two numbers, based on the fact that 170 is closer to 150 than to 200. It's just another linear interpolation:



Again, this answer makes sense, because it's closer to the 1.125 of the 150 bar column, than the 1.2 of the 200 bar column.

Finally, while 1.17 is very close to 1.18, if you want to go smaller, then you would then choose the 1.1mm size tip.

Does all that make sense?

Thanks for that reply with formula. You know I was sure It was not an easy solution. I would need to code that formula in VBA so that ultimately just the flow rate LPM and pressure in BAR could be entered and it would give the 'best fit' tip size.
Ive struggled for ages trying to figure this out. now I have a much better understanding on how to approach this problem. is the Weighted Average and Linear interpolation as starndard approach to this type of problem. or any other ways to look at it.
some one at some stage must of calculated the Tip sizes for the manufactures product data sheet. then at some point this was approximated down to standard Tip sizes and into the datasheet table.

oh by the way I like your quote in your footer.
my god if teachers in school assumed that approach with kids today it would make things a lot better. teachers stand up in class and just read from text books and yell at the kids when they dont understand and make them feel incompetent. I did absolutly terrible at maths at school, put into a duff class with a bunch of idiots. but after i left school and went engineering college we had a fantastic maths teacher who turned up to our lecture in board shorts and scruffy tee shirt. and turned teaching maths on its head. I went from 'failure' to A+ student in 12mths. the main thing that changed was a building of beleif that I could do it as was taught in very different way and he did not make anyone feel small for not understanding. it was great to have a teacher who was a real enthuiast of math and not someone who made it feel like hiking through treakle.

Confucius also said: great teacher is teachable...

im trying to work out whats going on. )

Originally Posted by Wilmer

Confucius also said: great teacher is teachable...

Absolutely! Totally agree on that one. Just as pride and ego in the student are the biggest impediments to learning that I know of, pride and ego in the teacher is the biggest impediment to teaching that I know of. A "teacher" who can't admit he's wrong shouldn't be in the classroom.

Originally Posted by mxcjon

im trying to work out whats going on. )

You got the idea perfectly. Nice visual there.

If you're working in Excel, you could probably do some sort of lookup function to find what you need. VBA might or might not be overkill.