Formatting formula results as Number

[Lacey D]

I am using the following formula

=TEXT(VLOOKUP([@[Frequency Of Use]],FOU[#All],2,FALSE)&"."&VLOOKUP([@Type],Types[#All],2,FALSE)&"."&VLOOKUP([@Category],Categories[#All],2,FALSE),0)

to get a Dewey decimal system type result

1.2.1

I understand this isn't registering as a number because of the "." but is there any other way to get the decimal type of result that will register as a number?

 

[coulombevin]

Hi Lacey,

If you want to see it as Dewey decimal but store it as a number, you should use the following formula in the cells:

=VALUE(TEXT(VLOOKUP([@[Frequency Of Use]],FOU[#All],2,FALSE)&VLOOKUP([@Type],Types[#All],2,FALSE)&VLOOKUP([@Category],Categories[#All],2,FALSE),0))

In the header, you need to go in Number > Format > Personalised and write : 0.0.0

Hope it help,

Vincent

 

[Lacey D]

In the header, you need to go in Number > Format > Personalised and write : 0.0.0

I'm not sure what this means. I changed the number format to custom for these cells and it still gives the #value error message with the value added to the formula

 

[coulombevin]

Hi Lacey,

Do you have an example of your date, also the personalised format does not change the value of the cell, it only change the apearance of the value. With the format 0.0.0, the input 123 will have the value 123 but you'll see it as 1.2.3

The #value error probably mean that one of your vlookup doesn't find something or return some text that value() try to convert as number.

 

[kvsrinivasamurthy]

Try.

=TEXT(VLOOKUP([@[Frequency Of Use]],FOU[#All],2,FALSE)&"."&VLOOKUP([@Type],Types[#All],2,FALSE)&"."&VLOOKUP([@Category],Categories[#All],2,FALSE),0)

This may give result as 1.21 INSTEAD OF 1.2.1