I'm trying to create a spreadsheet at work that used for reporting test
results but I've run into a potential problem.

The actual results are simple with an IF statement comparing an average (A1)
to a specification (S1) and reporting the average if its below the
specification or "Fail" if it's above the spec. It looks like this:

=IF(A1<=S1, A1, "Fail")

The specification varies from a whole number to a decimal, depending on the
sample tested. If the spec is 5 then what I'm actually using in the equation
is 5.49 such that, when rounded to the correct level of precision, the result
is "not more than" then given spec. If the spec is 0.2 then the value in the
equation would be 0.249.

The problem is that I'd like to make this one spreadsheet work for all
samples and therefore all specifications (5 to 0.2) and the quick formulas I
know of won't work for something that changes the level of precision.

Is there a way to determine the number of decimal places in a cell such that
"if S1 has 0 decimals then yield this result"?

I've tried the function ROUND( but I can't float the decimal (at least I
don't know how to). If I were able to get that function to round to the
level specified in S1 then that might work...

Ithaca wrote...
....
The specification varies from a whole number to a decimal, depending on the
sample tested. *If the spec is 5 then what I'm actually using in the equation
is 5.49 such that, when rounded to the correct level of precision, the result
is "not more than" then given spec. *If the spec is 0.2 then the value in the
equation would be 0.249.

So comparisons at 1 significant digit?

The problem is that I'd like to make this one spreadsheet work for all
samples and therefore all specifications (5 to 0.2) and the quick formulas I
know of won't work for something that changes the level of precision.
....

For comparisons at 1 significant digit, try

=IF(TEXT(A1,".0E+000")<=TEXT(S1,".0E+000"),A1,"Fai l")

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!*


"Ithaca" wrote:

I'm trying to create a spreadsheet at work that used for reporting test
results but I've run into a potential problem.

The actual results are simple with an IF statement comparing an average (A1)
to a specification (S1) and reporting the average if its below the
specification or "Fail" if it's above the spec. It looks like this:

=IF(A1<=S1, A1, "Fail")

The specification varies from a whole number to a decimal, depending on the
sample tested. If the spec is 5 then what I'm actually using in the equation
is 5.49 such that, when rounded to the correct level of precision, the result
is "not more than" then given spec. If the spec is 0.2 then the value in the
equation would be 0.249.

The problem is that I'd like to make this one spreadsheet work for all
samples and therefore all specifications (5 to 0.2) and the quick formulas I
know of won't work for something that changes the level of precision.

Is there a way to determine the number of decimal places in a cell such that
"if S1 has 0 decimals then yield this result"?

I've tried the function ROUND( but I can't float the decimal (at least I
don't know how to). If I were able to get that function to round to the
level specified in S1 then that might work...

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!*

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!*

That seems to have worked perfectly! Thanks!

"Glenn" wrote:

Keep it as text and modify as follows:

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