If and statements

[darrenciccone]

Hello, I will say thank you in advance for all your help.

I am working with a bonus project and I have corresponding % rates (for a certain percentage increase of unit sales; so for example if customer A bought 100 widgets last year and then this year he buys 105 he falls into the 101%-105% increase in units bought category that extra 5 units qualifies for a $ bonus based upon dollar sales of the incremntal units and I want the formula to return the appropiate % on the incremental dollar amount; so that for a range 1%-5% it Mulitplies the Incremental dollar amount by 1% and moreover if it were to change that unit volumes increased by 10% it would give back 2% on the incremental dollar purchases, and if it was 111%-115% it would be 3%.. etc.....

right now I have each % in columns and i have to manually check the % increases and it takes way too long


so right now there are 4 diffrent scales 101%-105% 106%-110%

111%-115% and 115% + so I need 4 arguments and I dont know where to begin If anyone has some insight it would be greatly appreciated and if u need more info just ask!!

Thanks again

 

[darrenciccone]

Yes, great question! that is part of my problem; see this data is from NOVEMBER so as you mention Honda's sales actually went down so they should not qualify for a bonus yet my assumption that they received the bonus is based on OCTOBER DATA when their sales volume was increased and had them qualifiying, so when I updated my data with current; NOVEMBER DATA it is still set up for assumptions based on OCTOBER and does not accurately reflect that HONDA should be disqualified for their bonus based on not meeting the + sales growth; so I am trying to get a formula that is something like

=IF(E8<C8,"NA") and then for it to also to say IF E8 is greater then or equal to C8 but less then 5%, E8*I8*E15(1%) which they do qualify for since they increase between .01% - 5% ,IF((E8/C8)>5%<10%),(E8*I8*F15) or they qualify for the next increase at a sales volume * 1.5% for increasing between 5% - 10%


I Hope this explains a littler better...

 

[darrenciccone]

bump

 

[darrenciccone]

sorry if I seem impatient! but I need to finish this project ASAP or its my ****!!!