Skip navigation

How can I set Numbers to return a specific integer in a set?

559 Views 12 Replies Latest reply: Jan 5, 2013 1:23 AM by rotarypower101 RSS
rotarypower101 Calculating status...
Currently Being Moderated
Sep 3, 2012 2:03 PM

How can I set Numbers to return a specific integer in a set?

 

I apologize in advance if I am not defining the question clearly, I will clarify as needed.

 

 

Say I am somewhere inbetween this set of numbers, and I would like to know which to choose based on a output with a decimal, how would I direct Numbers to change the output to one of the given integers in this set?

 

178, 175, 172,170,168,165,162,160,158,155,153

 

 

So say I receive a 161.01, but I want Numbers to choose the nearest lower integer in the set. 161.05 reads 160.

 

 

 

And on to a more difficult permutation of this...

I would ultimately like it to recommend the lower integer, and also make a estimation/recommendation in 20% increments (a position of 1-5) between the two closest integers. Of which some are seperated by 2,and some by three places.

 

So 161.05 would be integer 160, with a recommendation of position 3 of 5

 

160.01≤160.40 would be integer 160, with a recommendation of position 1 of 5

160.41≤160.80 would be integer 160, with a recommendation of position 2 of 5

160.81≤161.20 would be integer 160, with a recommendation of position 3 of 5

161.21≤161.60 would be integer 160, with a recommendation of position 4 of 5

161.61≤162.00 would be integer 160, with a recommendation of position 5 of 5

162.01≤162.40 would be integer 162, with a recommendation of position 1 of 5

 

Am I defining the question correctly?

Can I clarify the request with more detail or explanation?

 

I am sure there are errors, i will repair as needed also.

 

TIA!

Numbers, Mac OS X (10.7.4)
  • Yellowbox Level 4 Level 4 (3,910 points)

    Hi Rotarypower,

    I see you first posted this question in September. Maybe some reasons why you are rounding down and then postioning integers would help to stir up thoughts from this forum. I can't see your aim.

    Ian.

  • Badunit Level 6 Level 6 (10,765 points)

    This might get you started:

     

    Screen Shot 2012-12-31 at 5.55.33 AM.png

     

    Lookup column =LOOKUP(A,Table 2 :: A)

    Next Largest =LARGE(Table 2 :: A,RANK(B,Table 2 :: A)-1)

     

    From there you should be able to calculate the "recommended position".

     

    Note that my formulas do not include any errror checking for when the input number is <= the minimum or >= the maximum in your lookup table.

  • Yellowbox Level 4 Level 4 (3,910 points)

    rotarypower101 wrote: "I have also tried to find a way to push simple weather data from a web page to the spreadsheet, but I have not found a way to do this.

     

    Is it possible to do this with Numbers?

     

    Would such a question be well received here?

    I fear the complexity would be over my head."

     

    Hi rotarypower,

    If "to push simple weather data from a web page to the spreadsheet" means an automatic download, I don't know how to do that. However, if you mean to copy and paste data from a webpage, there is no complexity. You will have to clean up the data; web pages have hidden spaces and other crap that must be removed before the data will fit into a spreadsheet. Take a look at a clever solution by Barry:

     

    https://discussions.apple.com/message/20709587#20709587

     

    Ian.

  • Barry Level 7 Level 7 (29,095 points)

    Hi rp,

     

    I suspect Excel's "a little coaxing" comes from a macro or a Visual Basic routine, and that the data is 'pulled' from the webpage, not 'pushed' by the webpage.

     

    In Numbers, gathering data from a webpage can be done by an AppleScript. You can find some examples posted in this community by Yvan Koenig, and possibly by others, and may be able to determine how to apply the process to your case.

     

    Outside my range of competencies, though.

     

    Regards,

    Barry

  • Badunit Level 6 Level 6 (10,765 points)

    rotarypower101 wrote:

     

    Right now I am just adding extra estimated placeholders 150 and 180 integers, and that seems to resolve the issue, but I am assuming there is a "correct" way to do it?

     

    What results do you want for inputs less than 153 or greater than 178 since those were the outside limits of your jet sizes? What would you like for the low value and high value for those two conditions (so the next step in the algorithm can determine clip position) or what would you like the final recommendation to be for those two cases (i.e., jet size and clip position)? I assume for inputs less than 153 you would go with a 153 jet and the top clip position to make it as lean as possible. For inputs greater than 178, I think you need to add a "fake" higher jet size (such as 180, like you did) so the clip position can be calculated.

     

    Are you also going to add idle/pilot jets sizes and needle selections to this spreadsheet, assuming those are also necessary to change across your environmental conditions. I believe it can get complicated fast. I ask because it might be better to have a lookup table where you input the temp and altitude (or air/oxygen density) and it spits out the main jet, idle jet, needle, and clip position. In fact, I think a lookup table might be the better solution even for the simpler case you have proposed with just main (?) jets and clip positions.

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.