×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

excell round fuction question

excell round fuction question

excell round fuction question

(OP)
I am trying to set up a spreadsheet to automatically do interpolation from a table. I think I need to somehow use roundup with mround to get the correct results. If you take a look at the attached excel spread sheet you can get a better understanding to what I am doing as it is hard to explain! Ok here we go. Cells A15 & A17 are the equations I need to fix. If you look at row 6 the increments are in .05 increments so cells A15 & A17 need to end up with the .05 increments. A15 needs to round down to the .05 increment value and A17 needs to round up to the .05 increment. Does anyone have ideas?

RE: excell round fuction question

Try doing the following:
Cell A15 =Roundown(a16/0.05,0)*.05
Cell A17 =Roundup(a16,0.05,0)*.05

When I did this, I got a result of 0.10 for Cell A15, and 0.15 for Cell A17
 

RE: excell round fuction question

I've noticed a few errors in your spreadsheet
In cell b15, change "false" to "true"
In cell b20, change the statement "A17-A16=0" to "A17-A16=B17"

RE: excell round fuction question

I made an error in my last statement
Cell B20 should look like the following:
=IF(OR(A17-A16=0,A17-A15=0,B17-B15=0),B17,B17-((A17-A16)/((A17-A15)/(B17-B15))))

RE: excell round fuction question

Additionally, I don't know if this makes a whole lot of difference, but you're doing a linear interpretation for a curve.  Looking at your data (and messing around with trendlines) your data ends up as a 4th degree polynomial.  Rounded to 2 digits gets the following equation:

=ROUND(32.484*A16^4-64.968*A16^3+32.48*A16^2+0.0039*A16-0.0019,2)

RE: excell round fuction question

(OP)
Zelgar,

Thank you for the tip it worked perfect! I am not sure what the difference is using true or false because it seems to give the same answer. Do you know the logic behind true or false?

RE: excell round fuction question

For a generalised method to do piecewise linear interpolation it is better to use the match function to find the position of the closest tabulated value below the interpolation value, then use the index function to return the value you want.  That way you don't need to change the formula if the table step value changes, and you can use it on data with unequal steps.

In cell A14 add: =+MATCH(A16,B6:R6)
Then modify as follows:
A15: =INDEX($B$6:$R$6,$A$14)
A17: =INDEX($B$6:$R$6,$A$14+1)
B15: =IF(A15=0,0,INDEX($B$7:$R$7,A14))
B17: =IF(A17=0,0,INDEX($B$7:$R$7,A14+1))

B20 can stay the same.

I have attached a copy of the spreadsheet, modified as described above.

Also the spreadsheet IP.xls at http://newtonexcelbach.wordpress.com/2008/08/10/intersections-interpolations-and-rotations/
contains functions for linear and other types of interpolation.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: excell round fuction question

i probably am missing something major; why not:
A15 = FLOOR(A16,0.05)
and
A17 = CEILING(A16,0.05)

?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources