Hi I have a nested if formula to work out an agents 'status' based on revenue which works fine, however I've been asked to reduce the targets for 2 agents as they have been with us for much less time.
My original formula looke like;
=IF(E20<75000,"None",IF(E20<100000,"Iron",IF(E20<225000,"Bronze",IF(E20<575000,"Silver",IF(E20<750000,"Gold",IF(E20<1250000,"Platinum",IF(E20<1900000,"Diamond",IF(E20<2150000,"Hall of Fame",IF(E20<2600000,"Rockstar",IF(E20>=2600000,"Club President"))))))))))
I somehow need to add in that if the sales agent is Jim or Rob then to use the reduced targets. I''ve tried various incarnations of IF AND and IF OR but getting nowhere.
The targets and stauses are;
Any help would be greatly appreciated.
Thanks
Alex
My original formula looke like;
=IF(E20<75000,"None",IF(E20<100000,"Iron",IF(E20<225000,"Bronze",IF(E20<575000,"Silver",IF(E20<750000,"Gold",IF(E20<1250000,"Platinum",IF(E20<1900000,"Diamond",IF(E20<2150000,"Hall of Fame",IF(E20<2600000,"Rockstar",IF(E20>=2600000,"Club President"))))))))))
I somehow need to add in that if the sales agent is Jim or Rob then to use the reduced targets. I''ve tried various incarnations of IF AND and IF OR but getting nowhere.
The targets and stauses are;
Any help would be greatly appreciated.
Thanks
Alex