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

[Guest]

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 #

 

[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

 

[Guest]

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))

 

[Guest]

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?

 

[Guest]

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.

 

[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

 

[Guest]

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?

 

[Guest]

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