He definitely can’t lift 500 raw. Not even sure if he can squat 135. But he data mine like no other lol.
I’m doing this variation for the OH Press that is 10/8/6+ Week 1, 8/6/4+ Week 2, 5/5/5 Week 3. This way I’m hitting a higher volume at the beginning parts of the cycle since the weight will be lighter. I think since I do want to take bigger jumps this is the only way I can make it work with 5/3/1.
Dude that is awesome! The 531 progression scheme just got so much bettter for the fractional plate hater. Gonna try to whip this up too
Nobody went with Vlookup?? Lame
= MROUND (MAX (p1:p14),5)
?
This seemed like it might have worked but I couldn’t get it to. Did it work for you?
It definitely picks the largest number held in cells p1 to p14 then rounds it up/down (I choose to ignore the round up only argument lol 15.01 to 20? Psssh).
I’m not sure that’s what OP wants though
Nope that’s not quite the mark that we needed to hit.
Good idea though I’ve never used those two functions together.
An array formula should get you what you are looking for, try:
{=INDEX(P1:P15,MATCH(MIN(ABS(P1:P15-(B5D4))),ABS(P1:P15-(B5D4)),0))}
B5*D4 is 195 so the formula pulls 205 from your list in P1:P15, no need for any conversion table. Note: where the value you are looking up is equal distance from 2 values in your array it takes the larger of the 2 (so 205 vs 185 in this instance).
If you’ve never used array formulas before they are pretty simple once you get the hang of them and very useful. just remember to press CTRL+Shift+Enter to apply the formula.
You guys are making this way too complicated, I tried the array forumula and it didn’t appear to work.
Sort the list in ascending order, use vlookup. Vlookup will round down, so you can create a copy of the list and shift the values up so it always rounds up. The array function would work better for getting the closest value, not always rounding up.
formula: =VLOOKUP(B5*C4,E1:F16,2,1)
The array formula works perfectly if used correctly, and the benefit is you don’t need the conversion table. (When I copied it from excel into my post it removed the ‘*’ between b5 and d4 which may be why you couldn’t get it to work.
Also I think the objective was to round to the closest value, not up to.
Fair enough, i stand corrected. vlookup will always round.
