Normally I ask about work stuff so here's something different. To approach my new year's resolution in the nerdiest way possible, I'm creating a gym program in an excel file. I'll be doing ramped sets, which means each set I increase the weight be 10%. The formula I'm using allows a base weight (e.g. last week's max) to be entered, and calculates all the previous sets, rounded, up or down, to the nearest 2.5kg (1.25kg plates on each side of a bar; the smallest increment available).
通常我会问一些工作方面的问题，所以这里有一些不同的东西。为了让我的新年决心以最书呆子的方式实现，我在excel文件中创建了一个健身房项目。我将做一个ramped集，这意味着每一个集合I增加的权重是10%我使用的公式允许输入一个基本权重(例如上周的最大值)，并计算所有之前的集合(向上或向下)，直到最近的2.5kg(每个bar上有1.25kg的盘子);可用的最小增量)。
=CEILING(($B13 - ($A$2 * 0.1)) - 1.25, 1.25 * 2)
In this formula, B13 is the next set after this one, and A2 is the base weight. So each set is 10% of the base weight less than the set after it, rounded to the nearest 2.5kg by the CEILING function.
在这个公式中，B13是这个公式后面的下一个集合，A2是底重量。因此，每一组都是基础重量的10%小于后面的一组，用上限函数四舍五入到最近的2.5kg。
Here's the kicker: the device I'll be taking with me is a Windows Phone 7 (I know, I know!). Its Excel program apparently doesn't support the CEILING function (or FLOOR). Is there a way to achieve this using the supported formulas, which according to MS Support are:
我要随身携带的设备是Windows Phone 7(我知道，我知道!)它的Excel程序显然不支持上限功能(或下限)。是否有一种方法可以使用所支持的公式来实现这一点，根据MS支持，公式如下:
ABS, ACOS, AND, ASIN, ATAN, ATAN2, AVERAGE, CHOOSE, COLUMNS, CONCATENATE, COS, COUNT, COUNTA, COUNTBLANK, COUNTIF, DATE, DATEVALUE, DAVERAGE, DAY, DCOUNT, DCOUNTA, DDB, DEGREES, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP, ERROR.TYPE, EXACT, EXP, FACT, FIND, FV, HLOOKUP, HOUR, IF, INDEX, INT, IRR, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISTEXT, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MID, MIN, MINUTE, MOD, MONTH, N, NA, NOT, NOW, NPER, NPV, OR, PI, PMT, POWER, PRODUCT, PROPER, PV, RADIANS, RAND, RANK, RATE, REPLACE, REPT, RIGHT, ROUND, ROWS, SECOND, SIN, SLN, SQRT, STDEV, STDEVP, SUBSTITUTE, SUM, SUMIF, SUMPRODUCT, SYD, T, TAN, TIME, TIMEVALUE, TODAY, TRIM, TRUNC, UPPER, VALUE, VAR, VARP, VLOOKUP, YEAR, FALSE, and TRUE.
a类，a类，a类，a类，a类，平均，选择，列，连接，COS，计数，计数，计数，计数，计数，计数，计数，计数，日期，日期，数据平均值，日，DCOUNT, DCOUNTA, dcounb, DGET, DMAX, DPRODUCT, DSTDEV, dstdevarp, ERROR。类型、具体经验,事实上,发现,阵线,HLOOKUP,小时,如果指数,INT,IRR,ISBLANK,ISERR,返回错误,ISLOGICAL,并网发电,ISNONTEXT,ISNUMBER,ISTEXT,左,LEN,LN,日志,LOG10,查找,低,匹配,马克斯,中期,分钟,分钟,国防部,月,N,NA,不是现在,期数,NPV,或者,π,PMT,力量,产品,适当,PV,弧度,兰德公司的排名,速度,替换,报告,对的,圆的,行,其次,罪恶,SLN,返回值,方差,STDEVP,替代品,和,条件求和,SUMPRODUCT,悉德,T,棕褐色,时间,时间价值,今天,修剪,TRUNC,上层,价值,VAR,VARP, VLOOKUP, YEAR, FALSE和TRUE。
And for those of you who were wondering, no, I swear I'm not as lonely as I must sound.
对于那些想知道的人，不，我发誓我并不像我想说的那样孤独。
2 个解决方案
#1
5
It should work with the ROUND function, no? Just divide your value by 2.5, round it to the nearest integer and multiply it again by 2.5
它应该是圆函数，对吧?把值除以2。5，四舍五入到最近的整数，再乘以2。5
#2
0
FLOOR =(value) - MOD((value), divisor)
CEILING =(value) - MOD((value), divisor) + divisor
Use 1 as divisor for integers. In the case of the original question, use 2.5.
用1作为整数的除数。对于原始问题，使用2.5。
=CEILING(($B13 - ($A$2 * 0.1)) - 1.25, 1.25 * 2)
becomes:
就变成:
=(($B13 - ($A$2 * 0.1)) - 1.25) - MOD(($B13 - ($A$2 * 0.1)) - 1.25, 1.25 * 2) + 1.25 * 2
#1
5
It should work with the ROUND function, no? Just divide your value by 2.5, round it to the nearest integer and multiply it again by 2.5
它应该是圆函数，对吧?把值除以2。5，四舍五入到最近的整数，再乘以2。5
#2
0
FLOOR =(value) - MOD((value), divisor)
CEILING =(value) - MOD((value), divisor) + divisor
Use 1 as divisor for integers. In the case of the original question, use 2.5.
用1作为整数的除数。对于原始问题，使用2.5。
=CEILING(($B13 - ($A$2 * 0.1)) - 1.25, 1.25 * 2)
becomes:
就变成:
=(($B13 - ($A$2 * 0.1)) - 1.25) - MOD(($B13 - ($A$2 * 0.1)) - 1.25, 1.25 * 2) + 1.25 * 2