Rounding Off

[Joohn Calder]

Hi

I have the following formula in my worksheet

=IF(AK6="","",(AK6+AM6)/2)

Even although I have formatted this column to zero decimal places the pivot
table I use reads it to one deciamal place.

Can anyone tell me how I could add the "ROUND" function to the above formula
so that when my pivot table reads it there is zero decimal places?

I use Excel 2000

Thanks

[Max]

Try: =IF(AK6="","",ROUND((AK6+AM6)/2,0))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Joohn Calder" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I have the following formula in my worksheet
>
> =IF(AK6="","",(AK6+AM6)/2)
>
> Even although I have formatted this column to zero decimal places the
pivot
> table I use reads it to one deciamal place.
>
> Can anyone tell me how I could add the "ROUND" function to the above
formula
> so that when my pivot table reads it there is zero decimal places?
>
> I use Excel 2000
>
> Thanks
>
>

[Anne Troy]

Try this: =IF(AK6="","",round(AK6+AM6)/2),0)
************
Anne Troy
www.OfficeArticles.com

"Joohn Calder" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I have the following formula in my worksheet
>
> =IF(AK6="","",(AK6+AM6)/2)
>
> Even although I have formatted this column to zero decimal places the
> pivot
> table I use reads it to one deciamal place.
>
> Can anyone tell me how I could add the "ROUND" function to the above
> formula
> so that when my pivot table reads it there is zero decimal places?
>
> I use Excel 2000
>
> Thanks
>
>

[Peo Sjoblom]

If you want to round to nearest integer

=ROUND(IF(AK6="","",(AK6+AM6)/2),-1)

if you want to remove the decimals

=INT(IF(AK6="","",(AK6+AM6)/2))


=TRUNC(IF(AK6="","",(AK6+AM6)/2))


INT and TRUNC works the same way for positive numbers but not for negative

--
Regards,

Peo Sjoblom

(No private emails please)


"Joohn Calder" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I have the following formula in my worksheet
>
> =IF(AK6="","",(AK6+AM6)/2)
>
> Even although I have formatted this column to zero decimal places the
> pivot
> table I use reads it to one deciamal place.
>
> Can anyone tell me how I could add the "ROUND" function to the above
> formula
> so that when my pivot table reads it there is zero decimal places?
>
> I use Excel 2000
>
> Thanks
>
>

[Peo Sjoblom]

Ouch, put the round, int or trunc inside the if function or else you'll get
an error if AK6 is blank

--
Regards,

Peo Sjoblom

(No private emails please)


"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> If you want to round to nearest integer
>
> =ROUND(IF(AK6="","",(AK6+AM6)/2),-1)
>
> if you want to remove the decimals
>
> =INT(IF(AK6="","",(AK6+AM6)/2))
>
>
> =TRUNC(IF(AK6="","",(AK6+AM6)/2))
>
>
> INT and TRUNC works the same way for positive numbers but not for negative
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Joohn Calder" <[email protected]> wrote in message
> news:[email protected]...
>> Hi
>>
>> I have the following formula in my worksheet
>>
>> =IF(AK6="","",(AK6+AM6)/2)
>>
>> Even although I have formatted this column to zero decimal places the
>> pivot
>> table I use reads it to one deciamal place.
>>
>> Can anyone tell me how I could add the "ROUND" function to the above
>> formula
>> so that when my pivot table reads it there is zero decimal places?
>>
>> I use Excel 2000
>>
>> Thanks
>>
>>
>

[Joohn Calder]

Thanks to all !