add 2 cells w/ 2 dec each, total=whole # except if answers is 2.9.

S

SusanN

need a formula: 2 cells w/ 2 decimals each, total = whole # except if
answers are between 2.96 - 2.99 range, does not want them to round up to
whole #
 
J

JulieD

Hi Susan

here's one option

=IF(AND(A1+A2>=2.96,A1+A2<=2.99),A1+A2,ROUND(A1+A2,0))

Cheers
JulieD
 
K

Kassie

The correct formula would be to use "OR", and not "AND".

=IF(OR(A1+A2>=2.96,A1+A2<=2.99),A1+A2,ROUND(A1+A2,0))
 
S

SusanN

When I chgd it to "OR", if the answer is 3.2 it did not round it down to "3",
it displayed 3.2. (using AND, it down round it down) Also, I noticed w/ AND
or OR, if the answer was had 6 decimals it displayed that, not in 2 decimals.
Is it possible to display 2 decimal answer w/in the range but round up/down
outside the range?
 
K

Kassie

You are so right. Try this
one:=IF(A1+A2<2.96,ROUND(A1+A2,0),IF(A1+A2>2.99,ROUND(A1+A2,0),A1+A2)). It
MUST now work. To limit the number of decimals displayd, format the cell
containing the formula as Number with 2 decimals.
 
J

JulieD

Hi Susan

try
=IF(AND(A1+A2>=2.96,A1+A2<=2.99),Round(A1+A2,2),ROUND(A1+A2,0))

note i've suggested rounding it rather than changing the display - which
just "hides" the extra decimals. Rounding it changes the value and if these
values are used in other calcs you won't then get rounding errors.

does this work for you?

Cheers
JulieD
 
S

SusanN

Here is the formula we used,
=IF(AND(J57+J59<3),ROUND(J57+J59,2),ROUND(J57+J59,0)) everything works for
#'s less than 3.00 (they stay rounded to 2 decimals), but need for 3.xx or
greater to round to 1 decimal place. Is this possible?
 
S

swatsp0p

Hello, SusanN:

Assuming your two numbers are in A1:A2, use this formula:

=IF(AND(SUM(A1:A2)>2.95,SUM(A1:A2)<3),SUM(A1:A2),ROUND(SUM(A1:A2),0))

Format this cell as General:

2.49 will return 2
2.50 will return 3
2.95 will return 3
2.96 will return 2.96
....
2.99 will return 2.99
3.00 will return 3
etc.

HTH

Bruce
 
Top