ignitebenhaigh
New Member
- Joined
- Apr 4, 2013
- Messages
- 17
Hi, Please help.
I need a (IF or similar) formula that's capable of doing the following.
If "value" = or is > "tier 1 value" calculate the difference between "value" & "tier 1 value" and multiply by "tier 1 %" but if "value" = or is > "tier 2 value" override & calculate the difference between "value" & "tier 1 value" and multiply by "tier 2 %" but if "value" = or is > "tier 3 value" calculate the difference between "value" & "tier 1 value" and multiply by "tier 3 %" bit if "value" = or is > "tier 4 value" calculate the difference between "value" & "tier 1 value" and multiply by "tier 4 %".
Problem i'm having is that i've tried to use:
=IFERROR(IF(C16<$B$6,"0",IF(C16>=$B$6,(C16-$B$6)*$C$6+$D$6,IF(C16>=$B$7,(C16-$B$6)*$C$7+$D$6,IF(C16>=$B$8,(C16-$B$6)*$C$8+$D$6,IF(C16>=$B$9,(C16-$B$6)*$C$9+$D$6,""))))),0)
but it only ever calculates the difference at the tier 1 %
Hopefully someone out their has a solution. Thanks in advance
I need a (IF or similar) formula that's capable of doing the following.
If "value" = or is > "tier 1 value" calculate the difference between "value" & "tier 1 value" and multiply by "tier 1 %" but if "value" = or is > "tier 2 value" override & calculate the difference between "value" & "tier 1 value" and multiply by "tier 2 %" but if "value" = or is > "tier 3 value" calculate the difference between "value" & "tier 1 value" and multiply by "tier 3 %" bit if "value" = or is > "tier 4 value" calculate the difference between "value" & "tier 1 value" and multiply by "tier 4 %".
Problem i'm having is that i've tried to use:
=IFERROR(IF(C16<$B$6,"0",IF(C16>=$B$6,(C16-$B$6)*$C$6+$D$6,IF(C16>=$B$7,(C16-$B$6)*$C$7+$D$6,IF(C16>=$B$8,(C16-$B$6)*$C$8+$D$6,IF(C16>=$B$9,(C16-$B$6)*$C$9+$D$6,""))))),0)
but it only ever calculates the difference at the tier 1 %
Hopefully someone out their has a solution. Thanks in advance