# Conditionally change a number format

#### Airfix9

##### Well-known Member
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:

Code:
``[>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!

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Fazza

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP, Moderator
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

##### Well-known Member
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

##### MrExcel MVP, Moderator
Generous? My company? HAHAHAHAHAHA!!!
Oh well, I thought it was worth a try!

Replies
6
Views
296
Replies
2
Views
409
Replies
2
Views
177
Replies
9
Views
82
Replies
5
Views
194

1,191,621
Messages
5,987,727
Members
440,106
Latest member
davcurnutt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back