Moving 3 decimal places

[apharrington]

=IF(ISERROR(VLOOKUP($B17,'DNS SAMPLES'!$B$3:$H$42,7,FALSE)),"",VLOOKUP($B17,'DNS SAMPLES'!$B$3:$H$42,7,FALSE))

Above is the formula I'm trying to incorporate another 'IF' into. All I've been able to come up with is IF(B17="Travel",h17=DOLLAR,3) but it doesn't work...either that or I'm having placement problems in the above formula.

I can't seem to get this to work. Clearly I'm putting it in wrong. I don't want all the entries to go to 3 decimals; only the ones where "Travel" is chosen.

Please help :help :bug:

[gnod]

could you post a sample workbook

[apharrington]

Not really. I would end up having to butcher it to take out all the confidential info (the workbook itself is about 14 sheets). I only need this active cell to move to 3 decimal places if "travel" is chosen in the same row.

It is for mileage charges and the only time I need it to move to 3 dec. Whoever came up with the 1/2 cent is beyond me. :p I don't know if it's possible. It does reference another sheet as you can see and that reference does have 3 decimal places. It brings the amount forward but doesn't adjust it to 3 places.

[gnod]

is it because of your cell format that's why it doesn't adjust to 3 decimal places..

or if your cell format is correct, try tou use the Round function..

[apharrington]

??? I can do it manually, I want it to do it automatically when the word "Travel" is in "cell B", I want "cell H" of the same row to change to 3 decimal places.

Can this not be accomplished?

[ammx]

Hi,

while waiting for an answer to my own problem, I may try to solve yours :friends:

I think there are two solutions:

1. I assume your long formula is in cell H17 and you want to show as DOLLAR format with 3 digits when B17 is 'Travel', otherwise show h17 without converting it.

IF($B17="Travel",DOLLAR(h17,3),h17)

This assumes that H17 is not showing the empty string value (""), thus you have to adjust your IF..statement in cell H17 to return the number 0 instead of "".

=IF(ISERROR(VLOOKUP($B17,'DNS SAMPLES'!$B$3:$H$42,7,FALSE)),0,VLOOKUP($B17,'DNS SAMPLES'!$B$3:$H$42,7,FALSE))


2. I case you don't want to use the temp value in column H:H , you may try to do it all in one go (hope the () are O.K.):

=IF(ISERROR(VLOOKUP($B17,'DNS SAMPLES'!$B$3:$H$42,7,FALSE)),"",IF($B17='Travel',DOLLAR(VLOOKUP($B17,'DNS SAMPLES'!$B$3:$H$42,7,FALSE),3) ,VLOOKUP($B17,'DNS SAMPLES'!$B$3:$H$42,7,FALSE))


However, I prefer to use helper columns. Just makes it also easier to maintain in the long run...

Michael

[apharrington]

:ipray:

IT WORKS!! THANK YOU!! My next question is; how do change the format that =DOLLAR is displaying? It's entirely different than what I'm using ie, I'm diplaying an arial font at 0.40 for example, this is displaying a courier font and $0.445

Not to be picky, I'll use it as it is, again THANK YOU! I've never run into a font problem before, the formula must be dominating this?

[ammx]

Hi,

the formula does not set any formatting, it just returns a text string that converts the input value into a currency look-a-like text string.

So simply change the format of column H to anything you like.
However, keep in mind that it is a text value. So changing to a numeric format, would not make any difference (because it is text!).

Alternatively you can also use the TEXT(<value>,<format>) function. It is more flexible, as you can use different kind of formats. It is also less picky when the value is not a number.

The DOLLAR (<value>, 3) function can be replaced by:

=TEXT(<value>,"_-* #,##0.000 $_-;-* #,##0.000 $_-;_-* """"-""""??? $_-;_-@_-")

To easily get to the weird format string, I use a simple trick:

Format the cells using the FORMAT -> CELLS -> NUMBER menu.
Select e.g. CURRENCY category with 3 digits
Now select the CUSTOM category and copy the string to the clipboard
Write the =TEXT() formula into a cell
Paste the string from CUSTOM category to the <format> parameter
In case there is a double quote in the string, replace it with four double quotes!More string formats can be found in the EXCEL - HELP , then search for:

'custom number format codes'

Regards,

Michael

[apharrington]

Thank you, Michael:

I think I'm just looking at this thing too long. :bug: