代理服务费、大小写转换公式

大小写转换、代理服务费计算公式

大小写转换：

=IF(ROUND(L12,2)=0,\\元\整

\零\零

\BNum2]\角

\\分\整\

工程代理服务费：

=IF(E3<100,E3*0.01,IF(AND(E3>=100,E3<500),((E3-100)*0.007+1),IF(AND(E3>=500,E3<1000),((E3-500)*0.0055+3.8),IF(AND(E3>=1000,E3<5000),((E3-1000)*0.0035+6.55),IF(AND(E3>=5000,E3<10000),((E3-5000)*0.002+20.55),IF(AND(E3>=10000,E3<100000),((E3-10000)*0.0005+30.55)))))))

服务代理服务费：

=IF(E3<100,E3*0.015,IF(AND(E3>=100,E3<500),((E3-100)*0.008+1.5),IF(AND(E3>=500,E3<1000),((E3-500)*0.0045+4.7),IF(AND(E3>=1000,E3<5000),((E3-1000)*0.0025+6.95),IF(AND(E3>=5000,E3<10000),((E3-5000)*0.001+16.95),IF(AND(E3>=10000,E3<100000),((E3-10000)*0.0005+21.95)))))))

物资代理服务费：

=IF($B2<=100,$B2*1.5%,IF(100<$B2,($B2-100)*1.1%+1.5,IF(500<=$B2<1000,($B2-500)*0.8%+5.9,IF(1000<=$B2<5000,($B2-1000)*0.5%+9.9,IF(5000<=$B2<10000,($B2-5000)*0.25%+29.9,IF(10000<=$B2<100000,($B2-10000)*0.05%+42.4,IF($B2>100000,($B2-100000)*0.01%+87.4)))))))