Fuel flow: Chart of 100 increments from a potential of 140 data sets - how to do it?

[Markexsel]

Modelling Carburettor Fuel Flow
The derived free area from a 'taper' in a 'hole' (atomiser) @ different 'angles & heights'

28 needles of different taper angles...... with those 'taper angles' starting at 5 different positions.

Hence 140 different areas, for any given throttle position, depending upon which needle is chosen, and which height it is set to - in relation to the fuel hole (atomiser).
_______________

The primary programming is completed
Input the % throttle opening, and the area between the needle and the atomiser is calculated for all 28 needles, at each of the 5 potential positions.

We can enter 100(%) and the spreadsheet calculates the 'fuel flow area', with the throttle wide open....... for every needle and their potential positions.
We can enter any percentage throttle opening, and see how much area is available for fuel to flow.

However....... we really need to chart the 'fuel flow area' at 1% intervals.
AND
place comparative curves (from say 5 other needles & positions), so we can see the difference.

However, that would be 100 variables for each of the 28 needles in each of their 5 positions.
...... 14000 tables/charts, as the programming is currently set.
Here is what it looks like:

The first image shows the highlighted table of 'atomiser area' when the throttle is 100% open, for each needle, at each position.
(see 'choose % throttle opening' at left)

If we had input say, 56% open, the table would show the 'atomiser area', for each needle at each position, at 56% open.

.
.
Derived PHBN Needla Data - ExcelForum.png
.
.
.
The second image shows, the 'off screen' tables.
At the point where we calculate the 'atomiser area' there are three tables.
These three tables were produced to minimise complications with nested formula.

I could nest all the formula into the 1st table:

.
.
Calculation Tables for PHBN Needla Data - ExcelForum.png
.
.
.

What is Required?

Unless there are better ideas:
I figured that there should be an area, say beneath the current tables.

This could be a table of 'check boxes'
N1 - N5 across, 28 needles down.

This would be 140 check boxes.

To the right would be an empty chart.
'x' axis across, would be 100 in single increments, indicating 100 throttle positions.
'y' axis would be 'free atomiser area'.

In simple terms:

A range of '0-100' is applied to the variable 'choose % throttle opening'.
The envisaged check boxes would indicate which data sets should be calculated, to show the curves.

The graphs would show say, 5 curves, indicating the potential fuel flow across the throttle range, for the 5 specified needles, at their chosen position.

In Effect:
We have only one input ('choose % throttle opening'), and 140 outputs.

Can we apply 100 inputs simultaneously, and choose the number of outputs to graphically display the results?

It's an obvious next step...... but I just don't know where to start.

Please Note:
This project is fundamentally 'open source', given that everybody shares their work.
To fully model a carburettor, involves linking the 'opening of the throat (air passage)', to the 'opening of the atomiser (as shown here)'.
So there is plenty of room for progress.

Further.... there are a number of different ways of skinning the carburettor cat.
The PHxx series of Dellorto carbs is just one of those ways........ but it is universally used..... so it's a good start.
.... and knowing the fuel flow area, is immediately helpful.

Primarily, I'm wanting to know if Excel can deliver the graphical representation as described.
I obviously don't mind doing the work, if somebody can give me pointers.
If somebody wants to have a go themselves, I'll upload the file to Google Drive, and provide the link.

Project Objectives

To provide the user with information that allows them to achieve ideal 'fuel burn efficiency' across the throttle range.
Ie. Reducing fuel delivery, where it's not needed, and consequently improving efficiency.

[Glenn Kennedy]

I think you need to break your request down into bite-sized chunks. One thing is ABSOLUTELY certain. No-ONE is going to retype some/any/all of your data to reproduce your staring point. That would take HOURS.

So post a representative set of data - as an Excel sheet - showing EXACTLY what you want.

[Markexsel]

Thanks Glen.

I hadn't uploaded the worksheet last night because I wanted to tidy up the explanatory notes and diagrams, that are included above each table, on Row 2.

I've also added the selection check boxes to indicate a 'potential way' of choosing which needle and notch combination to chart, and an example chart.
These I've added, primarily to assist in envisaging what I'm trying to do.
(The check boxes were copied across from the display column, so are currently linked to that column)

Operationally there are just 3 variables: % throttle opening, and two atomisers selections.

Needle data can be hidden using check boxes, or 0.
The data can be displayed by a 1, or 1,2,3 etc. to enable grouping on sort.
A named range selects all relevant data for sorting.

Hiding unwanted needle data, and grouping needles of interest, certainly helps clear the fog, from being presented with a mass of data.

The problem is that the '% throttle opening' variable, only provides a snapshot of fuel delivery for that setting.
If a method can be found for generating curves from the entire throttle range...... the visual feedback will help the user select a combination to modify fuel flow where it is needed.

I think that it's a useful project.
I've just reached the limit of my current excel knowledge, when it comes to generating the charts.
Any help from board members would be greatly appreciated.

Here is the link to the download:
https://drive.google.com/file/d/0B9H...ew?usp=sharing
Here is the screen shot of the envisaged chart section (it doesn't have to be this way)


PHBN envisaged chart operation.png

[Markexsel]

Just a minor issue, to follow on from my previous post.
I had copied the worksheet to a new file, and after uploading discovered that a 'named range' was still referring to that other file.

It was nothing much...... just the drop box referencing the atomiser options.
I deleted the old range name, and re-created it, referencing the current worksheet.

I also modified the data area, adding a row between the data and the titles, to create space for 'filter arrows'.
This makes the primary sorting much easier.

Sorting on one of the atomiser columns brings all the 'needle fuel flow' ascending.
Then using the check boxes, all the needles out of range can then be hidden.

A further sort could then be to input 1,2,3 etc. against (say) similar land lengths, though this 'sort' requires highlighting the cells, starting from the cell after the 'far right' check boxes, upwards and left.
The presence of a check box seems to prevent the start of selection highlighting.

This 'selection of cells' is not a major problem...... and if we can ultimately select the needle/notch/atomiser combinations for charting; this minor difficulty is eliminated completely.

Anyway...... the latest version with these minor mods is here (please refer to previous post re: objectives)

https://drive.google.com/file/d/0B9H...ew?usp=sharing

[Markexsel]

I'm just wondering if the solution involves using array's?

I could nest all the formulas from the 3 preliminary and final display tables.
This would produce a single table of 140 formulae.

They could be individually copied to 140 functions, each using an array of 1...... 100, as the variable.

There could be a second variable - atomiser 1 and atomiser 2.

Clicking the appropriate check box would run its associated function, to display the graph.
Clicking atomiser 2, would run the function with that variable.

Is this the right path to investigate?

Or should I just bite the bullet and create 140 tables.
Then it would be just a case of selecting which tables to graph.

Not efficient programming, but it would work.

Perhaps they could be 'non-calculating' until checked, at which point the calculation is done, and the curve is produced.
This would prevent all 140 tables from being calculated every time.

What do you think?

There are still some bridges to cross, but I could at least program the tables, and then look at controls and charting

Hmmmmmm!

[Markexsel]

Success...... at least... well on the way

1st job was to nest the formulae.
This was a minor nightmare.
It helped greatly when I discovered that IF OR statements could be written on 3 lines.

I used a text box, and incorporated the code in stages, checking the output against the relevant table that was previously programmed.

The new atomiser area display table was built, using the nested formula.
After inserting a couple of columns between each of the 5 notch columns..... each notch column would be the starting column of a table of 50 columns in 2% increments.

Therefore I have 5 tables 28 x 50.

A chart was inserted beneath notch 1 table, and the first four needles selected for curves.
It displays exactly as hoped for, selecting a different atomiser causes the curves to update instantly.

Understanding the chart below:

A pair of needles delivers more fuel at open throttle, but one needle delivers less lower down.
The other pair offers a choice for less fuel at open throttle, with a choice of more or less lower down.

Next Job:
I have to learn how to control the chart....... another mountain to climb

1st fuel flow chart.png

[Markexsel]

[B]
Next Job:
I have to learn how to control the chart....... another mountain to climb

I couldn't do it!
..... and it doesn't look like it's possible, without using VBA...... and that seems out of the question, due to the security risks for an openly available spreadsheet.

While the 'validation drop list' is intended for ensuring correct data entry..... it does offer ideal 'select' functions, and is easily programmed, so very useful when dealing with 140 select lists.
The only problem is that it cannot be sorted....... as far as I can discover.

I have tried many methods, without success...... moments of perceived victory, only dashed upon testing.
.... and there appears to be no specific searchable info on the web.

Am I the only one that wants to sort drop lists?

I have tried combo boxes, but they too appear to fall over, when sorted.
It looks like I'm pushing at the envelope, but unable to smash through.

What to do?
I'm open to suggestions.

The sadness of it all, is that, after all the effort..... everything is perfect, but for sorting.

Why sorting?

The fact is, that the mass of numbers are quite confusing to the eye.
Ideally we should be able to first sort the needles on atomiser free space at 100% throttle.

This would group together a smallish number of needles (say, in ascending order).
A 2nd sort on 'active land' would group those needles by fuel delivery at low throttle.

Boom!...... we'd have a good logical grouping.
We could then move to the chart section, and start playing with notches, and different atomisers.

But it's not the end
I'll have to proceed without sorting (unless somebody has a solution).

The program still works well.
Here it is in action.......... a lot of different combinations in play.
If you were working around your current setup....... you'd soon have it down to a couple of options.

Nice.

sort_fail.png