Each .5 increases 5% but <.5 doesn't increase anything.
Gaurav wrote:
Thanks Joe for the response. But it doesnt seem to working as I want it to.
The daily sale is in C4 and here is my "previous" complete formula.
=IF(C5<11,0,IF(AND(C5=11,C5<12),0.4,IF(AND(C5=12 ,C5<13),0.6,IF(AND(C5=13,C5<14),0.8,IF(AND(C5=14 ,C5<14.5),1,IF(C5=14.5,(1+(C5-14)*0.1),""))))))
Can be simplified to this:
=IF(C5<11,0,IF(C5<12,0.4,IF(C5<13,0.6,IF(C5<14,0.8 ,IF(C5<14.5,1,1+(C5-14)*0.1)))))
When the first IF() fails, it confirms that C5=11, so no need to check that
again. Same goes for the rest, eliminating the need for AND().
which I changed to the following after your suggestion.
=IF(C4<11,0,IF(AND(C4=11,C4<12),0.4,IF(AND(C4=12 ,C4<13),0.6,IF(AND(C4=13,C4<14),0.8,IF(AND(C4=14 ,C4<14.5),1,IF(C4=14.5,(1+INT((C4-14)/0.05)*0.05),""))))))
The previous one was increasing 1% with every .1 increase in the sale value.
and the one you suggested is increasing 10% with each .1 increase in the
sale value.
What I actually want is. a 5% increase with each .5 increase. so if the
increase in the sale value is .6 to .9, it would still increase 5%.
I think this will work for you:
=IF(C5<11,0,IF(C5<14.5,(INT(C5)-10)*0.2+0.2,(ROUNDDOWN(C5*2,0)/2-14)/10+1))
Thanks again for your help with this.
|