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

[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 #

[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

[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


"SusanN" <SusanN@discussions.microsoft.com> wrote in message
news:732E7001-4F8B-4D2D-B966-AAB983F10066@microsoft.com...
> 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 #
>

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



"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 #
>

[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?

"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 #
> >

[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.

"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 #
> > >

[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

"SusanN" <SusanN@discussions.microsoft.com> wrote in message
news:803AA7A1-65E6-45A5-9443-EFA1F7827E29@microsoft.com...
> 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 #
>> >

[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?

"JulieD" wrote:

> 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
>
> "SusanN" <SusanN@discussions.microsoft.com> wrote in message
> news:803AA7A1-65E6-45A5-9443-EFA1F7827E29@microsoft.com...
> > 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 #
> >> >
>
>
>

[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