How can I change the number format of a cell based on its value? Hello, I have a

Question

How can I change the number format of a cell based on its value?

Hello,

I have a workbook that our salespeople are using to create a bid. Currently it's for in-house use only, then the salespeople complete another spreadsheet with the results of the bid workbook. I want to automate the process a bit more, but I'm stuck on one thing.

For certain products which have variable costs, the salespeople have the option to mark up from cost either as a % of cost or a flat $ amount. In this case, the bid sheet that goes to the client has one spot where we can indicate how their pricing is created. I'd like to be able to format the cell with this information so that if the number is less than 1 the format is % with two decimal places. If the value is greater than or equal to 1, the format should be currency (USD). The sales department doesn't want to show two boxes (i.e., % Markup and $$ Markup) where one of them would remain blank, so I need to do it with one cell.

I can write this as a macro that the user would run, but I'd really like for it to happen automatically. I'm thinking it will need to be a sheet-level macro in that case and I have no experience with worksheet macros.

Can anyone help me with the coding to do this?   In this case, the mark up % or $ would be in cells M25:AE25.

Thank you!

Explanation / Answer

Hi,

Select M25 then drag to select M25:AE25 then

Home tab | Conditional format | New rule | use a formula to determine which cells to format and enter this formula

=M25<=1

Click the format button | Number tab and choose % and ok Out.

Repeat the above using the formula

=M25>1

and on the number tab choose your currency.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.