G
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?
D
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..
point are not equal to 00 or 25 or 50 or 75, it returns an error?
=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..
formatted to two decimal places), where if the two digits after the decimalCatheyM said:
point are not equal to 00 or 25 or 50 or 75, it returns an error?
J
J.E. McGimpsey
One way:
=IF(INT(A1*4)=(A1*4),"","Error")
replace A1 with your cell reference or formula.
=IF(INT(A1*4)=(A1*4),"","Error")
replace A1 with your cell reference or formula.
G
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?
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?
D
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.
or .75 or 0, doesn't take into account what she sees with 2 decimal places
as the format.
J
J.E. McGimpsey
Ah, misread the question. Thanks for the correction.
K
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
----------------------------------------------------------------------------
equal to 00 or 25 or 50 or 75, it returns an error?
=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
----------------------------------------------------------------------------
to two decimal places), where if the two digits after the decimal point are notCatheyM said:
equal to 00 or 25 or 50 or 75, it returns an error?
J
Jonathan Rynd
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")
K
Ken Wright
None of the other answers so far take the number format into account.
You're sure about that??
You're sure about that??
P
Peo Sjoblom
Jonathan Rynd said:
In what way?
Mround is part of the ATP and you cannot use it as conditional format (at
least not directly)
Regards,
Peo Sjoblom
D
Dave R.
Most of them do!
J
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.