Bankerboy8
New Member
- Joined
- Nov 25, 2016
- Messages
- 2
Hello, I am trying to calculate a different percentage for each part of an aggregate number. I think the formula is correct, but when I test it I get some wonky outcomes. Especially when I enter numbers just over the break points.
So for instance on the number $5,100,000 the break down is
Column M Column N Column O
<tbody>
</tbody>
The formula being:
=IF($C$7<=M12,ROUND($C$7*N12,2),(M12*N12))
=IF(IF($C$7-M12<=M12,ROUND(($C$7-M12)*N13,2),(M13*N13))<0,0,IF($C$7-M12<=M12,ROUND(($C$7-M12)*N13,2),(M13*N13)))
=IF(IF($C$7-(M13+M12)<=(M13+M12),ROUND(($C$7-(M13+M12))*N14,2),(M14*M14))<0,0,IF($C$7-(M13+M12)<=(M13+M12),ROUND(($C$7-(M13+M12))*N14,2),(M14*N14)))
=IF(IF($C$7-(M14+M13+M12)<=(M14+M13+M12),ROUND(($C$7-(M14+M13+M12))*N15,2),(M15*N15))<0,0,IF($C$7-(M14+M13+M12)<=(M14+M13+M12),ROUND(($C$7-(M14+M13+M12))*N15,2),(M15*N15)))
=IF(IF($C$7-(M15+M14+M13+M12)<=(M15+M14+M13),ROUND(($C$7-(M15+M14+M13+M12))*N16,2),(M16*N16))<0,0,IF($C$7-(M15+M14+M13+M12)<=(M15+M14+M13+M12),ROUND(($C$7-(M15+M14+M13+M12))*N16,2),(M16*N16)))
=IF(ROUND(($C$7-(M15+M14+M13+M12+M16))*N17,2)<0,0,ROUND(($C$7-(M15+M14+M13+M12+M16))*N17,2))
Where $5,100,000 = cell C7
Hope that makes sense and that you are able to reconstruct what it is I am trying to convey.
Thank you
So for instance on the number $5,100,000 the break down is
Column M Column N Column O
12 First | $ 500,000 | 0.500% | $2,500 |
13 Next | $ 500,000 | 0.450% | $2,250 |
14 Next | $ 1,000,000 | 0.400% | $4,000 |
15 Next | $ 1,000,000 | 0.350% | $3,500 |
16 Next | $ 2,000,000 | 0.300% | $6,300 |
17 Next | $ 5,000,000 | 0.250% | $250 |
$18,800 |
<tbody>
</tbody>
The formula being:
=IF($C$7<=M12,ROUND($C$7*N12,2),(M12*N12))
=IF(IF($C$7-M12<=M12,ROUND(($C$7-M12)*N13,2),(M13*N13))<0,0,IF($C$7-M12<=M12,ROUND(($C$7-M12)*N13,2),(M13*N13)))
=IF(IF($C$7-(M13+M12)<=(M13+M12),ROUND(($C$7-(M13+M12))*N14,2),(M14*M14))<0,0,IF($C$7-(M13+M12)<=(M13+M12),ROUND(($C$7-(M13+M12))*N14,2),(M14*N14)))
=IF(IF($C$7-(M14+M13+M12)<=(M14+M13+M12),ROUND(($C$7-(M14+M13+M12))*N15,2),(M15*N15))<0,0,IF($C$7-(M14+M13+M12)<=(M14+M13+M12),ROUND(($C$7-(M14+M13+M12))*N15,2),(M15*N15)))
=IF(IF($C$7-(M15+M14+M13+M12)<=(M15+M14+M13),ROUND(($C$7-(M15+M14+M13+M12))*N16,2),(M16*N16))<0,0,IF($C$7-(M15+M14+M13+M12)<=(M15+M14+M13+M12),ROUND(($C$7-(M15+M14+M13+M12))*N16,2),(M16*N16)))
=IF(ROUND(($C$7-(M15+M14+M13+M12+M16))*N17,2)<0,0,ROUND(($C$7-(M15+M14+M13+M12+M16))*N17,2))
Where $5,100,000 = cell C7
Hope that makes sense and that you are able to reconstruct what it is I am trying to convey.
Thank you