[AI] EXCEL FORMULA

Amiyo Biswas amiyo.biswas at gmail.com
Tue Apr 27 22:31:08 EDT 2010


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 





More information about the AccessIndia mailing list