Formula Needed

[Guest]

Is it possible to put in a formula for a range of cells (which are formatted to two decimal places), where if the two digits after the decimal point are not equal to 00 or 25 or 50 or 75, it returns an error?

 

[Dave R.]

I'm pretty sure this is overkill, but it works;

=IF(SUMPRODUCT((MOD(ROUND(A1,2),1)={0,0.25,0.5,0.75})*1)=1,"","error")

If your values are in A1, this looks at the digits to the right of the
decimal point once rounded (formatting doesn't make any difference to the
actual value of the number) to see if they are 0,.25,.5,.75. if they're not,
"error". this formula would have to be copied down.

maybe theres a way to look at what the format produces..

Is it possible to put in a formula for a range of cells (which are

formatted to two decimal places), where if the two digits after the decimal
point are not equal to 00 or 25 or 50 or 75, it returns an error?

 

[J.E. McGimpsey]

One way:

=IF(INT(A1*4)=(A1*4),"","Error")

replace A1 with your cell reference or formula.

 

[Guest]

Cathey
You can't get a formula to replace the actual number in the cell, but there are a couple of other options

1) Format>Conditional_Formatting. Use the following formula

=MOD(A1,0.25)<>

and set a pattern to highlight the cells

2) Data>Validation. Use a custom formula of

=MOD(A1,0.25)=

to restrict users from entering values that don't meet the criteria

3) In an adjacent column use the formula

=IF(MOD(A1,0.25)=0,"","Invalid Entry"

This will put an error message in the adjacent cell

Good Luck
Mark Graesse
(e-mail address removed)

----- CatheyM wrote: ----

Is it possible to put in a formula for a range of cells (which are formatted to two decimal places), where if the two digits after the decimal point are not equal to 00 or 25 or 50 or 75, it returns an error?

 

[Dave R.]

This will only work if the values in that column are actually rounded to .25
or .75 or 0, doesn't take into account what she sees with 2 decimal places
as the format.

 

[J.E. McGimpsey]

Ah, misread the question. Thanks for the correction.

 

[Ken Wright]

With your number in A1

=IF(MOD(ROUND(A1-INT(A1),2),0.25)<>0,"ERROR","")

This should handle the data as though it were actually as you have formatted it.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Is it possible to put in a formula for a range of cells (which are formatted

to two decimal places), where if the two digits after the decimal point are not
equal to 00 or 25 or 50 or 75, it returns an error?

 

[Jonathan Rynd]

Is it possible to put in a formula for a range of cells (which are
formatted to two decimal places), where if the two digits after the
decimal point are not equal to 00 or 25 or 50 or 75, it returns an
error?

if the data starts in A1, put this B1 and fill down:
=MROUND(A1,0.25)=MROUND(A1,0.01)
returns "TRUE" if the two displayed digits after the decimal point are
00, 25, 50, or 75, and returns "FALSE" if the two displayed digits after
the decimal point are anything else.

None of the other answers so far take the number format into account.

You can use this as a conditional format, or you can use it as an input
to an IF:

=IF(MROUND(A1,0.25)=MROUND(A1,0.01),A1,"Error")

 

[Ken Wright]

None of the other answers so far take the number format into account.

You're sure about that??

 

[Peo Sjoblom]

if the data starts in A1, put this B1 and fill down:
=MROUND(A1,0.25)=MROUND(A1,0.01)
returns "TRUE" if the two displayed digits after the decimal point are
00, 25, 50, or 75, and returns "FALSE" if the two displayed digits after
the decimal point are anything else.

None of the other answers so far take the number format into account.

In what way?

You can use this as a conditional format, or you can use it as an input
to an IF:

=IF(MROUND(A1,0.25)=MROUND(A1,0.01),A1,"Error")

Mround is part of the ATP and you cannot use it as conditional format (at
least not directly)



Regards,

Peo Sjoblom

 

[Dave R.]

Most of them do!

 

[Jonathan Rynd]

You're sure about that??

No, I'm not. I should have said "Most of.... don't". I didn't see the
call to ROUND( ,2) in Dave's post. My apologies! Dave was first and with
the right answer.