Dear experts,
I have a table which looks up values from a database; this table is linked
to a cell where I can select what I want to see in the table.
Now, sometimes the values I want to see need to be in a 2 decimal format
(ex. 2.43) and sometimes they need to have no decimals at all (ex. 25000). It
really all depends on their magnitude - when <10 then I need to have 2
decimals, if > 10 then no decimal is needed.
Is there a way to do this in Excel 2003?
Many thanks for your help!
Kind regards
--
Valeria

Suppose your number is in A1. Try this in B1:

=TEXT(A1,IF(A1>10,"0",0.00"))

This will return a text value, and it will round the number, so if
your number is 10.6 it will show 11.

If you don't want this, then here's an alternative:

=IF(A1>10,INT(A1),A1)

Format the cell as General. This time 10.6 will show as 10, and 9.95
will show as 9.95.

If you want this to happen to your cell which contains the VLOOKUP
formula, then just substitute your formula for A1 in the above.

Hope this helps.

Pete



On Sep 11, 10:29*am, Valeria <Vale...@discussions.microsoft.com>
wrote:
> Dear experts,
> I have a table which looks up values from a database; this table is linked
> to a cell where I can select what I want to see in the table.
> Now, sometimes the values I want to see need to be in a 2 decimal format
> (ex. 2.43) and sometimes they need to have no decimals at all (ex. 25000).. It
> really all depends on their magnitude - when <10 then I need to have 2
> decimals, if > 10 then no decimal is needed.
> Is there a way to do this in Excel 2003?
> Many thanks for your help!
> Kind regards
> --
> Valeria

Format Cell/ Number/ Custom
[<10]0.00; 0

You talked about <10 and >10, but I guessed that you meant >=10.
--
David Biddulph

"Valeria" <(E-Mail Removed)> wrote in message
news:0EE3BF8F-C90E-4957-8DBA-(E-Mail Removed)...
> Dear experts,
> I have a table which looks up values from a database; this table is linked
> to a cell where I can select what I want to see in the table.
> Now, sometimes the values I want to see need to be in a 2 decimal format
> (ex. 2.43) and sometimes they need to have no decimals at all (ex. 25000).
> It
> really all depends on their magnitude - when <10 then I need to have 2
> decimals, if > 10 then no decimal is needed.
> Is there a way to do this in Excel 2003?
> Many thanks for your help!
> Kind regards
> --
> Valeria

Try this formula

=IF(A1="","",IF(A1=T(A1),"",IF(A1<10,TEXT(A1,"#.##"),TEXT(A1,"#######"))))

Type your value in a1 cell. for applying the same formula to your cell
change the reference a1 to your cell.

If this post helps, Click yes...

--------------------
(MS-Exl-Learner)
--------------------



"Valeria" wrote:

> Dear experts,
> I have a table which looks up values from a database; this table is linked
> to a cell where I can select what I want to see in the table.
> Now, sometimes the values I want to see need to be in a 2 decimal format
> (ex. 2.43) and sometimes they need to have no decimals at all (ex. 25000). It
> really all depends on their magnitude - when <10 then I need to have 2
> decimals, if > 10 then no decimal is needed.
> Is there a way to do this in Excel 2003?
> Many thanks for your help!
> Kind regards
> --
> Valeria

You may want to think a bit further.
As -20 is less than 10, do you want that to show 2 decimals?
If what you intended was 2 decimals for numbers > -10 and < 10, you may want
[<=-10]-0;[<10]0.00; 0
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
news:4aaa1e6f$(E-Mail Removed)...
> Format Cell/ Number/ Custom
> [<10]0.00; 0
>
> You talked about <10 and >10, but I guessed that you meant >=10.
> --
> David Biddulph
>
> "Valeria" <(E-Mail Removed)> wrote in message
> news:0EE3BF8F-C90E-4957-8DBA-(E-Mail Removed)...
>> Dear experts,
>> I have a table which looks up values from a database; this table is
>> linked
>> to a cell where I can select what I want to see in the table.
>> Now, sometimes the values I want to see need to be in a 2 decimal format
>> (ex. 2.43) and sometimes they need to have no decimals at all (ex.
>> 25000). It
>> really all depends on their magnitude - when <10 then I need to have 2
>> decimals, if > 10 then no decimal is needed.
>> Is there a way to do this in Excel 2003?
>> Many thanks for your help!
>> Kind regards
>> --
>> Valeria
>
>

This works really great and is really easy to do!!! Many thanks.
Kind regards
--
Valeria


"David Biddulph" wrote:

> Format Cell/ Number/ Custom
> [<10]0.00; 0
>
> You talked about <10 and >10, but I guessed that you meant >=10.
> --
> David Biddulph
>
> "Valeria" <(E-Mail Removed)> wrote in message
> news:0EE3BF8F-C90E-4957-8DBA-(E-Mail Removed)...
> > Dear experts,
> > I have a table which looks up values from a database; this table is linked
> > to a cell where I can select what I want to see in the table.
> > Now, sometimes the values I want to see need to be in a 2 decimal format
> > (ex. 2.43) and sometimes they need to have no decimals at all (ex. 25000).
> > It
> > really all depends on their magnitude - when <10 then I need to have 2
> > decimals, if > 10 then no decimal is needed.
> > Is there a way to do this in Excel 2003?
> > Many thanks for your help!
> > Kind regards
> > --
> > Valeria
>
>
>

This is a great help to me. However I tried to modify it:

[<=-10]-0;[<10]0.00; [<100]0.0; 0

And Excel won't accept the number format. Did I do something wrong?


"David Biddulph" wrote:

> You may want to think a bit further.
> As -20 is less than 10, do you want that to show 2 decimals?
> If what you intended was 2 decimals for numbers > -10 and < 10, you may want
> [<=-10]-0;[<10]0.00; 0
> --
> David Biddulph
>
> "David Biddulph" <groups [at] biddulph.org.uk> wrote in message
> news:4aaa1e6f$(E-Mail Removed)...
> > Format Cell/ Number/ Custom
> > [<10]0.00; 0
> >
> > You talked about <10 and >10, but I guessed that you meant >=10.
> > --
> > David Biddulph
> >
> > "Valeria" <(E-Mail Removed)> wrote in message
> > news:0EE3BF8F-C90E-4957-8DBA-(E-Mail Removed)...
> >> Dear experts,
> >> I have a table which looks up values from a database; this table is
> >> linked
> >> to a cell where I can select what I want to see in the table.
> >> Now, sometimes the values I want to see need to be in a 2 decimal format
> >> (ex. 2.43) and sometimes they need to have no decimals at all (ex.
> >> 25000). It
> >> really all depends on their magnitude - when <10 then I need to have 2
> >> decimals, if > 10 then no decimal is needed.
> >> Is there a way to do this in Excel 2003?
> >> Many thanks for your help!
> >> Kind regards
> >> --
> >> Valeria
> >
> >
>
>
>