Trying to calculate the total amount based on different discount factors throughout the term. For example, if a base price is
Trying to calculate the total amount based on different discount factors throughout the term. For example, if a base price is $1,000/month, that will be for months 1 – 24, then $500/month 25-48 and $250/month 49 and after. I think I can do this with IF statements but is there a better way to compute this calculation in Excel?
试着根据整个学期不同的折现因子计算总金额。例如，如果基础价格是1000美元/月，那么第1 - 24个月，然后是500美元/月25-48和250美元/月49和之后。我想我可以用IF语句来做但是有没有更好的方法用Excel计算这个计算呢?
=IF(B2<=$E$2,A2*B2,IF(AND(B2>=$D$3,B2<=$E$3),(24*A2*$F$2)+(B2-$E$2)*(A2*$F$3),IF(B2>=D4,(24*A2*F2)+(24*A2*$F$3)+(B2-48)*(A2*F4))))
1 个解决方案
#1
1
Change your table as shown below, and use the following formula:
更改您的表如下所示，并使用以下公式:
=A2*VLOOKUP(B2,$D$2:$F$4,2)+A2*(B2-VLOOKUP(B2,$D$2:$F$4,1))*VLOOKUP(B2,$D$2:$F$4,3)
There is no test done for maximum months. You can add that as an IF if necessary.
在最长的几个月里没有测试。如果有必要，你可以加上IF。
This method allows for a lot of flexibility in altering the terms. It is also useful in calculating things like income taxes and other stuff for which there is this kind of "tiering"
这种方法允许在修改术语时具有很大的灵活性。它也适用于计算像所得税和其他有这种“分层”的东西
EDIT: The formula for the Base column:
编辑:基列公式:
E2: 0
E3: =E2+(D3-D2)*F2
and fill down from E3 to the bottom of the table
从E3填充到表格底部
,000/month, that will be for months 1 – 24, then 0/month 25-48 and 0/month 49 and after. I think I can do this with IF statements but is there a better way to compute this calculation in Excel?Trying to calculate the total amount based on d
