excell round fuction question

[eit09]

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?

 

[zelgar]

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

 

[zelgar]

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"

 

[zelgar]

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))))

 

[zelgar]

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)

 

[eit09]

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?

 

[IDS]

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/

 

[youngEIT]

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

?

 

[yakpol]

If you have space in the spreadsheet use linear interpolation between the table values. Attached is your spreadsheet with the needed formulas.