I’ve searched all the tech forums and I can’t seem to find the answer to this.
Are any of you excel experts/masters? So this is my issue:
In Cell F4 I have a value of 195. The formula in F4 is =CEILING(B5*D4, 5). The formula is taking 300 and taking 65% of it which is 195. So with the way I currently have it setup it’s multiplying b5 by d4 and its rounding up to the closest 5.
I have a list of numbers in P1-P14 in descending order. The numbers are 405, 385, 365, 335, 315, 295, 275, 245, 225, 205, 185, 155, 135, 115, 95.
What I need to be able to do is instead of F4 rounding up to the closest 5, I need it to round to the closet number in this list.
What formula can I write to make this happen?
If you want to know what I’m doing, I am creating my 5/3/1 spreadsheet. I have it fully functional. But the thing is I only want to use 45’s, 25’s, 10’s when training. So instead of using the exact number how it currently displayed, I want it to round up the closest number (up or down) to the list. So for example my particular says 195. It would round up to 205 or round down to 185 depending on what it actually rounds to.
Let me know if I need to explain this a bit better. Thanks!
this formula rounds to 5s
=MROUND((equation),5)
The issue I had when rounding to 10s was that it kept giving me numbers which ended in 0. I’m sure there is a way around this, I just haven’t played around with it enough.
1 Like
Yeah see I need something like:
=CEILING(b5*d4, round to the closet number on my list on P1:P15)
First you need to make a conversion table. I put mine in Columns I and J. In my example I assumed that you’d jump up to the next plate once you’re past the previous. So 195 goes to 205. If you want the closest instead of jumping up, just adjust your conversion value in column J. Maybe 190-195 maps back to 185, not 205. Your choice.
From there it is a simple lookup off of your formula in f4. =vlookup(F4,I:J,2,FALSE)
1 Like
Here’s another shot of the conversion columns. You can complete column I very quickly by entering 0, 5, 10 in the first three cells, then drag-copy it all the way down to 405. Then just plug whatever values you want to map to in column J.
Here’s another pic of the conversion table.
1 Like
Just to give you guys a better view of what I am asking.
Just park your conversion table on another worksheet or way off to the side and it will work fine.
1 Like
You’ll also want to map values up to at lest six plates since you’re running 5/3/1 and sure to get strong as fuck very soon.
2 Likes
@twojarslave thanks for the assistance!
So I’m still confused a bit. How do I get my value in F4 reflect the conversion table? I just went through and used Q,R as my conversion tables.
1 Like
F4 is unchanged. Same formula you gave me multiplying your training max by your work set percentage.
The lookup I put in G5 works off of that value.
1 Like
I see what you’re saying now that I looked at your sheet. Just park that somewhere else, or hide the column and park the lookup next to it.
Make sense?
1 Like
Another way is to imbed the ceiling function in the lookup. Like this.
This will let you preserve the sheets current layout
1 Like
Thank you so much man!!!
That conversion table did exactly what I needed it to do. I had to create the second layout at the bottom in order for it to work how I envisioned it. This will be god send. Thank you so much man, cheers to you brotha!
2 Likes
Lazy asshole, learn how to code.
4 Likes
I wish man lol. I know one of my employees said he could script this in SQL for me but that was overkill for what I needed. This will work just fine though!!
1 Like
Lol, I fucking hate SQL. Excel FTW!
1 Like
I miss @aero51. I wonder what his C++ solution would be. Better than mine for sure!
3 Likes
He wouldn’t have a solution and it would be capitalism’s fault.
2 Likes
You’re wise to come to T-Nation for this. I doubt your sql guy can squat 500 raw. Probably doesn’t even lift.
I guess this makes things convenient for squat and deadlift, but I’d really rethink this for overhead press (and sorry if you weren’t going to do this for everything). Some of those weeks look strange. Slick Exceling everyone though, that’s pretty cool.
