[第45期]有折扣乘车费用统计[已结]

willin2000

本期答案大家只要把公式自己写在跟帖中就可以了(不需要进行设置任何回帖方式的),各人只能看到自己的回复内容,看不到其他人的回复内容.

答题前请先点击链接阅读最新规则:正式竞赛区运行规则说明

冻豆腐

=70-SUM(TEXT(COUNTIF(OFFSET(A1,,,ROW(1:69)),">="&A2:A70-1/12),"[>9]9")*0.05)
76个字符

kkitt

=SUM(1-TEXT(COUNTIF(A1:A70,">="&A1:A70-2/24)-70-1+ROW(1:70),"[>9]9;0")*0.05)

所有的70为参数,是数组公式. 76个字符.

jyhxr

占位:71字符
=SUM(1-TEXT(ROW(A1:A70)-COUNTIF(A:A,"<"&A1:A70-0.08334)-1,"[>9]!9")*5%)

neverquest

=SUM(1-TEXT(ROW(1:70)-COUNTIF(A1:A70,"<"&A1:A70-1/12)-1,"[>9]9")*5%)
68字符！合要求了。

wjzwang

占个位先，做了个超字数的，再想想
137 =COUNT(A:A)-SUM(MMULT((ROW(1:70)+9>=TRANSPOSE(ROW(1:70)))*(A1:A70>=TRANSPOSE(A1:A70-1/12))*(A1:A70<=TRANSPOSE(A1:A70)),ROW(1:70)^0)-1)*5%
129 =COUNT(A:A)-(SUM((ROW(1:70)+9>=TRANSPOSE(ROW(1:70)))*(A1:A70>=TRANSPOSE(A1:A70-1/12))*(A1:A70<=TRANSPOSE(A1:A70)))-COUNT(A:A))*5%
121 =COUNT(A:A)*1.05-SUM((ROW(1:70)+9>=TRANSPOSE(ROW(1:70)))*(A1:A70>=TRANSPOSE(A1:A70-1/12))*(A1:A70<=TRANSPOSE(A1:A70)))*5%
105 =COUNT(A:A)-(SUM((A1:A70>=TRANSPOSE(A1:A70-1/12))*(ROW(1:70)+9>=TRANSPOSE(ROW(1:70))))-SUM(ROW(1:70)))*5%
80=COUNT(A:A)-SUM(TEXT(LOOKUP(A1:A70+1/12,A1:A70,ROW(1:70))-ROW(1:70),"[>9]9")*5%)
71=SUM(1-TEXT(LOOKUP(A1:A70+1/12,A1:A70,ROW(1:70))-ROW(1:70),"[>9]9")*5%)

fdd

先做个80个字符，看还能否简化：
=SUM(TEXT(105-COUNTIF(OFFSET(A1,,,ROW(1:70)),">="&A1:A70-2/24)*5,"[>=55]0;55")%)
79个字符：
=SUM(TEXT(105-COUNTIF(OFFSET(A1,,,ROW(1:70)),">="&A1:A70-2/24)*5,"[<55]55;0")%)
78字符：
=SUM(1-TEXT(COUNTIF(OFFSET(A1,,,ROW(1:70)),">="&A1:A70-2/24)-1,"[>9]!9;0")*5%)

lbzcc

111字符
=SUM(1-(TEXT(MMULT((A1:A70<=TRANSPOSE(A1:A70))*(A1:A70>=TRANSPOSE(A1:A70-2/24)),ROW(1:70)^0)-1,"[>=9]9"))*0.05)

mxqchina

数组公式（106字符，实在没有办法简化了，等待看答案）

=SUM(1-TEXT((COUNTIF(OFFSET(A1,,,ROW(INDIRECT("1:"&COUNT(A:A)))),">="&A1:A70-2/24)-1)*5%,"[>=.45]!0!.45"))

投机一下,87字符（但是数据增加就要必须改2个地方）

=SUM(1-TEXT((COUNTIF(OFFSET(A1,,,ROW(A1:A70)),">="&A1:A70-2/24)-1)*5%,"[>=.45]!0!.45"))

84字符：
=SUM(1-TEXT((COUNTIF(OFFSET(A1,,,ROW(A1:A70)),">="&A1:A70-2/24)-1)*5%,"[>=.45]45%"))

继续投机，83字符。

=SUM(1-TEXT((COUNTIF(OFFSET(A1,,,ROW(A1:A70)),">="&A1:A70-2/24)-1)*5%,"[>.46]45%"))

最后一次修改：77字符
=SUM(1-TEXT((COUNTIF(OFFSET(A1,,,ROW(1:70)),">="&A1:A70-2/24)-1),"[>9]9")*5%)

veobai

方法一：
=SUM(1.05-MATCH(MMULT(N(TRANSPOSE(A1:A70)>=A1:A70-1/12),ROW(1:70)^0)-70+ROW(1:70),ROW(1:10))/20)
不算数组的大括号共96个字符，请版主验收~~
方法二：
=SUM(1.05-MATCH(ROW(1:70)-MATCH(A2:A71-1/12-10^-8,A1:A71)+1,ROW(1:10))/20)
74个字符，但这个公式有点违规，因为加了一个单元格。

方法二结果是#NA. -willin2000