Re: Asking too much of Excel?

Keep it as text and modify as follows:

=IF(ISERROR(FIND(".",A1)),0,LEN(A1)-FIND(".",A1))

Ithaca wrote:
This works well, with only a couple of issues popping up relating to reference cell formatting. The functionality has inspired me to expand it's use for a wider range of reference values, down to two decimal places.

If the reference cell is formatted as text this equation works for any number of decimals but fails for whole numbers (due to lack of a decimal point in the cell). A simple workaround is typing "1." but when I push this out for use by the rest of the group I know I'll get frantic call about it not working due to them not putting in the decimal. My goal would be to have something that an idiot could work...

I tried switching the cell format to "general" or "custom" and it works for integers now but fails for decimals with a (significant to me) trailing zero. Every attempt I've made with formatting the cell drops the "insignificant" zero at the end of my decimal (e.g. 0.30) and gives me the result for the single decimal input.

Is there a way to get around this? Can I format the cell to keep the trailing zero, if entered, AND not mess with the format of other inputs (1=1 not 1.00)? Am I asking too much of Excel with this?

"Luke M" wrote:

You could do something like this:
=IF(INT(A1)=A1,0,LEN(A1)-FIND(".",A1))
to get you the amount of decimals entered.

NOTE: This depends on actual number entered, not how number is formatted. Inputing "1" and formatting as number with two decimals, the formula will still return 0.

You could then use this number in a INDEX or LOOKUP function, to return the correct number to use in rest of calculation.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

.

Relevant Pages

  • Re: Formula Help
    ... Enter 100 in an unused cell. ... "Leo Heuser" wrote: ... Format B2 with 2 decimals. ... You can't do it with formatting. ...
    (microsoft.public.excel.worksheet.functions)
  • RE: how to highlight a cell so I know which point on the chart it
    ... differnet groups to highlight in distinctive ways it's a little ... a bold border or any formatting effect you want to apply. ... Both ways defenitely help to find the reference cell of the data ...
    (microsoft.public.excel.charting)
  • Asking too much of Excel?
    ... If the reference cell is formatted as text this equation works for any ... integers now but fails for decimals with a trailing zero. ... Every attempt I've made with formatting the cell drops the "insignificant" ...
    (microsoft.public.excel.worksheet.functions)
  • Re: percentage question
    ... If you want to round to the nearest 1%, that is 2 decimal places, so change ... and is formatted as percentage with 0 decimals. ... But when I take that percentage and multiply it by another cell to ... Perhaps if you remove all formatting and look at your numbers, ...
    (microsoft.public.excel.misc)
  • Re: pound signs cell formatting
    ... when I formatted it for number, any number of decimals didn't make it ... even grid in the body of the sheet. ... I can't make the cell larger ... I've already tried formatting it ...
    (microsoft.public.excel.misc)