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

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 #

Hi Susan

here's one option

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

Cheers
JulieD


"SusanN" wrote in message
...
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 #

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



"SusanN" wrote:

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 #

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?

"Kassie" wrote:

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



"SusanN" wrote:

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 #

You are so right. Try this
one:=IF(A1+A2<2.96,ROUND(A1+A2,0),IF(A1+A22.99,RO UND(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.

"SusanN" wrote:

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?

"Kassie" wrote:

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



"SusanN" wrote:

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 #

Hi Susan

try
=IF(AND(A1+A2=2.96,A1+A2<=2.99),Round(A1+A2,2),RO UND(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

"SusanN" wrote in message
...
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?

"Kassie" wrote:

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



"SusanN" wrote:

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 #

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?

"JulieD" wrote:

Hi Susan

try
=IF(AND(A1+A2=2.96,A1+A2<=2.99),Round(A1+A2,2),RO UND(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

"SusanN" wrote in message
...
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?

"Kassie" wrote:

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



"SusanN" wrote:

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 #

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),R OUND(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