Simple? Form array with numbers between limits.

[BeenBoy]

It seems like this should be a simple task. I would like to take some values from a table that are between and two given values, and put them into an array vector so that I can run some more calculations on those numbers. What I am doing is taking some speed versus torque output data on a vehicle in several gears, and trying to come up with the torque output through a maximum acceleration event. Anyways, if anyone wants more specifics, let me know. Thanks.

 

[Matty]

Welcome to the Board!

Could post a small example to show how your data is laid out? And tell us the values to be extracted based on the two values mentioned?

Easiest way to paste data is to ensure there are borders around the Cells, select those Cells, hit copy and then paste directly into the body of your response message.

Matty

 

[BeenBoy]

	1st gear		2nd gear	
rpm	force	speed	force	speed
1000	2.2	3.5	1.7	4.4
1250	2.3	4.3	1.8	5.5
1500	2.4	5.2	1.9	6.7

Hope this turns out legible. The data above is a small sample of what I am working with for a racing vehicle. Basically, in each gear, you have a varying force output. In an acceleration event, the vehicle switches gears at predefined shift points. Say, 3mph for 1st to second, 6mph for 2nd to 3rd gear, etc. In order to do some calculations on the overall time an acceleration event takes, I'll need a concatenated vector of the force outputs between the shifts points. This vector would look like "2.2, 1.7, 1.8" Make sense? Sorry, I may not be very effective at communicating my goal here. Thanks again.

 

[BeenBoy]

I know I could do this easily in Matlab, but another guy has already created this great spreadsheet that finds all the information that I need to do my calculations, and I would like for everything to be included in the excel spreadsheet. Please let me know if I just need to be way more descriptive, or specific, or just try to work my problem better. Thanks.

 

[Matty]

Sorry, but I'm struggling to understand how you get to the values you mention.

And do you really want the values returned concatenated, or is it just the way the formatting's come out?

Hope you can elaborate further.

Matty

 

[BeenBoy]

Alright, I'll try and explain further. I have a table of force versus speed for 6 separate gears in a vehicle. The speed ranges overlap at the shift points (where these lines would intersect on a plot of force versus speed for all 6 gears). I would like to throw out the overlapping data, keeping only the non-overlapping data. In other words, that would be the data between the shift points (intersections). Someone else has already calculated the intersection points, so I should just be able to extract the numbers sitting between these shift points, right? Concatenating everything is just a way to simplify further calculation, but I can probably figure that out. I can see that this could probably be done with some formula that populates a column with the values i'm looking for, or it could be done in some kind of array form, where all the data is stored in one cell. For an already busy spreadsheet, it seem like the single cell approach might be cleaner, as this data doesn't need to be displayed, but use in further calculation. Once again, let me know if you need more info, hopefully this was specific enough.

 

[Matty]

I think I'm starting to understand. If a 3rd gear was to be brought in, would the values to extract be as highlighted in yellow below?

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td> </td><td>1st gear</td><td> </td><td>2nd gear</td><td> </td><td>3rd gear</td><td> </td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>rpm</td><td>force</td><td>speed</td><td>force</td><td>speed</td><td>force</td><td>speed</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: right;">1000</td><td style="background-color: rgb(255, 255, 0); text-align: right;">2.2</td><td style="text-align: right;">3.5</td><td style="background-color: rgb(255, 255, 0); text-align: right;">1.7</td><td style="text-align: right;">4.4</td><td style="text-align: right;">1.2</td><td style="text-align: right;">5.3</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right;">1250</td><td style="text-align: right;">2.3</td><td style="text-align: right;">4.3</td><td style="background-color: rgb(255, 255, 0); text-align: right;">1.8</td><td style="text-align: right;">5.5</td><td style="background-color: rgb(255, 255, 0); text-align: right;">1.3</td><td style="text-align: right;">6.4</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right;">1500</td><td style="text-align: right;">2.4</td><td style="text-align: right;">5.2</td><td style="text-align: right;">1.9</td><td style="text-align: right;">6.7</td><td style="background-color: rgb(255, 255, 0); text-align: right;">1.4</td><td style="text-align: right;">7.6</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: right;">1750</td><td style="text-align: right;">2.5</td><td style="text-align: right;">6.2</td><td style="text-align: right;">2.0</td><td style="text-align: right;">8.0</td><td style="background-color: rgb(255, 255, 0); text-align: right;">1.5</td><td style="text-align: right;">8.9</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: right;">2000</td><td style="text-align: right;">2.6</td><td style="text-align: right;">7.3</td><td style="text-align: right;">2.1</td><td style="text-align: right;">9.4</td><td style="background-color: rgb(255, 255, 0); text-align: right;">1.6</td><td style="text-align: right;">10.3</td></tr></tbody></table>
Matty