Rounding to closest value in a list

[HaZeR86]

Hey!

I have a little problem with my weightcalculator. I have a worksheet with 8 exercises and the weights calculated for each training day for a several months.

The calculations are made with percentages and alot of weird values made by a pro. The PROBLEM is that the weight calculations are exact while the weights available at the gym are
1) Available only in different multiplums
2) Available in multiplums that increase the heavier they get. I.e. machines. (This probably needs a list of values to round to)

The question is:
How can I make the calculations round to the values available at the gym?
(Not manually rounding; I want it to be dynamic)
- This probably requires inputting a list of the values available, but how?

Thanks in advance.

[whisperinghill]

Good article about this at http://office.microsoft.com/en-us/ex...CL100570551033

Also In Excel in the help box type "Match" or "vlookup" An example on how to use approximate closest match

[daddylonglegs]

If you always wanted to go DOWN to the next lowest weight then just list your available weights, lowest to highest in A1:A10 for instance, then with your specific weight in C2 to get the next lowest

=LOOKUP(C2,A1:A10)

If you just want the nearest it gets a little more complicated....

=INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-C2)),ABS(A1:A10-C2),0))

confirmed with CTRL+SHIFT+ENTER

[HaZeR86]

Tried the suggested:
=INDEX(A1:A10,MATCH(MIN(ABS(A1:A10-C2)),ABS(A1:A10-C2),0))

With the values in it looked like this:
=INDEX(Tall!D2:E12,MATCH(MIN(ABS('Tall'!D2:E12-'Grunnprogram RYP'!C20)),ABS(Tall!D2:E12-'Grunnprogram RYP'!C20),0))

It didn't work at all...
So i tried to swap the commas with semicolons
like this:
=INDEX(Tall!D2:E12; MATCH(MIN(ABS('Tall'!D2:E12-'Grunnprogram RYP'!C20)); ABS(Tall!D2:E12-'Grunnprogram RYP'!C20); 0))

This didn't work either.. What's wrong??

[daddylonglegs]

That formula needs to be confirmed with CTRL+SHIFT+ENTER otherwise you probably get #N/A

If you select the cell with the formula, press F2 then, while holding down CTRL+SHIFT keys, press ENTER. If done correctly curly braces like { and } will appear automatically around the formula in the formula bar....and you should get the correct result

[HaZeR86]

Yeah I get the #N/A, but that's even when CTRL+SKIFT +ENTER ing the cellformula. -And that's right: I do get the braces {}.

Something is wrong...

What i did was to make a new sheet named "Tall" and a copy sheet of "Grunnprogram RYP" (Named "Grunnprogram RYP (2)) and a made a list in "Tall" like this:
Exercise 1 - - Exercise 2 - - ...Exercise n
20 - - - - - - 30 - - - - - - - - xx
25 - - - - - - 35 - - - - - - - - xx
32,5 - - - - - 40 - - - - - - - - xx
40 - - - - - - 50 - - - - - - - - xx
50 - - - - - - 65 - - - - - - - - xx
.. You get the idea..
Then I changed the references in the formula given, changed the commas into semicolons, pasted it in the respective cells and ctrl+skift+entered.

It looked like this
{=INDEX(Tall!$D$2:$E$12; MATCH(MIN(ABS(Tall!$D$2:$E$12-'Grunnprogram RYP'!C20)); ABS(Tall!$D$2:$E$12-'Grunnprogram RYP'!C20); 0))}

The outcome: #N/A

What's wrong???

[HaZeR86]

I forgot to tell you that since I have the norwegian version of office, I had to change the formulas to the norwegian equivalent. I'm just making it clear that this isn't the case, it's something wrong with the code itself. So please help me out... anyone!

[daddylonglegs]

Aah, sorry I didn't notice you were using two columns in your formula.....my suggestion will only work with one column, can you put all values in one?

Otherwise I'm sure I can come up with a version that works for multiple columns, probably needs a longer formula though.......give me a few minutes.....

[daddylonglegs]

Actually, it's easier than I thought, try this

=MIN(IF(ABS(Tall!$D$2:$E$12-'Grunnprogram RYP'!C20)=MIN(ABS(Tall!$D$2:$E$12-'Grunnprogram RYP'!C20));Tall!$D$2:$E$12))

still confirmed with CTRL+SHIFT+ENTER

Note: if you have two values, equidistant from 'Grunnprogram RYP'!C20, e.g. if that value is 39 and the range contains both 38 and 40 then the formula returns the lower of the two, i.e. 38. If you want to return the greater of the two, i.e. 40 then change the first MIN only to MAX

[HaZeR86]

daddylonglegs: firstly, thank you for your impecable commitment! secondly, thank you again, it worked!!!