Conditionally change a number format

[Airfix9]

Hi Mr Excel-land - I have a nutty one!

I am building a bonus template for our salesforce which shows how they are performing against their pre-agreed criteria. Essentially, it works on % of annual salary (so, if this criterion is met then they earn 5% of annual salary and so on).

I would like this to be fairly funky so that, if they enter their annual salary in cell E4, the percentages change to £ values instead (thus, showing what they have ACTUALLY earned).

In order to manage this, I have done the mathematical calculation for the formula in the cell but was wondering if there is a non-VBA method to change the actual cell format? I have been working with the following customised format:

[>1]£#,##0.00;[>0]0.00%;""

...but, if the £ value is below £1 (as it may be in certain circumstances), then this appears as a percentage instead of a monetary value. The percentage will never be over 100% as it is percentage of maximum bonus.

Any ideas, please? I am completely mystified!

 

[Fazza]

Hi,

What about if you use two cells? One for value and one for percentage. A little conditional formatting or whatever would be easy to manage - KISS.

Regards, Fazza

 

[Airfix9]

Yes, this is a solution that I had considered. Perhaps it would have been better to mention that I wish to draw a graph based on this data also and the values on the graph should also switch from £ to % accordingly...

 

[Peter_SSs]

What about a little generosity () by changing the formula so that the monetary amount is always at least 1, then your existing format should be OK.

 

[Airfix9]

Generous? My company? HAHAHAHAHAHA!!!

Looks like I need to think of a VBA workaround - I am capable of that but didn't want to go down that route. Thanks for your help anyway, guys.

 

[Peter_SSs]

Generous? My company? HAHAHAHAHAHA!!!

Oh well, I thought it was worth a try!