Conditional number format?

[HRIS]

I have a cell with a vlookup formula in it for which I am wanting to change the number format if the value returned by the vlookup is less than 1. If it is less than 1, I want it to show as a percentage; if it is greater than 1, I want it to show as a flat dollar amount. Is this possible in Excel 2003? I can do it with conditional formatting in newer versions of Excel, but am not sure how to get this done in Excel 2003.

The reason I'm trying to do this is that the vlookup is pulling data from a table with flat dollar amounts or percentages. All of the percentages will be less than 100% (or less than 1) and all of the flat dollar amounts will be greater than $1.00 (or greater than 1). The vlookup is bringing over the percentages as .1 instead of the 10% showing on the table being looked up.

I appreciate any thoughts you may have!

 

[julia55]

I used an if function with a text function to do what you were requesting. The formula is below.

=IF(VLOOKUP(E2,A:B,2,FALSE)<1,TEXT(VLOOKUP(E2,A:B,2,FALSE),"0.00%"),VLOOKUP(E2,A:B,2,FALSE))

 

[HRIS]

I used an if function with a text function to do what you were requesting. The formula is below.

This worked great with the appearance, but messes with calculations based on the cell. I have some IF type calculations that if it is greater than 1, it adds it to another value; if it is less than 1, it multiplies it by that value.

Is there a way to get that to still work?

 

[mikerickson]

Try the Custom Number Format
[<1] 0.00 %;$#0.00

What I wonder is what logical structure leads to a cell being either a percentage or a dollar amount?

Is the column argument of VLookup a variable?

 

[HRIS]

Try the Custom Number Format
[<1] 0.00 %;$#0.00

What I wonder is what logical structure leads to a cell being either a percentage or a dollar amount?

Is the column argument of VLookup a variable?

The custom number format worked great! Thank you both for your assistance! I wasn't aware that you could set variable conditions in custom format.

Regarding the logic, their really isn't any good logic to the % vs. flat rate. The column in which the vlookup is looking does have variable entries - some being flat rates & some being percentages. It is a table of pay grades that indicates which pay grades are eligible for shift differentials and whether it is a flat rate or % amount. The grades with % amounts are calculated on the individual's pay, while the flat rate grades are just added to the individual's pay. It has morphed over time and there is not a completely consistent reason behind which ones are flat and which are percentage. Is that what you were meaning when you asked about the logic?

This is something we are in the process of reviewing and adjusting - but will have to live with for the time being!