would it help if i attached the spreadsheet ?)
yes, remember its a public forum, so dummy data
you just need to set up a nested IF statement where you can use an IF where true or false
for example
IF( Test , True , IF( test, True , IF( test, true , false)
you can also use on true
IF(TEST , IF( Test , True , False ) , False)
If sales (c14) are less than target (c5), then zero
Sales = C14
Taget = C5
Taget * 1.5 = C6
if sales (c14) are more than the target (c5), and sales (c14) are less than or equal to target *1.5 (c6), then take 20% of the difference between target and target *1.5 (b22)
Not sure what B22 is unless its the difference between target and target*1.5
if sales are more than target *1.5 (c6), then take 20% of the difference between sales (c14) and target *1.5 (c6)
target * 1.5 = cell C6
Sales = C14
=IF(
if sales (c14) are more than the target (c5), and sales (c14) are less than or equal to target *1.5 (c6) ,
then take 20% of the difference between target and target *1.5 (b22) ,
IF ( if sales are more than target *1.5 (c6),
then take 20% of the difference between sales (c14) and target *1.5 (c6),
this is all that can be left - sales (c14) are less than target (c5),
0 ,
)))
So try an put that into a formula
= IF (
AND(C14 > C5, C14<= C6 ),
B22 or (C6-C5)*.2
IF ( C14>c6 ,
(c6-c14)*.2
0
= IF(AND(C14 > C5, C14<= C6) , B22 or (C6-C5)*.2 , IF ( C14>c6 , (c6-c14)*.2 , 0 ))