[AI] EXCEL FORMULA

Zujar Shabbir Kanchwala zujarbright at gmail.com
Wed Apr 28 15:02:28 EDT 2010


If I understand it correctly, specifying 2 in the Number of Decimal Places
spin box is equivalent to the round() function with 2 as second argument.
Let me make it clear that both these methods would not change 9.49 or 9.50
(numbers with 2 decimal places) if you want 2 decimal places in the result.
If you opt for 1 decimal place, 9.49 wull yield 9.5 by both methods. Also it
is not necessary to specify a cell name as the first argument of the round()
function. You would not need two cells if you specify the number itself as
the first argument of the round() function.

hth...

Best Regards,
Zujar...
 
An optimist laughs to forget, whereas a pessimist forgets to laugh!


-----Original Message-----
From: accessindia-bounces at accessindia.org.in
[mailto:accessindia-bounces at accessindia.org.in] On Behalf Of Amiyo Biswas
Sent: Wednesday, April 28, 2010 8:01 AM
To: accessindia at accessindia.org.in
Subject: Re: [AI] EXCEL FORMULA

Hello,

Thanks to all of you for discussion on formulas. I think, I failed to make 
my question clear. When we are using formulas, we have to use two cells, 
cell A1 for entry of value, B1 for display of value, for instance. I was 
looking for a format option which can solve the problem.

Let me explain it further. Suppose, you write 9.03 in cell a1. Now press 
ctrl+1 for format cell dialogue box, select number category and enter 0 in 
the number of decimal places. This will yield 9 in the same cell A1. Now 
write 9.53 in cell A1 and you will get 10 since you have 0 decimal place in 
the number format box. Experiment it with 9.49 and 9.50 and notice that the 
values displayed are 9 and 10.

My question is, this is an easy alternative for the formula
=ROUND(A1,0)

Can you suggest any format option for an alternative to
=ROUND(A1,2)

In that case, we could use a single cell for both entry and display of 
value.

Best regards,
Amiyo Biswas
Cell: 91-9433464329

----- Original Message ----- 
From: "Zujar Shabbir Kanchwala" <zujarbright at gmail.com>
To: <accessindia at accessindia.org.in>
Sent: Tuesday, April 27, 2010 7:28 AM
Subject: Re: [AI] EXCEL FORMULA


> Using formula you can convert 9.03 to 10 like this:
>
> =ceiling(9.03,1)
>
> The CEILING() function calculates the next multiple of a number after a
> given number. The syntax is:
>
> =CEILING(number,significance)
>
> Where number is the base number which you want to round off.
>
> And
>
> Significance is the multiple to consider. Specifying 1 here gives you the
> next non decimal number if the first argument is a decimal number.
>
> Examples:
>
> =ceiling(10.5,2) gives 12 (the next multiple of 2 after 10.5)
>
> =ceiling(22,11) gives 22. (22 itself is a multiple of 11)
>
> =ceiling(6.15, 4.02) gives 8.04
>
> Hope that's clear...
>
> Best Regards,
> Zujar...
>
> An optimist laughs to forget, whereas a pessimist forgets to laugh!
>
> -----Original Message-----
> From: accessindia-bounces at accessindia.org.in
> [mailto:accessindia-bounces at accessindia.org.in] On Behalf Of Amiyo Biswas
> Sent: Tuesday, April 27, 2010 6:20 AM
> To: accessindia at accessindia.org.in
> Subject: Re: [AI] EXCEL FORMULA
>
> Hello Kamal,
>
> How was it solved? You wanted a format option and I did not notice if
> anybody suggested any suggestion on format option. We were all discussing
> formulas.
> Best regards,
> Amiyo Biswas
> Cell: 91-9433464329
>
>
> ----- Original Message ----- 
> From: "Kamal Verma" <kamalverma at pnb.co.in>
> To: <accessindia at accessindia.org.in>
> Sent: Monday, April 26, 2010 11:00 AM
> Subject: Re: [AI] EXCEL FORMULA
>
>
>> Hello!
>>
>> Thanks to all for their valuable suggestions. My problem is solved now.
>>
>> Kamal Verma.
>> ----- Original Message ----- 
>> From: "Surya Prakash Sharma" <surya.sharma26 at gmail.com>
>> To: <accessindia at accessindia.org.in>
>> Sent: Monday, April 26, 2010 6:43 AM
>> Subject: Re: [AI] EXCEL FORMULA
>>
>>
>>> hi,
>>> 9.00 is the nearest round off of 9.03. so one should not expect 10 from
>>> the formula.
>>> ----- Original Message ----- 
>>> From: "Amiyo Biswas" <amiyo.biswas at gmail.com>
>>> To: <accessindia at accessindia.org.in>
>>> Sent: Monday, April 26, 2010 6:18 AM
>>> Subject: Re: [AI] EXCEL FORMULA
>>>
>>>
>>>> Ok, it's not giving 10 for 9.03, as Kamal wanted.
>>>>
>>>> ----- Original Message ----- 
>>>> From: "Mukesh Baviskar" <mukeshbaviskar.nsk at gmail.com>
>>>> To: <accessindia at accessindia.org.in>
>>>> Sent: Sunday, April 25, 2010 7:34 AM
>>>> Subject: Re: [AI] EXCEL FORMULA
>>>>
>>>>
>>>>> Here is the solution for your problem.
>>>>> Open excel sheet.
>>>>> For example: write 9.03 in a1.
>>>>> Go to format menu or simply press the shortcut ctrl + 1 and go to
>>>>> number tab.
>>>>> Choose category number-tab-decimal place 0-tab-okay-enter. Copy this
>>>>> format in required column or row. your problem will be solved. Thank
>>>>> you.
>>>>>
>>>>> Mukesh R. Baviskar
>>>>> 9403161157
>>>>> ----- Original Message ----- 
>>>>> From: "Kamal Verma" <kamalverma at pnb.co.in>
>>>>> To: <accessindia at accessindia.org.in>
>>>>> Sent: Thursday, April 22, 2010 11:28 AM
>>>>> Subject: [AI] EXCEL FORMULA
>>>>>
>>>>>
>>>>>> Hello everyone,
>>>>>>
>>>>>> I want to round off the figure in nearest rupee. Suppose, I enter 
>>>>>> 9.03
>
>>>>>> in A1, it should appear as 10.00 in the same cell. Further, I enter
>>>>>> 99.10 in B1, it should automatically be changed to 100.
>>>>>>
>>>>>> Please suggest me any formula or macro in this regard. Though I am
>>>>>> well acquainted  with excel formulae, this exercise is out of my
>>>>>> comprehension.
>>>>>>
>>>>>> Kamal Verma,
>>>>>>
>>>>>>
>>>>>>
>>>>>> DISCLAIMER:
>>>>>>
>>>>>>
>>>>>> The Information transmitted in this email is solely for the 
>>>>>> addressee.
>
>>>>>> It is confidential and may be legally privileged. Access to this 
>>>>>> email
>
>>>>>> by anyone else is unauthorized. Any disclosure, copying, distribution
>>>>>> or any action taken by anyone other than by the intended recipient is
>>>>>> prohibited and may be unlawful. If you are not the intended recipient
>>>>>> then kindly delete the mail from your system. Any opinion or views
>>>>>> expressed in this mail may not necessarily reflect that of Punjab
>>>>>> National Bank. The bank considers unencrypted email as an insecure
>>>>>> mode of communication.
>>>>>>
>>>>>>
>>>>>> To unsubscribe send a message to
>>>>>> accessindia-request at accessindia.org.in with the subject unsubscribe.
>>>>>>
>>>>>> To change your subscription to digest mode or make any other changes,
>>>>>> please visit the list home page at
>>>>>>
>>>>>>
> http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> To unsubscribe send a message to 
>>>>> accessindia-request at accessindia.org.in
>
>>>>> with the subject unsubscribe.
>>>>>
>>>>> To change your subscription to digest mode or make any other changes,
>>>>> please visit the list home page at
>>>>>
>>>>>
> http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
>>>>
>>>>
>>>>
>>>>
>>>> To unsubscribe send a message to accessindia-request at accessindia.org.in
>>>> with the subject unsubscribe.
>>>>
>>>> To change your subscription to digest mode or make any other changes,
>>>> please visit the list home page at
>>>>
>>>>
> http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
>>>
>>>
>>>
>>>
>>> To unsubscribe send a message to accessindia-request at accessindia.org.in
>>> with the subject unsubscribe.
>>>
>>> To change your subscription to digest mode or make any other changes,
>>> please visit the list home page at
>>>
>>>
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
>>>
>>
>>
>>
>> DISCLAIMER:
>>
>>
>> The Information transmitted in this email is solely for the addressee. It
>> is confidential and may be legally privileged. Access to this email by
>> anyone else is unauthorized. Any disclosure, copying, distribution or any
>> action taken by anyone other than by the intended recipient is prohibited
>> and may be unlawful. If you are not the intended recipient then kindly
>> delete the mail from your system. Any opinion or views expressed in this
>> mail may not necessarily reflect that of Punjab National Bank. The bank
>> considers unencrypted email as an insecure mode of communication.
>>
>>
>> To unsubscribe send a message to accessindia-request at accessindia.org.in
>> with the subject unsubscribe.
>>
>> To change your subscription to digest mode or make any other changes,
>> please visit the list home page at
>> 
>> http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
>
>
>
>
>
> To unsubscribe send a message to accessindia-request at accessindia.org.in 
> with
> the subject unsubscribe.
>
> To change your subscription to digest mode or make any other changes, 
> please
> visit the list home page at
>  http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.814 / Virus Database: 271.1.1/2836 - Release Date: 04/26/10
> 12:01:00
>
>
>
>
> To unsubscribe send a message to accessindia-request at accessindia.org.in 
> with the subject unsubscribe.
>
> To change your subscription to digest mode or make any other changes, 
> please visit the list home page at
>  http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in





To unsubscribe send a message to accessindia-request at accessindia.org.in with
the subject unsubscribe.

To change your subscription to digest mode or make any other changes, please
visit the list home page at
  http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 9.0.814 / Virus Database: 271.1.1/2838 - Release Date: 04/27/10
11:57:00





More information about the AccessIndia mailing list