Re: [AI] Excel formula
-- From: "Mukesh Baviskar" Sent: Monday, August 18, 2014 5:00 PM To: "Mukesh Baviskar" Subject: Re: [AI] Excel formula Hi Excel experts, Waiting to receive a guidance to solve this problem soon. Please do think on this challenging problem. Thanking you in anticipation. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "Mukesh Baviskar" Sent: Sunday, August 17, 2014 2:14 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning the disabled." Subject: Re: [AI] Excel formula Yes, I sort the total marks in in descending order. I'm unable to sort the fail in subject column in custom sort as: P all, F1, F2, F3 and so on. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "Aravind R" Sent: Sunday, August 17, 2014 1:15 PM To: "AccessIndia: a list for discussing accessibility and issues concerningthe disabled." Subject: Re: [AI] Excel formula can't you sort marks by decending order? On 8/17/14, Mukesh Baviskar wrote: Dear friends, I want to sort out the top 5 students in the class. The criteria are as follows: https://dl.dropboxusercontent.com/u/21186849/Multisort.xlsm 1. The total should be in descending order, column N 2. The fail in subjects column should be in ascending order as: P all, F1, F2, F3, F4, F5, F6, F7, F8, F9 I want to achieve it by array formula. It's very urgent so friends please do help me to achieve this target at the earliest. Waiting to receive a positive response with a solution soon. Thanking you, Regards, Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. -- nothing is difficult unless you make it appear so. r. aravind, D R O in bank of baroda, mobile no: +91 9940369593, email id : aravind_...@yahoo.com, aravind.andhrab...@gmail.com. Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] Excel formula
Yes, I sort the total marks in in descending order. I'm unable to sort the fail in subject column in custom sort as: P all, F1, F2, F3 and so on. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "Aravind R" Sent: Sunday, August 17, 2014 1:15 PM To: "AccessIndia: a list for discussing accessibility and issues concerningthe disabled." Subject: Re: [AI] Excel formula can't you sort marks by decending order? On 8/17/14, Mukesh Baviskar wrote: Dear friends, I want to sort out the top 5 students in the class. The criteria are as follows: https://dl.dropboxusercontent.com/u/21186849/Multisort.xlsm 1. The total should be in descending order, column N 2. The fail in subjects column should be in ascending order as: P all, F1, F2, F3, F4, F5, F6, F7, F8, F9 I want to achieve it by array formula. It's very urgent so friends please do help me to achieve this target at the earliest. Waiting to receive a positive response with a solution soon. Thanking you, Regards, Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. -- nothing is difficult unless you make it appear so. r. aravind, D R O in bank of baroda, mobile no: +91 9940369593, email id : aravind_...@yahoo.com, aravind.andhrab...@gmail.com. Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] Excel formula
can't you sort marks by decending order? On 8/17/14, Mukesh Baviskar wrote: > Dear friends, > > I want to sort out the top 5 students in the class. The criteria are as > follows: > > > > https://dl.dropboxusercontent.com/u/21186849/Multisort.xlsm > > > > 1. The total should be in descending order, column N > > 2. The fail in subjects column should be in ascending order as: > > P all, F1, F2, F3, F4, F5, F6, F7, F8, F9 > > I want to achieve it by array formula. It's very urgent so friends please do > help me to achieve this target at the earliest. > > > > Waiting to receive a positive response with a solution soon. > > > > Thanking you, > > > > Regards, > > > > Mukesh Baviskar > > Mobile: 9403161157 > > > > > Register at the dedicated AccessIndia list for discussing accessibility of > mobile phones / Tabs on: > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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: > 1. Contents of the mails, factual, or otherwise, reflect the thinking of the > person sending the mail and AI in no way relates itself to its veracity; > > 2. AI cannot be held liable for any commission/omission based on the mails > sent through this mailing list.. > -- nothing is difficult unless you make it appear so. r. aravind, D R O in bank of baroda, mobile no: +91 9940369593, email id : aravind_...@yahoo.com, aravind.andhrab...@gmail.com. Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] Excel formula required
hi, Extremely busy for weeks. See afterward. Remind me later. On 6/4/14, Mukesh Baviskar wrote: > Hi, > Thank you. Will you please modify the sheet as per your suggestion and send > > it to me on my personal email id as a attachment. Do change the file into > macro enable file. > > Waiting to receive a positive solution. > > Thank you. > > Regards, > > Mukesh Baviskar > Mobile: 9403161157 > > > -- > From: "Him Prasad Gautam" > Sent: Wednesday, June 04, 2014 2:57 PM > To: "AccessIndia: a list for discussing accessibility and issues > concerningthe disabled." > Subject: Re: [AI] Excel formula required > >> hi, >> There is not a ready made formula. >> However, you can do it easily with yourself. >> 1. In the sheet in which you want put the top five student, just link >> the cell like sheet1!a1 >> 2. Sort the sheet with the marks . >> 3. The name, subject, marks will be automatically adjusted. >> Alternatively, you can use the validation command to a cell too. >> The name can be auto generated as said in hint 1. >> For auto sorting, a macro coding is essential. >> >> >> >> >> >> On 6/4/14, Mukesh Baviskar wrote: >>> Dear friends, >>> I want to find out the first 5 students in each subject. In the attached >>> file there are 10 tables on sheet 2. I want to fill out these table by >>> formula. The name of student and his role number should also come by >>> formula. If there are more students having same marks in subject then >>> they >>> should also consider in top 5. The number of student then may be 6 or 7. >>> >>> For >>> example in hindi subject there are 3 students having same marks (46) so >>> there will be 6 students in the table. The main result sheet is on sheet >>> >>> 1. >>> >>> Please see the file on the following link. >>> >>> https://dl.dropboxusercontent.com/u/21186849/first%20five.xlsx >>> >>> Thanking you in anticipation. >>> >>> Regards, >>> Mukesh Baviskar >>> Mobile: 9403161157 >>> >>> >>> Register at the dedicated AccessIndia list for discussing accessibility >>> of >>> mobile phones / Tabs on: >>> http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in >>> >>> >>> Search for old postings at: >>> http://www.mail-archive.com/accessindia@accessindia.org.in/ >>> >>> To unsubscribe send a message to >>> accessindia-requ...@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: >>> 1. Contents of the mails, factual, or otherwise, reflect the thinking of >>> >>> the >>> person sending the mail and AI in no way relates itself to its veracity; >>> >>> 2. AI cannot be held liable for any commission/omission based on the >>> mails >>> sent through this mailing list.. >>> >> >> >> -- >> Him Prasad Gautam >> Kathmandu, Nepal >> >> >> >> Register at the dedicated AccessIndia list for discussing accessibility of >> >> mobile phones / Tabs on: >> http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in >> >> >> Search for old postings at: >> http://www.mail-archive.com/accessindia@accessindia.org.in/ >> >> To unsubscribe send a message to >> accessindia-requ...@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: >> 1. Contents of the mails, factual, or otherwise, reflect the thinking of >> the person sending the mail and AI in no way relates itself to its >> veracity; >> >> 2. AI cannot be held liable for any commission/omission based on the mails >> >> sent through this mailing list.. > > > > > Register at the dedicated AccessIndia list for discussing accessibility of > mobile phones / Tabs on: > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessin
Re: [AI] Excel formula required
Hi, Thank you. Will you please modify the sheet as per your suggestion and send it to me on my personal email id as a attachment. Do change the file into macro enable file. Waiting to receive a positive solution. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "Him Prasad Gautam" Sent: Wednesday, June 04, 2014 2:57 PM To: "AccessIndia: a list for discussing accessibility and issues concerningthe disabled." Subject: Re: [AI] Excel formula required hi, There is not a ready made formula. However, you can do it easily with yourself. 1. In the sheet in which you want put the top five student, just link the cell like sheet1!a1 2. Sort the sheet with the marks . 3. The name, subject, marks will be automatically adjusted. Alternatively, you can use the validation command to a cell too. The name can be auto generated as said in hint 1. For auto sorting, a macro coding is essential. On 6/4/14, Mukesh Baviskar wrote: Dear friends, I want to find out the first 5 students in each subject. In the attached file there are 10 tables on sheet 2. I want to fill out these table by formula. The name of student and his role number should also come by formula. If there are more students having same marks in subject then they should also consider in top 5. The number of student then may be 6 or 7. For example in hindi subject there are 3 students having same marks (46) so there will be 6 students in the table. The main result sheet is on sheet 1. Please see the file on the following link. https://dl.dropboxusercontent.com/u/21186849/first%20five.xlsx Thanking you in anticipation. Regards, Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. -- Him Prasad Gautam Kathmandu, Nepal Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] Excel formula required
hi, There is not a ready made formula. However, you can do it easily with yourself. 1. In the sheet in which you want put the top five student, just link the cell like sheet1!a1 2. Sort the sheet with the marks . 3. The name, subject, marks will be automatically adjusted. Alternatively, you can use the validation command to a cell too. The name can be auto generated as said in hint 1. For auto sorting, a macro coding is essential. On 6/4/14, Mukesh Baviskar wrote: > Dear friends, > I want to find out the first 5 students in each subject. In the attached > file there are 10 tables on sheet 2. I want to fill out these table by > formula. The name of student and his role number should also come by > formula. If there are more students having same marks in subject then they > should also consider in top 5. The number of student then may be 6 or 7. For > example in hindi subject there are 3 students having same marks (46) so > there will be 6 students in the table. The main result sheet is on sheet 1. > > Please see the file on the following link. > > https://dl.dropboxusercontent.com/u/21186849/first%20five.xlsx > > Thanking you in anticipation. > > Regards, > Mukesh Baviskar > Mobile: 9403161157 > > > Register at the dedicated AccessIndia list for discussing accessibility of > mobile phones / Tabs on: > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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: > 1. Contents of the mails, factual, or otherwise, reflect the thinking of the > person sending the mail and AI in no way relates itself to its veracity; > > 2. AI cannot be held liable for any commission/omission based on the mails > sent through this mailing list.. > -- Him Prasad Gautam Kathmandu, Nepal Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] Excel formula
Hi Johar, Excellent! It's working fine as per my requirement. Thank you and have a nice day! Regards, Mukesh Baviskar -- From: "zoher kheriwala" Sent: Friday, January 10, 2014 11:38 AM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula here is the solution, =IF(AND(DAY(TODAY())=DAY(A1), MONTH(TODAY())=MONTH(A1)),"today",IF(AND(DAY(TODAY())<>DAY(A1), MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," ")) zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message ----- From: "Mukesh Baviskar" To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Sent: Thursday, January 09, 2014 9:59 PM Subject: Re: [AI] Excel formula Hi Johar, The example is as follows: 01/01/1999: January 09/01/1999: Today 12/01/2011: January 09/01/2014: Today 09/01/1934: Today 11/02/14: 31/3/2012: 09/01/1911: Today Thank you and good night! Regards, Mukesh Baviskar Mobile: 9403161157 ------------------ From: "zoher kheriwala" Sent: Thursday, January 09, 2014 7:56 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula I am not getting your question, so kindly provide me with some examples. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Sent: Thursday, January 09, 2014 7:01 PM Subject: Re: [AI] Excel formula Hi Johar, Thank you. It's working fine. Actually, I want to create a list of today's birthdays and a list of birthdays in that particular months. The formula you suggested is creating a list of today's birthdays. I understood after seeing the output. So in this formula I want a remark 'Today' and not the particular month. Secondly, I want a remark 'particular month' when the birthday is in that particular month. So the remark should be the name of that month ignoring the day and year. Is it possible? Thank you for solution. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Thursday, January 09, 2014 12:01 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula You can acheave this by following formula. note I have changed the cell address from P9 to a1. =IF(AND(DAY(TODAY())=DAY(A1), MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," ") zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "Access India" Sent: Wednesday, January 08, 2014 10:20 AM Subject: [AI] Excel formula Dear friends, Please correct this formula to get correct output. =IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),"January","February","March","April","May","June","July","August","September","October","November","December"),"")) It's working fine if the year is 2014 but not if the year is different. Conditions: If the date is today then only the month should display else blank. The year should be ignore. For example: 08/01/2000: January 01/01/2000: blank cell 08/01/2014: January 01/01/2014: blank cell The date is in column 'p' in dd mm format. In short if the date is current date that is for today '8' then only the month should be display in the output. Please suggest me a correction to get correct output. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@accessindia.org.in with the subject unsubscribe. To change your subscription to digest mode or make any othe
Re: [AI] Excel formula
here is the solution, =IF(AND(DAY(TODAY())=DAY(A1), MONTH(TODAY())=MONTH(A1)),"today",IF(AND(DAY(TODAY())<>DAY(A1), MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," ")) zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Sent: Thursday, January 09, 2014 9:59 PM Subject: Re: [AI] Excel formula Hi Johar, The example is as follows: 01/01/1999: January 09/01/1999: Today 12/01/2011: January 09/01/2014: Today 09/01/1934: Today 11/02/14: 31/3/2012: 09/01/1911: Today Thank you and good night! Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Thursday, January 09, 2014 7:56 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula I am not getting your question, so kindly provide me with some examples. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message ----- From: "Mukesh Baviskar" To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Sent: Thursday, January 09, 2014 7:01 PM Subject: Re: [AI] Excel formula Hi Johar, Thank you. It's working fine. Actually, I want to create a list of today's birthdays and a list of birthdays in that particular months. The formula you suggested is creating a list of today's birthdays. I understood after seeing the output. So in this formula I want a remark 'Today' and not the particular month. Secondly, I want a remark 'particular month' when the birthday is in that particular month. So the remark should be the name of that month ignoring the day and year. Is it possible? Thank you for solution. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Thursday, January 09, 2014 12:01 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula You can acheave this by following formula. note I have changed the cell address from P9 to a1. =IF(AND(DAY(TODAY())=DAY(A1), MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," ") zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "Access India" Sent: Wednesday, January 08, 2014 10:20 AM Subject: [AI] Excel formula Dear friends, Please correct this formula to get correct output. =IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),"January","February","March","April","May","June","July","August","September","October","November","December"),"")) It's working fine if the year is 2014 but not if the year is different. Conditions: If the date is today then only the month should display else blank. The year should be ignore. For example: 08/01/2000: January 01/01/2000: blank cell 08/01/2014: January 01/01/2014: blank cell The date is in column 'p' in dd mm format. In short if the date is current date that is for today '8' then only the month should be display in the output. Please suggest me a correction to get correct output. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing lis
Re: [AI] Excel formula
Hi Johar, The example is as follows: 01/01/1999: January 09/01/1999: Today 12/01/2011: January 09/01/2014: Today 09/01/1934: Today 11/02/14: 31/3/2012: 09/01/1911: Today Thank you and good night! Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Thursday, January 09, 2014 7:56 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula I am not getting your question, so kindly provide me with some examples. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Sent: Thursday, January 09, 2014 7:01 PM Subject: Re: [AI] Excel formula Hi Johar, Thank you. It's working fine. Actually, I want to create a list of today's birthdays and a list of birthdays in that particular months. The formula you suggested is creating a list of today's birthdays. I understood after seeing the output. So in this formula I want a remark 'Today' and not the particular month. Secondly, I want a remark 'particular month' when the birthday is in that particular month. So the remark should be the name of that month ignoring the day and year. Is it possible? Thank you for solution. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Thursday, January 09, 2014 12:01 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula You can acheave this by following formula. note I have changed the cell address from P9 to a1. =IF(AND(DAY(TODAY())=DAY(A1), MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," ") zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "Access India" Sent: Wednesday, January 08, 2014 10:20 AM Subject: [AI] Excel formula Dear friends, Please correct this formula to get correct output. =IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),"January","February","March","April","May","June","July","August","September","October","November","December"),"")) It's working fine if the year is 2014 but not if the year is different. Conditions: If the date is today then only the month should display else blank. The year should be ignore. For example: 08/01/2000: January 01/01/2000: blank cell 08/01/2014: January 01/01/2014: blank cell The date is in column 'p' in dd mm format. In short if the date is current date that is for today '8' then only the month should be display in the output. Please suggest me a correction to get correct output. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Dis
Re: [AI] Excel formula
Please share a formula for me to solve this problem. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "Him Prasad Gautam" Sent: Thursday, January 09, 2014 7:56 PM To: "AccessIndia: a list for discussing accessibility and issues concerningthe disabled." Subject: Re: [AI] Excel formula Hi, It is definitely possible. Just use day and month functions. On 1/9/14, Mukesh Baviskar wrote: Hi Johar, Thank you. It's working fine. Actually, I want to create a list of today's birthdays and a list of birthdays in that particular months. The formula you suggested is creating a list of today's birthdays. I understood after seeing the output. So in this formula I want a remark 'Today' and not the particular month. Secondly, I want a remark 'particular month' when the birthday is in that particular month. So the remark should be the name of that month ignoring the day and year. Is it possible? Thank you for solution. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Thursday, January 09, 2014 12:01 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula You can acheave this by following formula. note I have changed the cell address from P9 to a1. =IF(AND(DAY(TODAY())=DAY(A1), MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," ") zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "Access India" Sent: Wednesday, January 08, 2014 10:20 AM Subject: [AI] Excel formula Dear friends, Please correct this formula to get correct output. =IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),"January","February","March","April","May","June","July","August","September","October","November","December"),"")) It's working fine if the year is 2014 but not if the year is different. Conditions: If the date is today then only the month should display else blank. The year should be ignore. For example: 08/01/2000: January 01/01/2000: blank cell 08/01/2014: January 01/01/2014: blank cell The date is in column 'p' in dd mm format. In short if the date is current date that is for today '8' then only the month should be display in the output. Please suggest me a correction to get correct output. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia li
Re: [AI] Excel formula
I am not getting your question, so kindly provide me with some examples. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Sent: Thursday, January 09, 2014 7:01 PM Subject: Re: [AI] Excel formula Hi Johar, Thank you. It's working fine. Actually, I want to create a list of today's birthdays and a list of birthdays in that particular months. The formula you suggested is creating a list of today's birthdays. I understood after seeing the output. So in this formula I want a remark 'Today' and not the particular month. Secondly, I want a remark 'particular month' when the birthday is in that particular month. So the remark should be the name of that month ignoring the day and year. Is it possible? Thank you for solution. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Thursday, January 09, 2014 12:01 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula You can acheave this by following formula. note I have changed the cell address from P9 to a1. =IF(AND(DAY(TODAY())=DAY(A1), MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," ") zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "Access India" Sent: Wednesday, January 08, 2014 10:20 AM Subject: [AI] Excel formula Dear friends, Please correct this formula to get correct output. =IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),"January","February","March","April","May","June","July","August","September","October","November","December"),"")) It's working fine if the year is 2014 but not if the year is different. Conditions: If the date is today then only the month should display else blank. The year should be ignore. For example: 08/01/2000: January 01/01/2000: blank cell 08/01/2014: January 01/01/2014: blank cell The date is in column 'p' in dd mm format. In short if the date is current date that is for today '8' then only the month should be display in the output. Please suggest me a correction to get correct output. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.acce
Re: [AI] Excel formula
Hi, It is definitely possible. Just use day and month functions. On 1/9/14, Mukesh Baviskar wrote: > Hi Johar, > Thank you. It's working fine. > > Actually, I want to create a list of today's birthdays and a list of > birthdays in that particular months. > > The formula you suggested is creating a list of today's birthdays. I > understood after seeing the output. So in this formula I want a remark > 'Today' and not the particular month. > > Secondly, I want a remark 'particular month' when the birthday is in that > particular month. So the remark should be the name of that month ignoring > the day and year. > > Is it possible? > > Thank you for solution. > > Regards, > > Mukesh Baviskar > Mobile: 9403161157 > > > -- > From: "zoher kheriwala" > Sent: Thursday, January 09, 2014 12:01 PM > To: "AccessIndia: a list for discussing accessibility and issuesconcerning > thedisabled." > Subject: Re: [AI] Excel formula > >> You can acheave this by following formula. >> note I have changed the cell address from P9 to a1. >> =IF(AND(DAY(TODAY())=DAY(A1), >> MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," >> >> ") >> zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala >> - Original Message - >> From: "Mukesh Baviskar" >> To: "Access India" >> Sent: Wednesday, January 08, 2014 10:20 AM >> Subject: [AI] Excel formula >> >> >>> Dear friends, >>> Please correct this formula to get correct output. >>> >>> =IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),"January","February","March","April","May","June","July","August","September","October","November","December"),"")) >>> >>> It's working fine if the year is 2014 but not if the year is different. >>> >>> Conditions: >>> >>> If the date is today then only the month should display else blank. >>> The year should be ignore. >>> For example: >>> >>> 08/01/2000: January >>> 01/01/2000: blank cell >>> 08/01/2014: January >>> 01/01/2014: blank cell >>> The date is in column 'p' in dd mm format. In short if the date is >>> current date that is for today '8' then only the month should be display >>> >>> in the output. >>> >>> Please suggest me a correction to get correct output. >>> >>> Thank you. >>> >>> Regards, >>> >>> Mukesh Baviskar >>> Mobile: 9403161157 >>> Time to meet up again! >>> Register for AccessIndia Convention 2014: >>> http://accessindia.org.in/harish/convention.htm >>> >>> >>> >>> Register at the dedicated AccessIndia list for discussing accessibility >>> of mobile phones / Tabs on: >>> http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in >>> >>> >>> Search for old postings at: >>> http://www.mail-archive.com/accessindia@accessindia.org.in/ >>> >>> To unsubscribe send a message to >>> accessindia-requ...@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: >>> 1. Contents of the mails, factual, or otherwise, reflect the thinking of >>> >>> the person sending the mail and AI in no way relates itself to its >>> veracity; >>> >>> 2. AI cannot be held liable for any commission/omission based on the >>> mails sent through this mailing list.. >> >> >> Time to meet up again! >> Register for AccessIndia Convention 2014: >> http://accessindia.org.in/harish/convention.htm >> >> >> >> Register at the dedicated AccessIndia list for discussing accessibility of >> >> mobile phones / Tabs on: >> http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in >> >> >> Search for old postings at: &
Re: [AI] Excel formula
Hi Johar, Thank you. It's working fine. Actually, I want to create a list of today's birthdays and a list of birthdays in that particular months. The formula you suggested is creating a list of today's birthdays. I understood after seeing the output. So in this formula I want a remark 'Today' and not the particular month. Secondly, I want a remark 'particular month' when the birthday is in that particular month. So the remark should be the name of that month ignoring the day and year. Is it possible? Thank you for solution. Regards, Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Thursday, January 09, 2014 12:01 PM To: "AccessIndia: a list for discussing accessibility and issuesconcerning thedisabled." Subject: Re: [AI] Excel formula You can acheave this by following formula. note I have changed the cell address from P9 to a1. =IF(AND(DAY(TODAY())=DAY(A1), MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," ") zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "Access India" Sent: Wednesday, January 08, 2014 10:20 AM Subject: [AI] Excel formula Dear friends, Please correct this formula to get correct output. =IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),"January","February","March","April","May","June","July","August","September","October","November","December"),"")) It's working fine if the year is 2014 but not if the year is different. Conditions: If the date is today then only the month should display else blank. The year should be ignore. For example: 08/01/2000: January 01/01/2000: blank cell 08/01/2014: January 01/01/2014: blank cell The date is in column 'p' in dd mm format. In short if the date is current date that is for today '8' then only the month should be display in the output. Please suggest me a correction to get correct output. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] Excel formula
You can acheave this by following formula. note I have changed the cell address from P9 to a1. =IF(AND(DAY(TODAY())=DAY(A1), MONTH(TODAY())=MONTH(A1)),CHOOSE(MONTH(A1),"jan","feb","mar","app","may","jun","jul","aug","sep","oct","nov","dec")," ") zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh Baviskar" To: "Access India" Sent: Wednesday, January 08, 2014 10:20 AM Subject: [AI] Excel formula Dear friends, Please correct this formula to get correct output. =IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),"January","February","March","April","May","June","July","August","September","October","November","December"),"")) It's working fine if the year is 2014 but not if the year is different. Conditions: If the date is today then only the month should display else blank. The year should be ignore. For example: 08/01/2000: January 01/01/2000: blank cell 08/01/2014: January 01/01/2014: blank cell The date is in column 'p' in dd mm format. In short if the date is current date that is for today '8' then only the month should be display in the output. Please suggest me a correction to get correct output. Thank you. Regards, Mukesh Baviskar Mobile: 9403161157 Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] Excel formula
You have one condition, but in formula you have two if statements. compare the month and day value with today but not today() as a whole. take date and month parameter. On 1/8/14, Mukesh Baviskar wrote: > Dear friends, > Please correct this formula to get correct output. > > =IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),"January","February","March","April","May","June","July","August","September","October","November","December"),"")) > > It's working fine if the year is 2014 but not if the year is different. > > Conditions: > > If the date is today then only the month should display else blank. > The year should be ignore. > For example: > > 08/01/2000: January > 01/01/2000: blank cell > 08/01/2014: January > 01/01/2014: blank cell > The date is in column 'p' in dd mm format. In short if the date is > current date that is for today '8' then only the month should be display in > the output. > > Please suggest me a correction to get correct output. > > Thank you. > > Regards, > > Mukesh Baviskar > Mobile: 9403161157 > Time to meet up again! > Register for AccessIndia Convention 2014: > http://accessindia.org.in/harish/convention.htm > > > > Register at the dedicated AccessIndia list for discussing accessibility of > mobile phones / Tabs on: > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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: > 1. Contents of the mails, factual, or otherwise, reflect the thinking of the > person sending the mail and AI in no way relates itself to its veracity; > > 2. AI cannot be held liable for any commission/omission based on the mails > sent through this mailing list.. > -- Him Prasad Gautam Kathmandu, Nepal Time to meet up again! Register for AccessIndia Convention 2014: http://accessindia.org.in/harish/convention.htm Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] excel formula
Dear Prashanth, The sample salary sheet is attached. Go through it and make necessary changes in rate of da, hra and so on. It will also calculate your income tax automatically. I think this will fulfill your requirement. Have a nice day. Regards Mukesh Baviskar Mobile 9403161157 -- From: "Prasanth P.V." Sent: Sunday, August 11, 2013 2:26 AM To: "Dedicated AccessIndia list for discussing accessibility of mobile phonesand Tabs." Subject: Re: [AI] excel formula Explain the formula. can we edit in case of change in da or deductions? On 8/10/13, Mukesh wrote: Dear Prasanth, Why not? It's possible beyond your expectation. If you simply type your basic pay then all the head entries will be automatically fill up with a formula as I did for my salary sheet. Do send me a sample salary sheet with your column headings and I will do it for you today. Required information: Rate of DA in percentage Rate of HRA in percentage Grade pay City allowance Travelling allowance Deductions: Provident fund (PF) LIC premium Loan: Society, Housing, recovery, other deductions from salary as per your salary sheet Income tax monthly deduction Do send me the information at the earliest and I will do it for you today without fail. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Prasanth P.V." Sent: Saturday, August 10, 2013 1:26 AM To: "accessindia" ; "bsftb" Subject: [AI] excell formula Friends, how to apply a formula in an excell worksheet to all the rows in it? I mean I've to prepare my monthly sallary sheet. I'll input the basic pay da and such data in each row for each month.I should get the sum of every row without typing the formula in each of the row. How can i do it? -- Prasanth. True friendship consists not innmultitude of friends, but in their worth> and value> Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. -- Prasanth. True friendship consists not innmultitude of friends, but in their worth> and value> Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@accessindia.org.in with the subject unsubscribe.
Re: [AI] excel formula
Explain the formula. can we edit in case of change in da or deductions? On 8/10/13, Mukesh wrote: > Dear Prasanth, > Why not? It's possible beyond your expectation. If you simply type your > basic pay then all the head entries will be automatically fill up with a > formula as I did for my salary sheet. Do send me a sample salary sheet with > > your column headings and I will do it for you today. > > Required information: > > Rate of DA in percentage > Rate of HRA in percentage > Grade pay > City allowance > Travelling allowance > > Deductions: > > Provident fund (PF) > LIC premium > Loan: Society, Housing, recovery, other deductions from salary as per your > salary sheet > Income tax monthly deduction > > Do send me the information at the earliest and I will do it for you today > without fail. > > Regards > > Mukesh Baviskar > Mobile: 9403161157 > > > -- > From: "Prasanth P.V." > Sent: Saturday, August 10, 2013 1:26 AM > To: "accessindia" ; "bsftb" > > Subject: [AI] excell formula > >> Friends, how to apply a formula in an excell worksheet to all the rows >> in it? I mean I've to prepare my monthly sallary sheet. I'll input the >> basic pay da and such data in each row for each month.I should get the >> sum of every row without typing the formula in each of the row. How >> can i do it? >> >> -- >> Prasanth. >>> True friendship consists not innmultitude of friends, but in their worth> >>> >>> and value> >> >> Register at the dedicated AccessIndia list for discussing accessibility of >> >> mobile phones / Tabs on: >> http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in >> >> >> Search for old postings at: >> http://www.mail-archive.com/accessindia@accessindia.org.in/ >> >> To unsubscribe send a message to >> accessindia-requ...@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: >> 1. Contents of the mails, factual, or otherwise, reflect the thinking of >> the person sending the mail and AI in no way relates itself to its >> veracity; >> >> 2. AI cannot be held liable for any commission/omission based on the mails >> >> sent through this mailing list.. > > > Register at the dedicated AccessIndia list for discussing accessibility of > mobile phones / Tabs on: > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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: > 1. Contents of the mails, factual, or otherwise, reflect the thinking of the > person sending the mail and AI in no way relates itself to its veracity; > > 2. AI cannot be held liable for any commission/omission based on the mails > sent through this mailing list.. > -- Prasanth. > True friendship consists not innmultitude of friends, but in their worth> and > value> Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] excel formula
Dear Prasanth, Why not? It's possible beyond your expectation. If you simply type your basic pay then all the head entries will be automatically fill up with a formula as I did for my salary sheet. Do send me a sample salary sheet with your column headings and I will do it for you today. Required information: Rate of DA in percentage Rate of HRA in percentage Grade pay City allowance Travelling allowance Deductions: Provident fund (PF) LIC premium Loan: Society, Housing, recovery, other deductions from salary as per your salary sheet Income tax monthly deduction Do send me the information at the earliest and I will do it for you today without fail. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Prasanth P.V." Sent: Saturday, August 10, 2013 1:26 AM To: "accessindia" ; "bsftb" Subject: [AI] excell formula Friends, how to apply a formula in an excell worksheet to all the rows in it? I mean I've to prepare my monthly sallary sheet. I'll input the basic pay da and such data in each row for each month.I should get the sum of every row without typing the formula in each of the row. How can i do it? -- Prasanth. True friendship consists not innmultitude of friends, but in their worth> and value> Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list.. Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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: 1. Contents of the mails, factual, or otherwise, reflect the thinking of the person sending the mail and AI in no way relates itself to its veracity; 2. AI cannot be held liable for any commission/omission based on the mails sent through this mailing list..
Re: [AI] Excel formula problem
Hello Zujar, Thank you for your response. Actually there is no data in excel sheet to find because I have inserted the formulas for each date and event of that date. It displays only when the particular date is current. Second thing of conditional formatting that I know the process of conditional formatting. But it is not applicable here because the cell address of bank holidays vary from month to month. For example 26th January is a bank holiday but 26th February is not a bank holiday. The cell address of both 26ths is changed in respective months. In this case how can I decide in which cell I should use conditional formatting. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Wednesday, January 02, 2013 4:40 PM To: Subject: Re: [AI] Excel formula problem You need not have to creat any search box, as if excel has the feature of find and replace dialog box. it is sufficient enough to search any kind of data including the date. 1 good thing about the find and replace is , no matter whatever the format of date is entered but you have to type the date in find dialog box in dd-mm- format. for coloring of bank holidays you have to use conditional formatting. to activate conditional formatting press alt+h,l. select = from the submenu, and enter the name of the holiday and select the color and press ok. repeat these steps for all holidays in a year. so whenever the cell shows the name of that holiday the color of that cell will change to the color you specifide. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh" To: Sent: Wednesday, January 02, 2013 11:45 AM Subject: Re: [AI] Excel formula problem Thank you Zujar. It's working fine. Now I want to know how to create a search box to find the details of a particular date. How to change the color of bank holidays to red automatically. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Zujar Shabbir Kanchwala" Sent: Wednesday, January 02, 2013 10:05 AM To: Subject: Re: [AI] Excel formula problem If you change the display format then you should change the formula too. Check the below formula that produces correct result for "dd mm " format mentioned in your email: =IF(TEXT(A1, "dd mm ") = "31 12 2012", "year end", "") Note that this formula would work well for the year 2012 and no others. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Tuesday, January 01, 2013 9:34 PM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Hello zujar, Thank you. It's working fine. I tried to do it in 'dd mm ' format but I didn't get the appropriate result. I don't know why. If the date is in in 'dd mm ' format then how to get the result. for ex. 1-1-2013 I enter 'value' instead of 'text' in formula but in vain. Regards Mukesh Baviskar Mobile: 9403161157 -------------- From: "Zujar Shabbir Kanchwala" Sent: Monday, December 31, 2012 12:43 PM To: Subject: Re: [AI] Excel formula problem Mukeshji, The following formula in cell C1 should work for you: =IF(TEXT(A1, " ") = "December 2012", "year end", "") Note the use of the format string with 4 m characters for full month name and y character 4 full year. You can use the same string to specify the Custom Format instead of the one with 9 m. The fourth m tells Excel to display full month names. Sameer, The issue is not the date entered in A1 but the way Excel treats date strings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message----- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Sameer Sent: Monday, December 31, 2012 10:25 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Fo
Re: [AI] Excel formula problem
Thank you Johar. It's working fine. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "zoher kheriwala" Sent: Wednesday, January 02, 2013 10:16 AM To: Subject: Re: [AI] Excel formula problem Suppose you type the date in cell a1, 01-01-12. Now you want the result in b1, if a1 = 01-01-12, then b1 should show happy new year. To do this task use the following formula in b1. =IF(TEXT(A1,"dd ")="01 january 2013","happy new year","") You can coppy this formula from mail and paste it in a excel sheet and try the result. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh" To: Sent: Tuesday, January 01, 2013 9:33 PM Subject: Re: [AI] Excel formula problem Hello zujar, Thank you. It's working fine. I tried to do it in 'dd mm ' format but I didn't get the appropriate result. I don't know why. If the date is in in 'dd mm ' format then how to get the result. for ex. 1-1-2013 I enter 'value' instead of 'text' in formula but in vain. Regards Mukesh Baviskar Mobile: 9403161157 ------------------ From: "Zujar Shabbir Kanchwala" Sent: Monday, December 31, 2012 12:43 PM To: Subject: Re: [AI] Excel formula problem Mukeshji, The following formula in cell C1 should work for you: =IF(TEXT(A1, " ") = "December 2012", "year end", "") Note the use of the format string with 4 m characters for full month name and y character 4 full year. You can use the same string to specify the Custom Format instead of the one with 9 m. The fourth m tells Excel to display full month names. Sameer, The issue is not the date entered in A1 but the way Excel treats date strings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Sameer Sent: Monday, December 31, 2012 10:25 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Format of cell 'a1' is 'custom' 'm ' date format. Regards Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.ma
Re: [AI] Excel formula problem
You're right. I mentioned the formula to match the format specified by Mukesh. "mm" stands for month in digits while "mmm" and "" stand for month name in short and full respectively. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of zoher kheriwala Sent: Wednesday, January 02, 2013 10:28 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear juzer, the formula mentioned by you in previous mail also holds good the only change that the mukesh has to do is, instead of wrighting " " wright "dd " and in true argument he has to include the date "31 december 2012", it will also work fine. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Zujar Shabbir Kanchwala" To: Sent: Wednesday, January 02, 2013 10:05 AM Subject: Re: [AI] Excel formula problem > If you change the display format then you should change the formula too. > Check the below formula that produces correct result for "dd mm " > format > mentioned in your email: > > =IF(TEXT(A1, "dd mm ") = "31 12 2012", "year end", "") > > Note that this formula would work well for the year 2012 and no others. > > -- > Regards, > Zujar... > > An optimist laughs to forget, whereas a pessimist forgets to laugh! > > > > > -Original Message- > From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On > Behalf Of Mukesh > Sent: Tuesday, January 01, 2013 9:34 PM > To: accessindia@accessindia.org.in > Subject: Re: [AI] Excel formula problem > > Hello zujar, > Thank you. It's working fine. I tried to do it in 'dd mm ' format > but I didn't get the appropriate result. I don't know why. If the date > is in in 'dd mm ' format then how to get the result. for ex. > 1-1-2013 I enter 'value' instead of 'text' in formula but in vain. > > Regards > > Mukesh Baviskar > Mobile: 9403161157 > > -- > From: "Zujar Shabbir Kanchwala" > Sent: Monday, December 31, 2012 12:43 PM > To: > Subject: Re: [AI] Excel formula problem > >> Mukeshji, >> >> The following formula in cell C1 should work for you: >> >> =IF(TEXT(A1, " ") = "December 2012", "year end", "") >> >> Note the use of the format string with 4 m characters for full month >> name and y character 4 full year. You can use the same string to >> specify the Custom Format instead of the one with 9 m. The fourth m >> tells Excel to display full month names. >> >> Sameer, >> The issue is not the date entered in A1 but the way Excel treats date >> strings. >> >> -- >> Regards, >> Zujar... >> >> An optimist laughs to forget, whereas a pessimist forgets to laugh! >> >> >> >> -Original Message- >> From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On >> Behalf Of Sameer >> Sent: Monday, December 31, 2012 10:25 AM >> To: accessindia@accessindia.org.in >> Subject: Re: [AI] Excel formula problem >> >> >> Dear Friend, >> >> Try changing the formula in cell C1 to 31/03/2012 instead of December >> 2012. >> >> Regards >> Mr. Sameer Latey >> Mumbai, India >> - Original Message - >> From: "Mukesh" >> To: "Access India" >> Sent: Sunday, December 30, 2012 10:58 PM >> Subject: [AI] Excel formula problem >> >> >>> Dear Friends, >>> In my excel sheet I have created a a table. Cell 'a1' has a formula >>> '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year >>> end","")' but I am not getting the desire result in cell 'c1'. >>> Please check it and give me the solution at the earliest. Thank you. >>> Format of cell 'a1' is 'custom' 'm ' date format. >>> >>> Regards >>> >>> Mukesh Baviskar >>> Mobile: 9403161157 >>> Register at the dedicated AccessIndia list for discussing >>> accessibility of >> >>> mobile phones / Tabs on: >>> >> > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_acc > essind >> ia.org.in >>> >&
Re: [AI] Excel formula problem
You need not have to creat any search box, as if excel has the feature of find and replace dialog box. it is sufficient enough to search any kind of data including the date. 1 good thing about the find and replace is , no matter whatever the format of date is entered but you have to type the date in find dialog box in dd-mm- format. for coloring of bank holidays you have to use conditional formatting. to activate conditional formatting press alt+h,l. select = from the submenu, and enter the name of the holiday and select the color and press ok. repeat these steps for all holidays in a year. so whenever the cell shows the name of that holiday the color of that cell will change to the color you specifide. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh" To: Sent: Wednesday, January 02, 2013 11:45 AM Subject: Re: [AI] Excel formula problem Thank you Zujar. It's working fine. Now I want to know how to create a search box to find the details of a particular date. How to change the color of bank holidays to red automatically. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Zujar Shabbir Kanchwala" Sent: Wednesday, January 02, 2013 10:05 AM To: Subject: Re: [AI] Excel formula problem If you change the display format then you should change the formula too. Check the below formula that produces correct result for "dd mm " format mentioned in your email: =IF(TEXT(A1, "dd mm ") = "31 12 2012", "year end", "") Note that this formula would work well for the year 2012 and no others. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Tuesday, January 01, 2013 9:34 PM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Hello zujar, Thank you. It's working fine. I tried to do it in 'dd mm ' format but I didn't get the appropriate result. I don't know why. If the date is in in 'dd mm ' format then how to get the result. for ex. 1-1-2013 I enter 'value' instead of 'text' in formula but in vain. Regards Mukesh Baviskar Mobile: 9403161157 ------------------ From: "Zujar Shabbir Kanchwala" Sent: Monday, December 31, 2012 12:43 PM To: Subject: Re: [AI] Excel formula problem Mukeshji, The following formula in cell C1 should work for you: =IF(TEXT(A1, " ") = "December 2012", "year end", "") Note the use of the format string with 4 m characters for full month name and y character 4 full year. You can use the same string to specify the Custom Format instead of the one with 9 m. The fourth m tells Excel to display full month names. Sameer, The issue is not the date entered in A1 but the way Excel treats date strings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message----- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Sameer Sent: Monday, December 31, 2012 10:25 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Format of cell 'a1' is 'custom' 'm ' date format. Regards Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_access
Re: [AI] Excel formula problem
Thank you Zujar. It's working fine. Now I want to know how to create a search box to find the details of a particular date. How to change the color of bank holidays to red automatically. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Zujar Shabbir Kanchwala" Sent: Wednesday, January 02, 2013 10:05 AM To: Subject: Re: [AI] Excel formula problem If you change the display format then you should change the formula too. Check the below formula that produces correct result for "dd mm " format mentioned in your email: =IF(TEXT(A1, "dd mm ") = "31 12 2012", "year end", "") Note that this formula would work well for the year 2012 and no others. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Tuesday, January 01, 2013 9:34 PM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Hello zujar, Thank you. It's working fine. I tried to do it in 'dd mm ' format but I didn't get the appropriate result. I don't know why. If the date is in in 'dd mm ' format then how to get the result. for ex. 1-1-2013 I enter 'value' instead of 'text' in formula but in vain. Regards Mukesh Baviskar Mobile: 9403161157 ------------------ From: "Zujar Shabbir Kanchwala" Sent: Monday, December 31, 2012 12:43 PM To: Subject: Re: [AI] Excel formula problem Mukeshji, The following formula in cell C1 should work for you: =IF(TEXT(A1, " ") = "December 2012", "year end", "") Note the use of the format string with 4 m characters for full month name and y character 4 full year. You can use the same string to specify the Custom Format instead of the one with 9 m. The fourth m tells Excel to display full month names. Sameer, The issue is not the date entered in A1 but the way Excel treats date strings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Sameer Sent: Monday, December 31, 2012 10:25 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Format of cell 'a1' is 'custom' 'm ' date format. Regards Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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_ac
Re: [AI] Excel formula problem
Dear juzer, the formula mentioned by you in previous mail also holds good the only change that the mukesh has to do is, instead of wrighting " " wright "dd " and in true argument he has to include the date "31 december 2012", it will also work fine. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Zujar Shabbir Kanchwala" To: Sent: Wednesday, January 02, 2013 10:05 AM Subject: Re: [AI] Excel formula problem If you change the display format then you should change the formula too. Check the below formula that produces correct result for "dd mm " format mentioned in your email: =IF(TEXT(A1, "dd mm ") = "31 12 2012", "year end", "") Note that this formula would work well for the year 2012 and no others. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Tuesday, January 01, 2013 9:34 PM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Hello zujar, Thank you. It's working fine. I tried to do it in 'dd mm ' format but I didn't get the appropriate result. I don't know why. If the date is in in 'dd mm ' format then how to get the result. for ex. 1-1-2013 I enter 'value' instead of 'text' in formula but in vain. Regards Mukesh Baviskar Mobile: 9403161157 ---------- From: "Zujar Shabbir Kanchwala" Sent: Monday, December 31, 2012 12:43 PM To: Subject: Re: [AI] Excel formula problem Mukeshji, The following formula in cell C1 should work for you: =IF(TEXT(A1, " ") = "December 2012", "year end", "") Note the use of the format string with 4 m characters for full month name and y character 4 full year. You can use the same string to specify the Custom Format instead of the one with 9 m. The fourth m tells Excel to display full month names. Sameer, The issue is not the date entered in A1 but the way Excel treats date strings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -----Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Sameer Sent: Monday, December 31, 2012 10:25 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Format of cell 'a1' is 'custom' 'm ' date format. Regards Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@accessindia.org.in with the subject unsubscribe. To change your subscription to digest mode or make any other
Re: [AI] Excel formula problem
Suppose you type the date in cell a1, 01-01-12. Now you want the result in b1, if a1 = 01-01-12, then b1 should show happy new year. To do this task use the following formula in b1. =IF(TEXT(A1,"dd ")="01 january 2013","happy new year","") You can coppy this formula from mail and paste it in a excel sheet and try the result. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh" To: Sent: Tuesday, January 01, 2013 9:33 PM Subject: Re: [AI] Excel formula problem Hello zujar, Thank you. It's working fine. I tried to do it in 'dd mm ' format but I didn't get the appropriate result. I don't know why. If the date is in in 'dd mm ' format then how to get the result. for ex. 1-1-2013 I enter 'value' instead of 'text' in formula but in vain. Regards Mukesh Baviskar Mobile: 9403161157 -------------- From: "Zujar Shabbir Kanchwala" Sent: Monday, December 31, 2012 12:43 PM To: Subject: Re: [AI] Excel formula problem Mukeshji, The following formula in cell C1 should work for you: =IF(TEXT(A1, " ") = "December 2012", "year end", "") Note the use of the format string with 4 m characters for full month name and y character 4 full year. You can use the same string to specify the Custom Format instead of the one with 9 m. The fourth m tells Excel to display full month names. Sameer, The issue is not the date entered in A1 but the way Excel treats date strings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Sameer Sent: Monday, December 31, 2012 10:25 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Format of cell 'a1' is 'custom' 'm ' date format. Regards Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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://a
Re: [AI] Excel formula problem
If you change the display format then you should change the formula too. Check the below formula that produces correct result for "dd mm " format mentioned in your email: =IF(TEXT(A1, "dd mm ") = "31 12 2012", "year end", "") Note that this formula would work well for the year 2012 and no others. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Tuesday, January 01, 2013 9:34 PM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Hello zujar, Thank you. It's working fine. I tried to do it in 'dd mm ' format but I didn't get the appropriate result. I don't know why. If the date is in in 'dd mm ' format then how to get the result. for ex. 1-1-2013 I enter 'value' instead of 'text' in formula but in vain. Regards Mukesh Baviskar Mobile: 9403161157 -------------- From: "Zujar Shabbir Kanchwala" Sent: Monday, December 31, 2012 12:43 PM To: Subject: Re: [AI] Excel formula problem > Mukeshji, > > The following formula in cell C1 should work for you: > > =IF(TEXT(A1, " ") = "December 2012", "year end", "") > > Note the use of the format string with 4 m characters for full month > name and y character 4 full year. You can use the same string to > specify the Custom Format instead of the one with 9 m. The fourth m > tells Excel to display full month names. > > Sameer, > The issue is not the date entered in A1 but the way Excel treats date > strings. > > -- > Regards, > Zujar... > > An optimist laughs to forget, whereas a pessimist forgets to laugh! > > > > -Original Message- > From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On > Behalf Of Sameer > Sent: Monday, December 31, 2012 10:25 AM > To: accessindia@accessindia.org.in > Subject: Re: [AI] Excel formula problem > > > Dear Friend, > > Try changing the formula in cell C1 to 31/03/2012 instead of December > 2012. > > Regards > Mr. Sameer Latey > Mumbai, India > - Original Message - > From: "Mukesh" > To: "Access India" > Sent: Sunday, December 30, 2012 10:58 PM > Subject: [AI] Excel formula problem > > >> Dear Friends, >> In my excel sheet I have created a a table. Cell 'a1' has a formula >> '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year >> end","")' but I am not getting the desire result in cell 'c1'. Please >> check it and give me the solution at the earliest. Thank you. >> Format of cell 'a1' is 'custom' 'm ' date format. >> >> Regards >> >> Mukesh Baviskar >> Mobile: 9403161157 >> Register at the dedicated AccessIndia list for discussing accessibility >> of > >> mobile phones / Tabs on: >> > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind > ia.org.in >> >> >> Search for old postings at: >> http://www.mail-archive.com/accessindia@accessindia.org.in/ >> >> To unsubscribe send a message to >> accessindia-requ...@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 >> > > > Register at the dedicated AccessIndia list for discussing accessibility of > mobile phones / Tabs on: > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind > ia.org.in > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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 > > > Register at the dedicated AccessIndia list for discussing accessibility of > mobile phones / Tabs on: > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@accessindia.org.in > with the subject
Re: [AI] Excel formula problem
Hello zujar, Thank you. It's working fine. I tried to do it in 'dd mm ' format but I didn't get the appropriate result. I don't know why. If the date is in in 'dd mm ' format then how to get the result. for ex. 1-1-2013 I enter 'value' instead of 'text' in formula but in vain. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Zujar Shabbir Kanchwala" Sent: Monday, December 31, 2012 12:43 PM To: Subject: Re: [AI] Excel formula problem Mukeshji, The following formula in cell C1 should work for you: =IF(TEXT(A1, " ") = "December 2012", "year end", "") Note the use of the format string with 4 m characters for full month name and y character 4 full year. You can use the same string to specify the Custom Format instead of the one with 9 m. The fourth m tells Excel to display full month names. Sameer, The issue is not the date entered in A1 but the way Excel treats date strings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Sameer Sent: Monday, December 31, 2012 10:25 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Format of cell 'a1' is 'custom' 'm ' date format. Regards Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula problem
Hello Zujar, Thank you. It's working fine and giving me the appropriate result. Please suggest me a method how to change the colour of bank holidays to red in calendar for each month respectively. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Zujar Shabbir Kanchwala" Sent: Monday, December 31, 2012 12:43 PM To: Subject: Re: [AI] Excel formula problem Mukeshji, The following formula in cell C1 should work for you: =IF(TEXT(A1, " ") = "December 2012", "year end", "") Note the use of the format string with 4 m characters for full month name and y character 4 full year. You can use the same string to specify the Custom Format instead of the one with 9 m. The fourth m tells Excel to display full month names. Sameer, The issue is not the date entered in A1 but the way Excel treats date strings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Sameer Sent: Monday, December 31, 2012 10:25 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Format of cell 'a1' is 'custom' 'm ' date format. Regards Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula problem
Dear Sameer, Thank you. But still it is not working. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Sameer" Sent: Monday, December 31, 2012 10:25 AM To: Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Format of cell 'a1' is 'custom' 'm ' date format. Regards Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula problem
Mukeshji, The following formula in cell C1 should work for you: =IF(TEXT(A1, " ") = "December 2012", "year end", "") Note the use of the format string with 4 m characters for full month name and y character 4 full year. You can use the same string to specify the Custom Format instead of the one with 9 m. The fourth m tells Excel to display full month names. Sameer, The issue is not the date entered in A1 but the way Excel treats date strings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Sameer Sent: Monday, December 31, 2012 10:25 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula problem Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem > Dear Friends, > In my excel sheet I have created a a table. Cell 'a1' has a formula > '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year > end","")' but I am not getting the desire result in cell 'c1'. Please > check it and give me the solution at the earliest. Thank you. > Format of cell 'a1' is 'custom' 'm ' date format. > > Regards > > Mukesh Baviskar > Mobile: 9403161157 > Register at the dedicated AccessIndia list for discussing accessibility of > mobile phones / Tabs on: > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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 > Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula problem
Dear Friend, Try changing the formula in cell C1 to 31/03/2012 instead of December 2012. Regards Mr. Sameer Latey Mumbai, India - Original Message - From: "Mukesh" To: "Access India" Sent: Sunday, December 30, 2012 10:58 PM Subject: [AI] Excel formula problem Dear Friends, In my excel sheet I have created a a table. Cell 'a1' has a formula '=today()' and cell 'c1' has a formula '=IF(A1="December 2012","year end","")' but I am not getting the desire result in cell 'c1'. Please check it and give me the solution at the earliest. Thank you. Format of cell 'a1' is 'custom' 'm ' date format. Regards Mukesh Baviskar Mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
Problem Statement 1: if any value in cell 'b2' starts with a letter 'd' the cell value of cell 'a1' should change in 0. In other words the value of cell 'a1' should not accept any value except 0, validation required. Solution: The Amount column is supposed to contain 0 if Bill/Receipt Number starts with the letter "d". The solution suggested by Aliasgar is a formula that evaluates to 0 if the said condition is true. This solution forces the user to remember that he has to enter the formula every time he makes an entry with "d" in Bill/Receipt Number column. Why is a formula required if the user knows that the Amount column has to be 0. I believe what you are looking for is a validation such that it doesn't allow the user to enter any non-zero value in the Amount column when Bill/Receipt Number starts with the letter "d". Here's the validation to perform on the entire Amount column to avoid such mistakes: 1. Select cells that need be validated for data entry. 2. Press ALT followed by V V to launch the Data Validation dialog box. 3. On the Settings tab of the dialog box, press TAB to reach the Allow: list box and select Custom. 4. TAB to the Formula edit box and enter the below formula: =OR(AND(LEFT(A2, 1) = "d", B2 = 0), AND(LEFT(A2, 1) <> "d", B2 <> 0)) 5. Click OK and you're done. Note that this validation would also ensure that non zero value is entered in cells where the Bill/Receipt Number doesn't start with "d". You may edit the formula if this validation is not required. Problem Statement 2: Populate list of blocks based on selection of district from the districts list. Solution: This one is also a case for data validation. We would make use of dependent lists here. I suggest creating lists on a separate worksheet for ease of use and avoiding confusions later. 1. Open a blank sheet (we shall call it as Sheet2) and create a list of districts in cells A1:A35. 2. Select the range A1:A35 and name the range as District. 3. Create a list of blocks for the first district as mentioned in the districts list - say Mumbai in the cell range B1:B5. 4. Name this cell range B1:B5 by the name of the district, Mumbai for this example. 5. Repeat steps 3 and 4 in columns C, D, E and so on to specify blocks for the rest of the districts. 6. Switch to the worksheet containing data (probably Sheet1). 7. Select the cells in the District column and apply the following Data Validation: a. Allow = List b. List Source = =District 8. Select the cells in the Block column and apply the following Data Validation: a. Allow = List b. List Source = =INDIRECT(A2) 9. Click OK and see the magic. Note that A2 in Step 8b refers to the first cell containing district name and also you should specify the list source as a formula starting with the equals to sign in steps 7b and 8b. I believe I've understood your problem statements correctly and the solutions work for you. Let me know if more inputs required. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Aliasgar Fakhri Sent: Saturday, December 01, 2012 10:03 PM To: Mukesh Cc: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula Hi Mukesh, working is very simple. But its description is little cumbersome.(Mix of Range and Data Validation) Pls call me. We will discuss and will let u know, how to create it. Regards. 98191 52110 On Sat, Dec 1, 2012 at 3:19 PM, Mukesh wrote: > Excellent! It's working fine. I have one more query as follows: > > There are 35 districts in Maharashtra. > There are 5-17 blocks in each district. > That mean total blocks are around 350. > It is very difficult to create a dropdown list for blocks in a cell. > Because it will take a long time to select a particular block. > Therefore I want to create a dropdown list of blocks related to the > district. That mean when I select a district in cell 'a1' the blocks > of that particular district should come in cell 'b1' as a dropdown list. > For example: > cell 'a1' Nashik then cell 'b1' should have a dropdown list of the > blocks in Nashik district only. and so on. > Guidance please. Thank you. > > Regards > > Mukesh Baviskar > Mobile: 9403161157 > Skype id: mukeshbaviskar > > > --** > From: "Aliasgar Fakhri" > Sent: Saturday, December 01, 2012 12:09 PM > To: > > Subject: Re: [AI] Excel formula > > > Hi, pls try out the following. >> Do let me know if any other assistance is required. >> >> =IF(LEFT(B3,1)="d",0,"") >> >> Above formula in
Re: [AI] Excel formula
Hi Mukesh, working is very simple. But its description is little cumbersome.(Mix of Range and Data Validation) Pls call me. We will discuss and will let u know, how to create it. Regards. 98191 52110 On Sat, Dec 1, 2012 at 3:19 PM, Mukesh wrote: > Excellent! It's working fine. I have one more query as follows: > > There are 35 districts in Maharashtra. > There are 5-17 blocks in each district. > That mean total blocks are around 350. > It is very difficult to create a dropdown list for blocks in a cell. > Because it will take a long time to select a particular block. > Therefore I want to create a dropdown list of blocks related to the > district. That mean when I select a district in cell 'a1' the blocks of > that particular district should come in cell 'b1' as a dropdown list. > For example: > cell 'a1' Nashik then cell 'b1' should have a dropdown list of the blocks > in Nashik district only. and so on. > Guidance please. Thank you. > > Regards > > Mukesh Baviskar > Mobile: 9403161157 > Skype id: mukeshbaviskar > > > --**------------ > From: "Aliasgar Fakhri" > Sent: Saturday, December 01, 2012 12:09 PM > To: > > Subject: Re: [AI] Excel formula > > > Hi, pls try out the following. >> Do let me know if any other assistance is required. >> >> =IF(LEFT(B3,1)="d",0,"") >> >> Above formula in cell a3 states, that for any alphanumerical value in cell >> b3, if it starts with d ( its leftmost character ), the value to be >> returned will be 0. >> If it does not satisfy the criteria, then "Null" value would be presented, >> indicating Blank field. >> >> Regards. >> >> >> >> On Sat, Dec 1, 2012 at 8:27 AM, Mukesh >> wrote: >> >> Dear Friends, >>> Please suggest/correct the formula. >>> Condition: if any value in cell 'b2' starts with a letter 'd' the cell >>> value of cell 'a1' should change in 0. In other words the value of cell >>> 'a1' should not accept any value except 0, validation required. >>> >>> formula: >>> =if(a2,"=any value starting with a letter 'd'","0") >>> >>> It's very urgent so please suggest/correct this formula at the earliest. >>> Waiting for your guidance at the earliest. Thank you. >>> >>> Regards >>> >>> Mukesh Baviskar >>> Mobile: 0943161157 >>> Skype id: mukeshbaviskar >>> Register at the dedicated AccessIndia list for discussing accessibility >>> of >>> mobile phones / Tabs on: >>> >>> http://mail.accessindia.org.**in/mailman/listinfo/mobile.** >>> accessindia_accessindia.org.in<http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in> >>> >>> >>> Search for old postings at: >>> http://www.mail-archive.com/**accessin...@accessindia.org.**in/<http://www.mail-archive.com/accessindia@accessindia.org.in/> >>> >>> To unsubscribe send a message to >>> accessindia-request@**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<http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in> >>> >>> >>> >> >> -- >> Regards, >> AliAsgar H.Fakhri >> Register at the dedicated AccessIndia list for discussing accessibility >> of mobile phones / Tabs on: >> http://mail.accessindia.org.**in/mailman/listinfo/mobile.** >> accessindia_accessindia.org.in<http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in> >> >> >> Search for old postings at: >> http://www.mail-archive.com/**accessin...@accessindia.org.**in/<http://www.mail-archive.com/accessindia@accessindia.org.in/> >> >> To unsubscribe send a message to >> accessindia-request@**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<http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in> >> >> -- Regards, AliAsgar H.Fakhri Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
small correction - probably shivaranjini men't you would need to visit the excel help to know all the formulas in use. -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Shivaranjini Ranjini Sent: 01 December 2012 15:27 To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula On 12/1/12, Aliasgar Fakhri wrote: > Hi, pls try out the following. > Do let me know if any other assistance is required. > > =IF(LEFT(B3,1)="d",0,"") > > Above formula in cell a3 states, that for any alphanumerical value in > cell b3, if it starts with d ( its leftmost character ), the value to > be returned will be 0. > If it does not satisfy the criteria, then "Null" value would be > presented, indicating Blank field. > > Regards. > > > > On Sat, Dec 1, 2012 at 8:27 AM, Mukesh > wrote: > >> Dear Friends, >> Please suggest/correct the formula. >> Condition: if any value in cell 'b2' starts with a letter 'd' the >> cell value of cell 'a1' should change in 0. In other words the value >> of cell 'a1' should not accept any value except 0, validation required. >> >> formula: >> =if(a2,"=any value starting with a letter 'd'","0") >> >> It's very urgent so please suggest/correct this formula at the earliest. >> Waiting for your guidance at the earliest. Thank you. >> >> Regards >> >> Mukesh Baviskar >> Mobile: 0943161157 >> Skype id: mukeshbaviskar >> Register at the dedicated AccessIndia list for discussing >> accessibility of mobile phones / Tabs on: >> >> http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_ac >> cessindia.org.in >> >> >> Search for old postings at: >> http://www.mail-archive.com/accessindia@accessindia.org.in/ >> >> To unsubscribe send a message to >> accessindia-requ...@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.or >> g.in >> >> > > > -- > Regards, > AliAsgar H.Fakhri > Register at the dedicated AccessIndia list for discussing > accessibility of mobile phones / Tabs on: > http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_acc > essindia.org.in > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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 > > you want all excel formullas? you go to jaws help and search there Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessind ia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
Excellent! It's working fine. I have one more query as follows: There are 35 districts in Maharashtra. There are 5-17 blocks in each district. That mean total blocks are around 350. It is very difficult to create a dropdown list for blocks in a cell. Because it will take a long time to select a particular block. Therefore I want to create a dropdown list of blocks related to the district. That mean when I select a district in cell 'a1' the blocks of that particular district should come in cell 'b1' as a dropdown list. For example: cell 'a1' Nashik then cell 'b1' should have a dropdown list of the blocks in Nashik district only. and so on. Guidance please. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Skype id: mukeshbaviskar -- From: "Aliasgar Fakhri" Sent: Saturday, December 01, 2012 12:09 PM To: Subject: Re: [AI] Excel formula Hi, pls try out the following. Do let me know if any other assistance is required. =IF(LEFT(B3,1)="d",0,"") Above formula in cell a3 states, that for any alphanumerical value in cell b3, if it starts with d ( its leftmost character ), the value to be returned will be 0. If it does not satisfy the criteria, then "Null" value would be presented, indicating Blank field. Regards. On Sat, Dec 1, 2012 at 8:27 AM, Mukesh wrote: Dear Friends, Please suggest/correct the formula. Condition: if any value in cell 'b2' starts with a letter 'd' the cell value of cell 'a1' should change in 0. In other words the value of cell 'a1' should not accept any value except 0, validation required. formula: =if(a2,"=any value starting with a letter 'd'","0") It's very urgent so please suggest/correct this formula at the earliest. Waiting for your guidance at the earliest. Thank you. Regards Mukesh Baviskar Mobile: 0943161157 Skype id: mukeshbaviskar Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 -- Regards, AliAsgar H.Fakhri Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
Sir, Thanks for your advice.very nice. Thanks & Regards. JP MISHRA On Tue, Nov 13, 2012 at 9:01 AM, Umesha Economics wrote: > go to excel help and index tab, type "if" and press enter. you will be > reaching the page on "if worksheet function". the explanation there is more > than enough. > > Umesha > > - Original Message - From: "Jai Prakas Mishra" < > jpmishra1...@gmail.com> > To: > > Sent: Monday, 12 November, 2012 9:32 AM > Subject: Re: [AI] Excel formula > > > Sir, >> >> Please send me how to use "IF" statement. >> >> Thanks >> JP MISHRA >> >> On Sun, Nov 11, 2012 at 12:12 PM, Zujar Shabbir Kanchwala < >> zujarbri...@gmail.com> wrote: >> >> Oh! So the Input Message is dependent on the value in other cell. Thanks >>> Zoher for bringing out the point. >>> >>> The Input Message cannot be conditional. An alternative is to make it >>> descriptive for users to understand. >>> >>> -- >>> Regards, >>> Zujar... >>> >>> An optimist laughs to forget, whereas a pessimist forgets to laugh! >>> >>> >>> >>> -Original Message- >>> From: AccessIndia >>> [mailto:accessindia-bounces@**accessindia.org.in] >>> On >>> Behalf >>> Of zoher kheriwala >>> Sent: Sunday, November 11, 2012 10:30 AM >>> To: accessindia@accessindia.org.in >>> Subject: Re: [AI] Excel formula >>> >>> I am aware of putting the input text, but according to mukesh, if d13 is >>> grater then k12 then b13 should not accept ay data. In this scenario the >>> error message that mukesh wants to display is "pls enter the value less >>> then >>> k12" is sounds illogical to me. Because the data validation condition for >>> b13 is not decided by the data you are entering in cell b13 but it is >>> decided by the comparison of 2 other cells d13 and k12. So in order to >>> accept the data by cell b13 the d13 has to be = to or less then k12. In >>> this >>> condition b13 can accept any value no matter grater or less then k12. I >>> hope >>> I was clear enough to explain my argument. >>> >>> zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala >>> - Original Message - >>> From: "Zujar Shabbir Kanchwala" >>> To: > >>> Sent: Saturday, November 10, 2012 10:31 PM >>> Subject: Re: [AI] Excel formula >>> >>> >>> > Mukeshji - Autosum is an Excel feature that helps inserting the SUM() >>> > function in the active cell. It cannot be used in a formula. As far you >>> > need >>> > to implement a formula on cell ranges of different sizes you may >>> select > a >>> > larger range while entering the formula for the first time and then > >>> copy >>> > it >>> > at various locations. Anyway this method is not foolproof and you may >>> need >>> > to verify the results and rewrite the formula with the correct range >>> where >>> > there are issues. Time consuming and tedious - but that's what comes to >>> my >>> > mind at present. >>> > >>> > Zoher - The Data Validation dialog box has three tabs - Validation, > >>> Input >>> > Message and Error Alert. You specify the validation on the first tab - >>> > mostly people just use this one and don't explore the others. The Input >>> > Message is displayed near the cell when the cell is highlighted. JAWS >>> > speaks >>> > it well and is useful to hint about what valid input that the cell can >>> > accept. Error Alert is used to specify a custom error message in case >>> > the >>> > user enters an invalid value in the cell. >>> > >>> > To specify an Input Message, open the Data Validation dialog box, > >>> switch >>> > to >>> > the Input Message tab using CTRL+TAB, specify the Heading for the Input >>> > Message and the Message itself and hit the OK button. >>> > >>> > -- >>> > Regards, >>> > Zujar... >>> > >>> > An optimist laughs to forget, whereas a pessimist forgets to laugh! >>> > >>> > >>> > >>> > -Original Message- >>> > From: AccessIndia >>> > [mailto:accessindia-bounces@**accessin
Re: [AI] Excel formula
go to excel help and index tab, type "if" and press enter. you will be reaching the page on "if worksheet function". the explanation there is more than enough. Umesha - Original Message - From: "Jai Prakas Mishra" To: Sent: Monday, 12 November, 2012 9:32 AM Subject: Re: [AI] Excel formula Sir, Please send me how to use "IF" statement. Thanks JP MISHRA On Sun, Nov 11, 2012 at 12:12 PM, Zujar Shabbir Kanchwala < zujarbri...@gmail.com> wrote: Oh! So the Input Message is dependent on the value in other cell. Thanks Zoher for bringing out the point. The Input Message cannot be conditional. An alternative is to make it descriptive for users to understand. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of zoher kheriwala Sent: Sunday, November 11, 2012 10:30 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula I am aware of putting the input text, but according to mukesh, if d13 is grater then k12 then b13 should not accept ay data. In this scenario the error message that mukesh wants to display is "pls enter the value less then k12" is sounds illogical to me. Because the data validation condition for b13 is not decided by the data you are entering in cell b13 but it is decided by the comparison of 2 other cells d13 and k12. So in order to accept the data by cell b13 the d13 has to be = to or less then k12. In this condition b13 can accept any value no matter grater or less then k12. I hope I was clear enough to explain my argument. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Zujar Shabbir Kanchwala" To: Sent: Saturday, November 10, 2012 10:31 PM Subject: Re: [AI] Excel formula > Mukeshji - Autosum is an Excel feature that helps inserting the SUM() > function in the active cell. It cannot be used in a formula. As far you > need > to implement a formula on cell ranges of different sizes you may select > a > larger range while entering the formula for the first time and then > copy > it > at various locations. Anyway this method is not foolproof and you may need > to verify the results and rewrite the formula with the correct range where > there are issues. Time consuming and tedious - but that's what comes to my > mind at present. > > Zoher - The Data Validation dialog box has three tabs - Validation, > Input > Message and Error Alert. You specify the validation on the first tab - > mostly people just use this one and don't explore the others. The Input > Message is displayed near the cell when the cell is highlighted. JAWS > speaks > it well and is useful to hint about what valid input that the cell can > accept. Error Alert is used to specify a custom error message in case > the > user enters an invalid value in the cell. > > To specify an Input Message, open the Data Validation dialog box, > switch > to > the Input Message tab using CTRL+TAB, specify the Heading for the Input > Message and the Message itself and hit the OK button. > > -- > Regards, > Zujar... > > An optimist laughs to forget, whereas a pessimist forgets to laugh! > > > > -Original Message----- > From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On > Behalf > Of Mukesh > Sent: Saturday, November 10, 2012 5:21 PM > To: accessindia@accessindia.org.in > Subject: Re: [AI] Excel formula > > Hello Johar, > Thank you. My first problem is solved. In second problem I have a > little > modification as follows because each time the cell renge is different. The > data entries may more or less each time. So I don't want to stick to a > particular cell range. Therefore I want to use auto sum formula so that > the > problem of different cell range will solve. Is it possible to give a > auto > sum formula in this case? > Require a formula as follows: > > if cell b13 has a text 'Closing balance' the cell c13 should have a > auto > sum > formula. > > Example: =if(b13="Closing balance",sum()) > > This formula is giving error. Please correct it. > > Thank you. > > Regards > > Mukesh Baviskar > Mobile: 0943161157 > > -- > From: "zoher kheriwala" > Sent: Saturday, November 10, 2012 12:13 PM > To: > Subject: Re: [AI] Excel formula > >> You can achieve your target by following formulas. >> >> Q1: If cell b13 has a text 'Closing balance', The cell k13 should have >> a formula 'j13-i13' >> >> Answer: =if(b13="closing balance&qu
Re: [AI] Excel formula
Sir, Please send me how to use "IF" statement. Thanks JP MISHRA On Sun, Nov 11, 2012 at 12:12 PM, Zujar Shabbir Kanchwala < zujarbri...@gmail.com> wrote: > Oh! So the Input Message is dependent on the value in other cell. Thanks > Zoher for bringing out the point. > > The Input Message cannot be conditional. An alternative is to make it > descriptive for users to understand. > > -- > Regards, > Zujar... > > An optimist laughs to forget, whereas a pessimist forgets to laugh! > > > > -Original Message- > From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On > Behalf > Of zoher kheriwala > Sent: Sunday, November 11, 2012 10:30 AM > To: accessindia@accessindia.org.in > Subject: Re: [AI] Excel formula > > I am aware of putting the input text, but according to mukesh, if d13 is > grater then k12 then b13 should not accept ay data. In this scenario the > error message that mukesh wants to display is "pls enter the value less > then > k12" is sounds illogical to me. Because the data validation condition for > b13 is not decided by the data you are entering in cell b13 but it is > decided by the comparison of 2 other cells d13 and k12. So in order to > accept the data by cell b13 the d13 has to be = to or less then k12. In > this > condition b13 can accept any value no matter grater or less then k12. I > hope > I was clear enough to explain my argument. > > zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala > - Original Message - > From: "Zujar Shabbir Kanchwala" > To: > Sent: Saturday, November 10, 2012 10:31 PM > Subject: Re: [AI] Excel formula > > > > Mukeshji - Autosum is an Excel feature that helps inserting the SUM() > > function in the active cell. It cannot be used in a formula. As far you > > need > > to implement a formula on cell ranges of different sizes you may select a > > larger range while entering the formula for the first time and then copy > > it > > at various locations. Anyway this method is not foolproof and you may > need > > to verify the results and rewrite the formula with the correct range > where > > there are issues. Time consuming and tedious - but that's what comes to > my > > mind at present. > > > > Zoher - The Data Validation dialog box has three tabs - Validation, Input > > Message and Error Alert. You specify the validation on the first tab - > > mostly people just use this one and don't explore the others. The Input > > Message is displayed near the cell when the cell is highlighted. JAWS > > speaks > > it well and is useful to hint about what valid input that the cell can > > accept. Error Alert is used to specify a custom error message in case the > > user enters an invalid value in the cell. > > > > To specify an Input Message, open the Data Validation dialog box, switch > > to > > the Input Message tab using CTRL+TAB, specify the Heading for the Input > > Message and the Message itself and hit the OK button. > > > > -- > > Regards, > > Zujar... > > > > An optimist laughs to forget, whereas a pessimist forgets to laugh! > > > > > > > > -Original Message- > > From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On > > Behalf > > Of Mukesh > > Sent: Saturday, November 10, 2012 5:21 PM > > To: accessindia@accessindia.org.in > > Subject: Re: [AI] Excel formula > > > > Hello Johar, > > Thank you. My first problem is solved. In second problem I have a little > > modification as follows because each time the cell renge is different. > The > > data entries may more or less each time. So I don't want to stick to a > > particular cell range. Therefore I want to use auto sum formula so that > > the > > problem of different cell range will solve. Is it possible to give a auto > > sum formula in this case? > > Require a formula as follows: > > > > if cell b13 has a text 'Closing balance' the cell c13 should have a auto > > sum > > formula. > > > > Example: =if(b13="Closing balance",sum()) > > > > This formula is giving error. Please correct it. > > > > Thank you. > > > > Regards > > > > Mukesh Baviskar > > Mobile: 0943161157 > > > > -- > > From: "zoher kheriwala" > > Sent: Saturday, November 10, 2012 12:13 PM > > To: > > Subject: Re: [AI] Excel formula > > > >> You can achieve your target by following formulas
Re: [AI] Excel formula
Oh! So the Input Message is dependent on the value in other cell. Thanks Zoher for bringing out the point. The Input Message cannot be conditional. An alternative is to make it descriptive for users to understand. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of zoher kheriwala Sent: Sunday, November 11, 2012 10:30 AM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula I am aware of putting the input text, but according to mukesh, if d13 is grater then k12 then b13 should not accept ay data. In this scenario the error message that mukesh wants to display is "pls enter the value less then k12" is sounds illogical to me. Because the data validation condition for b13 is not decided by the data you are entering in cell b13 but it is decided by the comparison of 2 other cells d13 and k12. So in order to accept the data by cell b13 the d13 has to be = to or less then k12. In this condition b13 can accept any value no matter grater or less then k12. I hope I was clear enough to explain my argument. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Zujar Shabbir Kanchwala" To: Sent: Saturday, November 10, 2012 10:31 PM Subject: Re: [AI] Excel formula > Mukeshji - Autosum is an Excel feature that helps inserting the SUM() > function in the active cell. It cannot be used in a formula. As far you > need > to implement a formula on cell ranges of different sizes you may select a > larger range while entering the formula for the first time and then copy > it > at various locations. Anyway this method is not foolproof and you may need > to verify the results and rewrite the formula with the correct range where > there are issues. Time consuming and tedious - but that's what comes to my > mind at present. > > Zoher - The Data Validation dialog box has three tabs - Validation, Input > Message and Error Alert. You specify the validation on the first tab - > mostly people just use this one and don't explore the others. The Input > Message is displayed near the cell when the cell is highlighted. JAWS > speaks > it well and is useful to hint about what valid input that the cell can > accept. Error Alert is used to specify a custom error message in case the > user enters an invalid value in the cell. > > To specify an Input Message, open the Data Validation dialog box, switch > to > the Input Message tab using CTRL+TAB, specify the Heading for the Input > Message and the Message itself and hit the OK button. > > -- > Regards, > Zujar... > > An optimist laughs to forget, whereas a pessimist forgets to laugh! > > > > -Original Message- > From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On > Behalf > Of Mukesh > Sent: Saturday, November 10, 2012 5:21 PM > To: accessindia@accessindia.org.in > Subject: Re: [AI] Excel formula > > Hello Johar, > Thank you. My first problem is solved. In second problem I have a little > modification as follows because each time the cell renge is different. The > data entries may more or less each time. So I don't want to stick to a > particular cell range. Therefore I want to use auto sum formula so that > the > problem of different cell range will solve. Is it possible to give a auto > sum formula in this case? > Require a formula as follows: > > if cell b13 has a text 'Closing balance' the cell c13 should have a auto > sum > formula. > > Example: =if(b13="Closing balance",sum()) > > This formula is giving error. Please correct it. > > Thank you. > > Regards > > Mukesh Baviskar > Mobile: 0943161157 > > -- > From: "zoher kheriwala" > Sent: Saturday, November 10, 2012 12:13 PM > To: > Subject: Re: [AI] Excel formula > >> You can achieve your target by following formulas. >> >> Q1: If cell b13 has a text 'Closing balance', The cell k13 should have >> a formula 'j13-i13' >> >> Answer: =if(b13="closing balance",j13-i13) >> >> Q 2: If cell b13 has a text 'Closing balance', The cell i13 should >> have a formula 'sum(i5:i12)' >> >> Answer: =if(b13="closing balance",sum(i5:i12)) >> >> Q: 3 a: If cell d13 is greater than k12, The cell d13 should not >> accept the value >> >> Answer: step 1: go to b13 >> >> Step 2: press alt+v, v >> >> Step 3: from allow combo box select custom. >> >> Step 4: press tab and type the formula =k12<=d13 >
Re: [AI] Excel formula
I am aware of putting the input text, but according to mukesh, if d13 is grater then k12 then b13 should not accept ay data. In this scenario the error message that mukesh wants to display is "pls enter the value less then k12" is sounds illogical to me. Because the data validation condition for b13 is not decided by the data you are entering in cell b13 but it is decided by the comparison of 2 other cells d13 and k12. So in order to accept the data by cell b13 the d13 has to be = to or less then k12. In this condition b13 can accept any value no matter grater or less then k12. I hope I was clear enough to explain my argument. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Zujar Shabbir Kanchwala" To: Sent: Saturday, November 10, 2012 10:31 PM Subject: Re: [AI] Excel formula Mukeshji - Autosum is an Excel feature that helps inserting the SUM() function in the active cell. It cannot be used in a formula. As far you need to implement a formula on cell ranges of different sizes you may select a larger range while entering the formula for the first time and then copy it at various locations. Anyway this method is not foolproof and you may need to verify the results and rewrite the formula with the correct range where there are issues. Time consuming and tedious - but that's what comes to my mind at present. Zoher - The Data Validation dialog box has three tabs - Validation, Input Message and Error Alert. You specify the validation on the first tab - mostly people just use this one and don't explore the others. The Input Message is displayed near the cell when the cell is highlighted. JAWS speaks it well and is useful to hint about what valid input that the cell can accept. Error Alert is used to specify a custom error message in case the user enters an invalid value in the cell. To specify an Input Message, open the Data Validation dialog box, switch to the Input Message tab using CTRL+TAB, specify the Heading for the Input Message and the Message itself and hit the OK button. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Saturday, November 10, 2012 5:21 PM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula Hello Johar, Thank you. My first problem is solved. In second problem I have a little modification as follows because each time the cell renge is different. The data entries may more or less each time. So I don't want to stick to a particular cell range. Therefore I want to use auto sum formula so that the problem of different cell range will solve. Is it possible to give a auto sum formula in this case? Require a formula as follows: if cell b13 has a text 'Closing balance' the cell c13 should have a auto sum formula. Example: =if(b13="Closing balance",sum()) This formula is giving error. Please correct it. Thank you. Regards Mukesh Baviskar Mobile: 0943161157 -- From: "zoher kheriwala" Sent: Saturday, November 10, 2012 12:13 PM To: Subject: Re: [AI] Excel formula You can achieve your target by following formulas. Q1: If cell b13 has a text 'Closing balance', The cell k13 should have a formula 'j13-i13' Answer: =if(b13="closing balance",j13-i13) Q 2: If cell b13 has a text 'Closing balance', The cell i13 should have a formula 'sum(i5:i12)' Answer: =if(b13="closing balance",sum(i5:i12)) Q: 3 a: If cell d13 is greater than k12, The cell d13 should not accept the value Answer: step 1: go to b13 Step 2: press alt+v, v Step 3: from allow combo box select custom. Step 4: press tab and type the formula =k12<=d13 It means it will not accept the value if d 13 is > then k12. step 5: press tab and go to ok button. Q 3b: show a inpute message 'enter a value less than k12' Answer: This part is not clear to me, because if the value of d13 is >then k12, then it will not allow the data to be entered. No matter what you want to enter in cell b 13. If you want to enter the data, then you must have the value of d13 = to or < then k12. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "mukesh baviskar" To: "accessindia" Sent: Friday, November 09, 2012 10:48 PM Subject: [AI] Excel formula Hello Friends, I want to create a formula for following: column b13: Closing balance Column i13: withdrawal Column j13: deposit Formula required: 1. If cell b13 has a text 'Closing balance', The cell k13 should have a formula 'j13-i13' 2, If cell b13 has a text 'Closing balance', The cell i13 should have a formula 'sum(i5:i12)' 3. If cell d13 i
Re: [AI] Excel formula
Hello Zujar, Thank you. Now I understand that the auto sum function cannot be done automatically. We have to do it manually each time to avoide wrong results due to cell range. Only we can insert a inpute message in that particular cell to use auto sum function here when the cell b13 contains the text 'Closing balance'. Regards Mukesh Baviskar On 11/10/12, Zujar Shabbir Kanchwala wrote: > Mukeshji - Autosum is an Excel feature that helps inserting the SUM() > function in the active cell. It cannot be used in a formula. As far you > need > to implement a formula on cell ranges of different sizes you may select a > larger range while entering the formula for the first time and then copy it > at various locations. Anyway this method is not foolproof and you may need > to verify the results and rewrite the formula with the correct range where > there are issues. Time consuming and tedious - but that's what comes to my > mind at present. > > Zoher - The Data Validation dialog box has three tabs - Validation, Input > Message and Error Alert. You specify the validation on the first tab - > mostly people just use this one and don't explore the others. The Input > Message is displayed near the cell when the cell is highlighted. JAWS > speaks > it well and is useful to hint about what valid input that the cell can > accept. Error Alert is used to specify a custom error message in case the > user enters an invalid value in the cell. > > To specify an Input Message, open the Data Validation dialog box, switch to > the Input Message tab using CTRL+TAB, specify the Heading for the Input > Message and the Message itself and hit the OK button. > > -- > Regards, > Zujar... > > An optimist laughs to forget, whereas a pessimist forgets to laugh! > > > > -Original Message- > From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf > Of Mukesh > Sent: Saturday, November 10, 2012 5:21 PM > To: accessindia@accessindia.org.in > Subject: Re: [AI] Excel formula > > Hello Johar, > Thank you. My first problem is solved. In second problem I have a little > modification as follows because each time the cell renge is different. The > data entries may more or less each time. So I don't want to stick to a > particular cell range. Therefore I want to use auto sum formula so that the > problem of different cell range will solve. Is it possible to give a auto > sum formula in this case? > Require a formula as follows: > > if cell b13 has a text 'Closing balance' the cell c13 should have a auto > sum > formula. > > Example: =if(b13="Closing balance",sum()) > > This formula is giving error. Please correct it. > > Thank you. > > Regards > > Mukesh Baviskar > Mobile: 0943161157 > > -- > From: "zoher kheriwala" > Sent: Saturday, November 10, 2012 12:13 PM > To: > Subject: Re: [AI] Excel formula > >> You can achieve your target by following formulas. >> >> Q1: If cell b13 has a text 'Closing balance', The cell k13 should have >> a formula 'j13-i13' >> >> Answer: =if(b13="closing balance",j13-i13) >> >> Q 2: If cell b13 has a text 'Closing balance', The cell i13 should >> have a formula 'sum(i5:i12)' >> >> Answer: =if(b13="closing balance",sum(i5:i12)) >> >> Q: 3 a: If cell d13 is greater than k12, The cell d13 should not >> accept the value >> >> Answer: step 1: go to b13 >> >> Step 2: press alt+v, v >> >> Step 3: from allow combo box select custom. >> >> Step 4: press tab and type the formula =k12<=d13 >> >> It means it will not accept the value if d 13 is > then k12. >> >> step 5: press tab and go to ok button. >> >> Q 3b: show a inpute message 'enter a value less than k12' >> >> Answer: This part is not clear to me, because if the value of d13 is >> >then k12, then it will not allow the data to be entered. No matter >> what you want to enter in cell b 13. If you want to enter the data, >> then you must have the value of d13 = to or < then k12. >> >> zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala >> - Original Message - >> From: "mukesh baviskar" >> To: "accessindia" >> Sent: Friday, November 09, 2012 10:48 PM >> Subject: [AI] Excel formula >> >> >>> Hello Friends, >>> I want to create a formula for following: >>> column b13: Closing balance >>> Column i13: withdrawal >>> Column j13: deposi
Re: [AI] Excel formula
Mukeshji - Autosum is an Excel feature that helps inserting the SUM() function in the active cell. It cannot be used in a formula. As far you need to implement a formula on cell ranges of different sizes you may select a larger range while entering the formula for the first time and then copy it at various locations. Anyway this method is not foolproof and you may need to verify the results and rewrite the formula with the correct range where there are issues. Time consuming and tedious - but that's what comes to my mind at present. Zoher - The Data Validation dialog box has three tabs - Validation, Input Message and Error Alert. You specify the validation on the first tab - mostly people just use this one and don't explore the others. The Input Message is displayed near the cell when the cell is highlighted. JAWS speaks it well and is useful to hint about what valid input that the cell can accept. Error Alert is used to specify a custom error message in case the user enters an invalid value in the cell. To specify an Input Message, open the Data Validation dialog box, switch to the Input Message tab using CTRL+TAB, specify the Heading for the Input Message and the Message itself and hit the OK button. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Saturday, November 10, 2012 5:21 PM To: accessindia@accessindia.org.in Subject: Re: [AI] Excel formula Hello Johar, Thank you. My first problem is solved. In second problem I have a little modification as follows because each time the cell renge is different. The data entries may more or less each time. So I don't want to stick to a particular cell range. Therefore I want to use auto sum formula so that the problem of different cell range will solve. Is it possible to give a auto sum formula in this case? Require a formula as follows: if cell b13 has a text 'Closing balance' the cell c13 should have a auto sum formula. Example: =if(b13="Closing balance",sum()) This formula is giving error. Please correct it. Thank you. Regards Mukesh Baviskar Mobile: 0943161157 -- From: "zoher kheriwala" Sent: Saturday, November 10, 2012 12:13 PM To: Subject: Re: [AI] Excel formula > You can achieve your target by following formulas. > > Q1: If cell b13 has a text 'Closing balance', The cell k13 should have > a formula 'j13-i13' > > Answer: =if(b13="closing balance",j13-i13) > > Q 2: If cell b13 has a text 'Closing balance', The cell i13 should > have a formula 'sum(i5:i12)' > > Answer: =if(b13="closing balance",sum(i5:i12)) > > Q: 3 a: If cell d13 is greater than k12, The cell d13 should not > accept the value > > Answer: step 1: go to b13 > > Step 2: press alt+v, v > > Step 3: from allow combo box select custom. > > Step 4: press tab and type the formula =k12<=d13 > > It means it will not accept the value if d 13 is > then k12. > > step 5: press tab and go to ok button. > > Q 3b: show a inpute message 'enter a value less than k12' > > Answer: This part is not clear to me, because if the value of d13 is > >then k12, then it will not allow the data to be entered. No matter > what you want to enter in cell b 13. If you want to enter the data, > then you must have the value of d13 = to or < then k12. > > zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala > - Original Message - > From: "mukesh baviskar" > To: "accessindia" > Sent: Friday, November 09, 2012 10:48 PM > Subject: [AI] Excel formula > > >> Hello Friends, >> I want to create a formula for following: >> column b13: Closing balance >> Column i13: withdrawal >> Column j13: deposit >> >> Formula required: >> 1. If cell b13 has a text 'Closing balance', The cell k13 should have >> a formula 'j13-i13' >> 2, If cell b13 has a text 'Closing balance', The cell i13 should have >> a formula 'sum(i5:i12)' >> 3. If cell d13 is greater than k12, The cell d13 should not accept >> the value and show a inpute message 'enter a value less than k12' >> >> Please suggest me a formula to to do this. Thanking you in anticipation. >> >> Regards >> >> Mukesh Baviskar >> Mobile: 09403161157 >> >> >> Search for old postings at: >> http://www.mail-archive.com/accessindia@accessindia.org.in/ >> >> To unsubscribe send a message to >> accessindia-requ...@accessindia.org.in >> with the subject unsubscribe. >&g
Re: [AI] Excel formula
Hello Johar, Thank you. My first problem is solved. In second problem I have a little modification as follows because each time the cell renge is different. The data entries may more or less each time. So I don't want to stick to a particular cell range. Therefore I want to use auto sum formula so that the problem of different cell range will solve. Is it possible to give a auto sum formula in this case? Require a formula as follows: if cell b13 has a text 'Closing balance' the cell c13 should have a auto sum formula. Example: =if(b13="Closing balance",sum()) This formula is giving error. Please correct it. Thank you. Regards Mukesh Baviskar Mobile: 0943161157 -- From: "zoher kheriwala" Sent: Saturday, November 10, 2012 12:13 PM To: Subject: Re: [AI] Excel formula You can achieve your target by following formulas. Q1: If cell b13 has a text 'Closing balance', The cell k13 should have a formula 'j13-i13' Answer: =if(b13="closing balance",j13-i13) Q 2: If cell b13 has a text 'Closing balance', The cell i13 should have a formula 'sum(i5:i12)' Answer: =if(b13="closing balance",sum(i5:i12)) Q: 3 a: If cell d13 is greater than k12, The cell d13 should not accept the value Answer: step 1: go to b13 Step 2: press alt+v, v Step 3: from allow combo box select custom. Step 4: press tab and type the formula =k12<=d13 It means it will not accept the value if d 13 is > then k12. step 5: press tab and go to ok button. Q 3b: show a inpute message 'enter a value less than k12' Answer: This part is not clear to me, because if the value of d13 is >then k12, then it will not allow the data to be entered. No matter what you want to enter in cell b 13. If you want to enter the data, then you must have the value of d13 = to or < then k12. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "mukesh baviskar" To: "accessindia" Sent: Friday, November 09, 2012 10:48 PM Subject: [AI] Excel formula Hello Friends, I want to create a formula for following: column b13: Closing balance Column i13: withdrawal Column j13: deposit Formula required: 1. If cell b13 has a text 'Closing balance', The cell k13 should have a formula 'j13-i13' 2, If cell b13 has a text 'Closing balance', The cell i13 should have a formula 'sum(i5:i12)' 3. If cell d13 is greater than k12, The cell d13 should not accept the value and show a inpute message 'enter a value less than k12' Please suggest me a formula to to do this. Thanking you in anticipation. Regards Mukesh Baviskar Mobile: 09403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
You can achieve your target by following formulas. Q1: If cell b13 has a text 'Closing balance', The cell k13 should have a formula 'j13-i13' Answer: =if(b13="closing balance",j13-i13) Q 2: If cell b13 has a text 'Closing balance', The cell i13 should have a formula 'sum(i5:i12)' Answer: =if(b13="closing balance",sum(i5:i12)) Q: 3 a: If cell d13 is greater than k12, The cell d13 should not accept the value Answer: step 1: go to b13 Step 2: press alt+v, v Step 3: from allow combo box select custom. Step 4: press tab and type the formula =k12<=d13 It means it will not accept the value if d 13 is > then k12. step 5: press tab and go to ok button. Q 3b: show a inpute message 'enter a value less than k12' Answer: This part is not clear to me, because if the value of d13 is >then k12, then it will not allow the data to be entered. No matter what you want to enter in cell b 13. If you want to enter the data, then you must have the value of d13 = to or < then k12. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "mukesh baviskar" To: "accessindia" Sent: Friday, November 09, 2012 10:48 PM Subject: [AI] Excel formula Hello Friends, I want to create a formula for following: column b13: Closing balance Column i13: withdrawal Column j13: deposit Formula required: 1. If cell b13 has a text 'Closing balance', The cell k13 should have a formula 'j13-i13' 2, If cell b13 has a text 'Closing balance', The cell i13 should have a formula 'sum(i5:i12)' 3. If cell d13 is greater than k12, The cell d13 should not accept the value and show a inpute message 'enter a value less than k12' Please suggest me a formula to to do this. Thanking you in anticipation. Regards Mukesh Baviskar Mobile: 09403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
sir, please send me a dummy excel template and I will do it. these things can be done using if function and data validation feature. sorry, as I am moving to my village today, I cannot contact you on skype. Umesha - Original Message - From: "mukesh baviskar" To: "accessindia" Sent: Friday, 09 November, 2012 10:48 PM Subject: [AI] Excel formula Hello Friends, I want to create a formula for following: column b13: Closing balance Column i13: withdrawal Column j13: deposit Formula required: 1. If cell b13 has a text 'Closing balance', The cell k13 should have a formula 'j13-i13' 2, If cell b13 has a text 'Closing balance', The cell i13 should have a formula 'sum(i5:i12)' 3. If cell d13 is greater than k12, The cell d13 should not accept the value and show a inpute message 'enter a value less than k12' Please suggest me a formula to to do this. Thanking you in anticipation. Regards Mukesh Baviskar Mobile: 09403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] excel formula correction
Solution for Query 1:- Switch to the "Input Message" page of the Data Validation dialog box using CTRL+TAB and uncheck the check box labeled "Show input message when cell is selected". Solution for Query 2:- You just want to delete data from one of the cells containing a combo box. Press DELETE key to do so. If you want to remove a month from the list then you can delete it from Data Validation settings. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: Mukesh [mailto:mukeshbaviskar@gmail.com] Sent: Wednesday, September 26, 2012 7:13 AM To: accessindia@accessindia.org.in Cc: Zujar Shabbir Kanchwala Subject: Re: [AI] excel formula correction Hello Zujar, Thank you for your guidance. It is working fine but I have a problem, when we go to that particular cell the jaws speaks the formula which I found irritating/unnecessary. How to stop it? Secondly, I have created a dropdown list in excel column 'j' of months. I want to cancel the entry of that month and want to leave it blank. How to cancel the entry, but not dropdown list? Thank you. Regards Mukesh Baviskar -- From: "Zujar Shabbir Kanchwala" Sent: Monday, September 24, 2012 11:40 AM To: Subject: Re: [AI] excel formula correction > Hi Mukeshji, > > The COUNTIF() function can be made use of in data validation for the > range of cells that are supposed to avoid duplicate entries. Here's > the > procedure: > > 1. Select the range of cells that should avoid duplication. I am > selecting > J2:j201 for illustrative purpose > 2. Press ALT+D followed by L to open Data Validation dialog box for > the selected cells. > 3. TAB to the Allow combo box and select Custom. > 4. TAB to the Formula edit box and enter the following formula: > > =COUNTIF($J$2:$J$201, J2) = 1 > > (The spaces in the above formula are just for clarity, they may be > ignored) > > 5. Activate the OK button. > > Note that this method helps you avoid duplication in the given cell > range but doesn't allow other validations on them. I think there's a > better method that can help us avoid duplication as well as perform > other data validation > - please help if someone on the list knows about it. > > -- > Regards, > Zujar... > > An optimist laughs to forget, whereas a pessimist forgets to laugh! > > > > -Original Message- > From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On > Behalf Of Mukesh > Sent: Monday, September 24, 2012 8:04 AM > To: Access India > Subject: Re: [AI] excel formula correction > > > > > From: Mukesh > Sent: Monday, September 24, 2012 5:56 AM > To: Mukesh > Subject: Re: excel formula correction > > > Hello Friends, > Sorry for trouble. Now I realized my mistake in formula and I have > corrected > it successfully. With your kind cooperation and guidance I have completed > my > database today. Now I have a problem of prevent duplicate entry only. If > anyone of you know how to prevent duplicate entry rather than removing it > later using remove duplicate function in excel. Waiting to get a positive > guidance from you to complete my database today. Thank you. > > Regards > > Mukesh Baviskar > Mobile: 9403161157 > > > From: Mukesh > Sent: Sunday, September 23, 2012 8:44 PM > To: Access India > Subject: excel formula correction > > > Dear Friends, > I have created a formula in excel but I found a error in which I can not > identify. Therefore I request all of you to correct my mistake in the > following formula. > > =COUNTIF(AN5:AN1000,"NSKMVIJAN",COUNTIF(AN5:AN1000,"NSKMVIFEB",COUNTIF(AN5:A > N1000,"NSKMVIMAR",COUNTIF(AN5:AN1000,"NSKMVIAPR",COUNTIF(AN5:AN1000,"NSKMVIM > AY",COUNTIF(AN5:AN1000,"NSKMVIJUNE",COUNTIF(AN5:AN1000,"NSKMVIJULY",COUNTIF( > AN5:AN1000,"NSKMVIAUG",COUNTIF(AN5:AN1000,"NSKMVISEPT",COUNTIF(AN5:AN1000,"N > SKMVIOCT",COUNTIF(AN5:AN1000,"NSKMVINOV",COUNTIF(AN5:AN1000,"NSKMVIDEC") > ))) > Waiting for your correction tomorrow. Thank you. > > Regards > > Mukesh Baviskar > Mobile: 9403161157 > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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 > > > > Search for old post
Re: [AI] excel formula correction
Hello Zujar, Thank you for your guidance. It is working fine but I have a problem, when we go to that particular cell the jaws speaks the formula which I found irritating/unnecessary. How to stop it? Secondly, I have created a dropdown list in excel column 'j' of months. I want to cancel the entry of that month and want to leave it blank. How to cancel the entry, but not dropdown list? Thank you. Regards Mukesh Baviskar -- From: "Zujar Shabbir Kanchwala" Sent: Monday, September 24, 2012 11:40 AM To: Subject: Re: [AI] excel formula correction Hi Mukeshji, The COUNTIF() function can be made use of in data validation for the range of cells that are supposed to avoid duplicate entries. Here's the procedure: 1. Select the range of cells that should avoid duplication. I am selecting J2:j201 for illustrative purpose 2. Press ALT+D followed by L to open Data Validation dialog box for the selected cells. 3. TAB to the Allow combo box and select Custom. 4. TAB to the Formula edit box and enter the following formula: =COUNTIF($J$2:$J$201, J2) = 1 (The spaces in the above formula are just for clarity, they may be ignored) 5. Activate the OK button. Note that this method helps you avoid duplication in the given cell range but doesn't allow other validations on them. I think there's a better method that can help us avoid duplication as well as perform other data validation - please help if someone on the list knows about it. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Monday, September 24, 2012 8:04 AM To: Access India Subject: Re: [AI] excel formula correction From: Mukesh Sent: Monday, September 24, 2012 5:56 AM To: Mukesh Subject: Re: excel formula correction Hello Friends, Sorry for trouble. Now I realized my mistake in formula and I have corrected it successfully. With your kind cooperation and guidance I have completed my database today. Now I have a problem of prevent duplicate entry only. If anyone of you know how to prevent duplicate entry rather than removing it later using remove duplicate function in excel. Waiting to get a positive guidance from you to complete my database today. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 From: Mukesh Sent: Sunday, September 23, 2012 8:44 PM To: Access India Subject: excel formula correction Dear Friends, I have created a formula in excel but I found a error in which I can not identify. Therefore I request all of you to correct my mistake in the following formula. =COUNTIF(AN5:AN1000,"NSKMVIJAN",COUNTIF(AN5:AN1000,"NSKMVIFEB",COUNTIF(AN5:A N1000,"NSKMVIMAR",COUNTIF(AN5:AN1000,"NSKMVIAPR",COUNTIF(AN5:AN1000,"NSKMVIM AY",COUNTIF(AN5:AN1000,"NSKMVIJUNE",COUNTIF(AN5:AN1000,"NSKMVIJULY",COUNTIF( AN5:AN1000,"NSKMVIAUG",COUNTIF(AN5:AN1000,"NSKMVISEPT",COUNTIF(AN5:AN1000,"N SKMVIOCT",COUNTIF(AN5:AN1000,"NSKMVINOV",COUNTIF(AN5:AN1000,"NSKMVIDEC") ))) Waiting for your correction tomorrow. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] excel formula
Thank you Mohit. -- From: "Mohit Gupta" Sent: Monday, September 24, 2012 2:33 PM To: Subject: Re: [AI] excel formula hello Friends f you want to prevent duplicate data from being entered into a range, here is a pretty simple solution by using *Data Validation*. 1. Select the range of cells in which you want to prevent duplicates (for example, A1:A50). 2. From the *Data* menu select *Validation*. 3. Click on the *Settings* tab. 4. From the *Allow* dropdown box select *Custom*. 5. In the formula box type *=COUNTIF($A$1:$A$50,A1)=1* *(see explanation below)* 6. Click on the *Error Alert* tab. 7. In the *Title* box type "*Duplicate Entry!*" 8. In the *Error Message* box type "*You cannot enter a value already in the list.*" *Explanation:* This formula tells Excel to count how many times the data from the current cell is included in the validation range. If the answer is equal to 1 then the formula =COUNTIF($A$1:$A$50,A1)=1 is TRUE and everything is OK. However, if the answer is not equal to 1, then the formula is FALSE and an error occurs causing the Error Alert. Instead of typing the cell ranges in the formula, you can select them with the mouse. For the validation range (i.e. A1:A50) select the range, then press the F4 key to make it an absolute reference (includes dollar signs). For the criteria range (i.e. A1) click on the first cell in the validation range (no dollar signs). You do not need to copy this formula. When you select the range first and then apply Data Validation, the validation applies to the whole range. The cell reference you use in the formula is not limited to one column. You can use a range such as $A$1:$C$50. regards CA mohit gupta On Mon, Sep 24, 2012 at 10:37 AM, wrote: Send AccessIndia mailing list submissions to accessindia@accessindia.org.in To subscribe or unsubscribe via the World Wide Web, visit http://mail.accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in or, via email, send a message with subject or body 'help' to accessindia-requ...@accessindia.org.in You can reach the person managing the list at accessindia-ow...@accessindia.org.in When replying, please edit your Subject line so it is more specific than "Re: Contents of AccessIndia digest..." Please do not reply to this digest mail. You should put your comments into a new mail with appropriate subject line. ___ AccessIndia mailing list AccessIndia@accessindia.org.in http://mail.accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in Today's Topics: 1. Hos usd to open an E MAILS in microsoft outlook? (anish.kha...@yahoo.com) 2. Fwd: New Radio services for sensory impaired people. (Wahid Raza) 3. FW: AFB blog post: A Review of the Humanware Communicator App by Scott Davert, Helen Keller National Center: Part 1 (Bhavesh Shah) 4. Re: excel formula correction (Mukesh) 5. Audio Description for the Movie Barfi (Swati Sinha) 6. Google Books Accessibility (with focus on tallyho); Tesseract OCR accuracy specially for Hindi? (Amar Jain) 7. Re: how to restrict youtube from opening (shivshankar.rvm...@gmail.com) 8. Re: Save start up time (Zujar Shabbir Kanchwala) 9. Re: Problem with the login page of gmail. (Zujar Shabbir Kanchwala) -- Message: 1 Date: Sun, 23 Sep 2012 19:05:04 + From: "anish.kha...@yahoo.com" To: "accessindia@accessindia.org.in" Subject: [AI] Hos usd to open an E MAILS in microsoft outlook? Message-ID: <680683.15732...@smtp119-mob.biz.mail.ne1.yahoo.com> Content-Type: TEXT/PLAIN; charset=UTF-8 Sent from my Nokia phone plz tell me procedure -- Message: 2 Date: Mon, 24 Sep 2012 01:25:11 +0530 From: Wahid Raza To: accessindia Cc: list Subject: [AI] Fwd: New Radio services for sensory impaired people. Message-ID: < caejdvbjyiefcrwm6ywdwzhpo1wfumnp+d1knby4-uvsot3n...@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Hi all, hope all are doing fine pasting below article, which get from another list. Regards Wahid -- Forwarded message -- new radio services on horizon NPR, Harris Corporation and Towson University Launch Global Effort To Make Radio Accessible to Hearing and Sight Impaired Tuesday January 8, 12:00 pm ET First Over-The-Air Transmission From Special CES Station LAS VEGAS, Jan. 8 /PRNewswire-FirstCall/ -- (LVCC S227) -- NPR, Harris Corporation and Towson University today announced a new initiative to make radio more accessible to the hundreds of millions of hearing and visually impaired people around the world. At a press conference at the Consumer Electronics Show in Las Vegas, the three organizations announced the global
Re: [AI] excel formula correction
Hello excel master, Thank you for the challenging formula. In my database a column contains 'CRR no' which is given by 'RCI' new Delhi. I want to check days of that particular number from 'rehabcouncil.nic.inn' website. So I want to give a hyperlink to all crr numbers so that clicking on a particular crr no. I will go to the particular teacher's/crr no.'s page to get the required information of him/her directly. Is it possible? If so how? Waiting for your sure guidance eagerly. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Zujar Shabbir Kanchwala" Sent: Monday, September 24, 2012 11:40 AM To: Subject: Re: [AI] excel formula correction Hi Mukeshji, The COUNTIF() function can be made use of in data validation for the range of cells that are supposed to avoid duplicate entries. Here's the procedure: 1. Select the range of cells that should avoid duplication. I am selecting J2:j201 for illustrative purpose 2. Press ALT+D followed by L to open Data Validation dialog box for the selected cells. 3. TAB to the Allow combo box and select Custom. 4. TAB to the Formula edit box and enter the following formula: =COUNTIF($J$2:$J$201, J2) = 1 (The spaces in the above formula are just for clarity, they may be ignored) 5. Activate the OK button. Note that this method helps you avoid duplication in the given cell range but doesn't allow other validations on them. I think there's a better method that can help us avoid duplication as well as perform other data validation - please help if someone on the list knows about it. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Monday, September 24, 2012 8:04 AM To: Access India Subject: Re: [AI] excel formula correction From: Mukesh Sent: Monday, September 24, 2012 5:56 AM To: Mukesh Subject: Re: excel formula correction Hello Friends, Sorry for trouble. Now I realized my mistake in formula and I have corrected it successfully. With your kind cooperation and guidance I have completed my database today. Now I have a problem of prevent duplicate entry only. If anyone of you know how to prevent duplicate entry rather than removing it later using remove duplicate function in excel. Waiting to get a positive guidance from you to complete my database today. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 From: Mukesh Sent: Sunday, September 23, 2012 8:44 PM To: Access India Subject: excel formula correction Dear Friends, I have created a formula in excel but I found a error in which I can not identify. Therefore I request all of you to correct my mistake in the following formula. =COUNTIF(AN5:AN1000,"NSKMVIJAN",COUNTIF(AN5:AN1000,"NSKMVIFEB",COUNTIF(AN5:A N1000,"NSKMVIMAR",COUNTIF(AN5:AN1000,"NSKMVIAPR",COUNTIF(AN5:AN1000,"NSKMVIM AY",COUNTIF(AN5:AN1000,"NSKMVIJUNE",COUNTIF(AN5:AN1000,"NSKMVIJULY",COUNTIF( AN5:AN1000,"NSKMVIAUG",COUNTIF(AN5:AN1000,"NSKMVISEPT",COUNTIF(AN5:AN1000,"N SKMVIOCT",COUNTIF(AN5:AN1000,"NSKMVINOV",COUNTIF(AN5:AN1000,"NSKMVIDEC") ))) Waiting for your correction tomorrow. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] excel formula correction
Hi Mukeshji, The COUNTIF() function can be made use of in data validation for the range of cells that are supposed to avoid duplicate entries. Here's the procedure: 1. Select the range of cells that should avoid duplication. I am selecting J2:j201 for illustrative purpose 2. Press ALT+D followed by L to open Data Validation dialog box for the selected cells. 3. TAB to the Allow combo box and select Custom. 4. TAB to the Formula edit box and enter the following formula: =COUNTIF($J$2:$J$201, J2) = 1 (The spaces in the above formula are just for clarity, they may be ignored) 5. Activate the OK button. Note that this method helps you avoid duplication in the given cell range but doesn't allow other validations on them. I think there's a better method that can help us avoid duplication as well as perform other data validation - please help if someone on the list knows about it. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -Original Message- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Monday, September 24, 2012 8:04 AM To: Access India Subject: Re: [AI] excel formula correction From: Mukesh Sent: Monday, September 24, 2012 5:56 AM To: Mukesh Subject: Re: excel formula correction Hello Friends, Sorry for trouble. Now I realized my mistake in formula and I have corrected it successfully. With your kind cooperation and guidance I have completed my database today. Now I have a problem of prevent duplicate entry only. If anyone of you know how to prevent duplicate entry rather than removing it later using remove duplicate function in excel. Waiting to get a positive guidance from you to complete my database today. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 From: Mukesh Sent: Sunday, September 23, 2012 8:44 PM To: Access India Subject: excel formula correction Dear Friends, I have created a formula in excel but I found a error in which I can not identify. Therefore I request all of you to correct my mistake in the following formula. =COUNTIF(AN5:AN1000,"NSKMVIJAN",COUNTIF(AN5:AN1000,"NSKMVIFEB",COUNTIF(AN5:A N1000,"NSKMVIMAR",COUNTIF(AN5:AN1000,"NSKMVIAPR",COUNTIF(AN5:AN1000,"NSKMVIM AY",COUNTIF(AN5:AN1000,"NSKMVIJUNE",COUNTIF(AN5:AN1000,"NSKMVIJULY",COUNTIF( AN5:AN1000,"NSKMVIAUG",COUNTIF(AN5:AN1000,"NSKMVISEPT",COUNTIF(AN5:AN1000,"N SKMVIOCT",COUNTIF(AN5:AN1000,"NSKMVINOV",COUNTIF(AN5:AN1000,"NSKMVIDEC") ))) Waiting for your correction tomorrow. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] excel formula correction
From: Mukesh Sent: Monday, September 24, 2012 5:56 AM To: Mukesh Subject: Re: excel formula correction Hello Friends, Sorry for trouble. Now I realized my mistake in formula and I have corrected it successfully. With your kind cooperation and guidance I have completed my database today. Now I have a problem of prevent duplicate entry only. If anyone of you know how to prevent duplicate entry rather than removing it later using remove duplicate function in excel. Waiting to get a positive guidance from you to complete my database today. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 From: Mukesh Sent: Sunday, September 23, 2012 8:44 PM To: Access India Subject: excel formula correction Dear Friends, I have created a formula in excel but I found a error in which I can not identify. Therefore I request all of you to correct my mistake in the following formula. =COUNTIF(AN5:AN1000,"NSKMVIJAN",COUNTIF(AN5:AN1000,"NSKMVIFEB",COUNTIF(AN5:AN1000,"NSKMVIMAR",COUNTIF(AN5:AN1000,"NSKMVIAPR",COUNTIF(AN5:AN1000,"NSKMVIMAY",COUNTIF(AN5:AN1000,"NSKMVIJUNE",COUNTIF(AN5:AN1000,"NSKMVIJULY",COUNTIF(AN5:AN1000,"NSKMVIAUG",COUNTIF(AN5:AN1000,"NSKMVISEPT",COUNTIF(AN5:AN1000,"NSKMVIOCT",COUNTIF(AN5:AN1000,"NSKMVINOV",COUNTIF(AN5:AN1000,"NSKMVIDEC" Waiting for your correction tomorrow. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
Hello Vivek, I have solved my problem by using 'if' function. But I would like to learn 'look up' function from you tomorrow. Please illustrate it using following information for me. column a column b name of districts abbreviations Ahmednagar: AH Akola: AK Amravati: AM Aurangabad: AU Beed: BE Bhandara: BH Nashik: NSK Pune: PU Latur: L These are some abbreviations of districts. Please send me a excel file as attachment illustrating the 'look up' function at the earliest. Please also let me know how to avoide duplicate entry from the database in excel. I know the remove duplicate function but instead of removing it manually later I want to prevent duplicate entry while entering the data. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 From: Singhal, Vivek Sent: Saturday, September 22, 2012 11:58 AM To: 'accessindia@accessindia.org.in' Cc: 'mukeshbaviskar@gmail.com' Subject: Excel formula Dear Mukesh, The problem indicated by you can be resolved by using VLOOKUP function in Excel. VLOOKUP function is a short form for Vertical Lookup. This function searches alphabetically in a database. You can therefore do the following: 1.. Create a worksheet called "Master List" or any other name desired by you. You can then input district names in column No. A and their corresponding Abbreviations or Codes in Column B. Remember to sort this alphabetically as VLOOKUP function will not work if the database is not sorted alphabetically. 2.. Once the above database / Master List is ready then use VLOOKUP in your working sheet and provide reference to the above created list. 3.. VLOOKUP function is not a very straight forward function and requires some hands on practice before using it in making worksheet. MS Excel built-in help can be used for the syntax of this function. Please write back in case of further issues. Hope that helps. Regards, Vivek Singhal, My E-mail Address has changed. Kindly use "singha...@indianoil.in" This Message was sent from Indian Oil Messaging Gateway, India. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
Hello Zoher, Thank you for your guidance. It is working fine and giving me the required result. One more query is: I don't want to repeat the entry/value in a particular column in future. For example: Suppose I have enter a number 'a00123' in cell a1, after that next time I enter this number again in cell a100. At this time the error message should display 'this number is already exist' and should not take the entry again. I this way by mistake no one can make a duplicate entry in my database after long time, after 10 years also. Please give me a suggestion for it. Waiting for your positive response with solution. Thank you once again. Regards Mukesh Baviskar -- From: "zoher kheriwala" Sent: Saturday, September 22, 2012 11:11 AM To: Subject: Re: [AI] Excel formula You can achieve this through (if) formula. type the following farmula in the b1 cell. =if(a1="nashik","nsk",if(a1="pune","pn")) hope it will solve your problem. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh" To: Sent: Friday, September 21, 2012 9:59 PM Subject: Re: [AI] Excel formula Hello Ashutosh, Thank you. It is working fine. Now I have one more query: cell a1 cell b1 any name of district abbreviation of district (for example: Nashik: nsk, Pune: pu, Latur: l, etc) My question is: When I type a name of any district in cell a1 the abbreviation of that particular district should come in cell b1 automatically. Suppose I type 'Nashik' in cell a1 the abbreviation of Nashik should automatically come in cell b1 'nsk'. Please guide me at the earliest because I have to complete my database within two/three days. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 ------------------ From: "Aashutosh pandey" Sent: Friday, September 21, 2012 2:59 PM To: Subject: Re: [AI] Excel formula Sir you can use =concatinet() hopeit will work On 9/20/12, Mukesh wrote: Dear Friends, I have created a database in excel. My query is: Columns: a1 b1 c1 d1 Titles: districts vi/si m/F (Combination abbreviation of these three cells should come automatically) Example: Nashik vi m nskmvi How to do this with a formula? Please suggest me a formula at the earliest. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 -- Thanks with best regards Aashutosh Pandey Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
Hello you can use VLookup formula for this Problem of abbreviations Go to Tools - Functions and type in the dialogue box vlookup Regards CA MOHIT GUPTA +91-9415055308 On Sat, Sep 22, 2012 at 12:30 AM, wrote: > Send AccessIndia mailing list submissions to > accessindia@accessindia.org.in > > To subscribe or unsubscribe via the World Wide Web, visit > > http://mail.accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in > > or, via email, send a message with subject or body 'help' to > accessindia-requ...@accessindia.org.in > > You can reach the person managing the list at > accessindia-ow...@accessindia.org.in > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of AccessIndia digest..." > > > Please do not reply to this digest mail. You should put your comments into > a new mail with appropriate subject line. > ___ > AccessIndia mailing list > AccessIndia@accessindia.org.in > > http://mail.accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in > > > Today's Topics: > >1. Re: Problem to open downloaded file (Aashutosh pandey) >2. Re: Excel formula (Mukesh) >3. Re: Excel formula (Aashutosh pandey) >4. Re: how to restrict youtube from opening (Shona Man) >5. Re: how to restrict youtube from opening (Shona Man) > > > -- > > Message: 1 > Date: Fri, 21 Sep 2012 21:11:09 +0530 > From: Aashutosh pandey > To: accessindia@accessindia.org.in > Subject: Re: [AI] Problem to open downloaded file > Message-ID: > nn2lb...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > Dear sir i use internet explorer i don't use any download manager i > searched in c:/downloads or c:,d:,e: but i couldn't get in the morning > i'm able to download in mydocument/downloads but suddenly in afternoon > i'm unable to do like this or is there any other option . > mob:08933845544 > Skype:aashutoshpandey1 > > On 9/21/12, uphar.73 wrote: > > Search in c :/downloads.Here you will get your needs. > > - Original Message - > > From: "Aashutosh pandey" > > To: > > Sent: Friday, September 21, 2012 4:53 PM > > Subject: [AI] Problem to open downloaded file > > > > > >> Hello to all today i downloaded a file but when i was searching in > >> downloads in mydocument i couldn't get that when it was downloading it > >> was sowing that it is downloading in temporaryfolder but i don't know > >> where is temporary folder please help me as soon as you can > >> mob:08933845544 > >> Skype:aashutoshpandey1 > >> > >> -- > >> Thanks with best regards > >> Aashutosh Pandey > >> > >> > >> Search for old postings at: > >> http://www.mail-archive.com/accessindia@accessindia.org.in/ > >> > >> To unsubscribe send a message to > >> accessindia-requ...@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 > >> > > > > > > > > Search for old postings at: > > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > > > To unsubscribe send a message to > > accessindia-requ...@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 > > > > > > > -- > Thanks with best regards > Aashutosh Pandey > > > > -- > > Message: 2 > Date: Fri, 21 Sep 2012 21:59:13 +0530 > From: "Mukesh" > To: > Subject: Re: [AI] Excel formula > Message-ID: > Content-Type: text/plain; format=flowed; charset="iso-8859-1"; > reply-type=original > > Hello Ashutosh, > Thank you. It is working fine. Now I have one more query: > cell a1 cell b1 > any name of district abbreviation of district > (for example: Nashik: nsk, Pune: pu, Latur: l, etc) > My question is: > When I type a name of any district in cell a1 the abbreviation of that > particular district should come in cell b1 automatically. Suppose I type > 'Nashik' in cell a1 the ab
Re: [AI] Excel formula
could you please provide the short cut keys of xl formulate? On 9/22/2012 11:11 AM, zoher kheriwala wrote: You can achieve this through (if) formula. type the following farmula in the b1 cell. =if(a1="nashik","nsk",if(a1="pune","pn")) hope it will solve your problem. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh" To: Sent: Friday, September 21, 2012 9:59 PM Subject: Re: [AI] Excel formula Hello Ashutosh, Thank you. It is working fine. Now I have one more query: cell a1 cell b1 any name of district abbreviation of district (for example: Nashik: nsk, Pune: pu, Latur: l, etc) My question is: When I type a name of any district in cell a1 the abbreviation of that particular district should come in cell b1 automatically. Suppose I type 'Nashik' in cell a1 the abbreviation of Nashik should automatically come in cell b1 'nsk'. Please guide me at the earliest because I have to complete my database within two/three days. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Aashutosh pandey" Sent: Friday, September 21, 2012 2:59 PM To: Subject: Re: [AI] Excel formula Sir you can use =concatinet() hopeit will work On 9/20/12, Mukesh wrote: Dear Friends, I have created a database in excel. My query is: Columns: a1 b1 c1 d1 Titles: districts vi/si m/F (Combination abbreviation of these three cells should come automatically) Example: Nashik vi m nskmvi How to do this with a formula? Please suggest me a formula at the earliest. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 -- Thanks with best regards Aashutosh Pandey Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
You can achieve this through (if) formula. type the following farmula in the b1 cell. =if(a1="nashik","nsk",if(a1="pune","pn")) hope it will solve your problem. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh" To: Sent: Friday, September 21, 2012 9:59 PM Subject: Re: [AI] Excel formula Hello Ashutosh, Thank you. It is working fine. Now I have one more query: cell a1 cell b1 any name of district abbreviation of district (for example: Nashik: nsk, Pune: pu, Latur: l, etc) My question is: When I type a name of any district in cell a1 the abbreviation of that particular district should come in cell b1 automatically. Suppose I type 'Nashik' in cell a1 the abbreviation of Nashik should automatically come in cell b1 'nsk'. Please guide me at the earliest because I have to complete my database within two/three days. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Aashutosh pandey" Sent: Friday, September 21, 2012 2:59 PM To: Subject: Re: [AI] Excel formula Sir you can use =concatinet() hopeit will work On 9/20/12, Mukesh wrote: Dear Friends, I have created a database in excel. My query is: Columns: a1 b1 c1 d1 Titles: districts vi/si m/F (Combination abbreviation of these three cells should come automatically) Example: Nashik vi m nskmvi How to do this with a formula? Please suggest me a formula at the earliest. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 -- Thanks with best regards Aashutosh Pandey Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
I'm very sorry sir i can't help you further because i don't have idia about it but you should send this mail on our mailing list again hear are many expert who will help us please sorry mob:08933845544 Skype:aashutoshpandey1 On 9/21/12, Mukesh wrote: > Hello Ashutosh, > Thank you. It is working fine. Now I have one more query: > cell a1 cell b1 > any name of district abbreviation of district > (for example: Nashik: nsk, Pune: pu, Latur: l, etc) > My question is: > When I type a name of any district in cell a1 the abbreviation of that > particular district should come in cell b1 automatically. Suppose I type > 'Nashik' in cell a1 the abbreviation of Nashik should automatically come in > > cell b1 'nsk'. Please guide me at the earliest because I have to complete my > > database within two/three days. Thank you. > > Regards > > Mukesh Baviskar > Mobile: 9403161157 > > ------ > From: "Aashutosh pandey" > Sent: Friday, September 21, 2012 2:59 PM > To: > Subject: Re: [AI] Excel formula > >> Sir you can use =concatinet() hopeit will work >> >> On 9/20/12, Mukesh wrote: >>> Dear Friends, >>> I have created a database in excel. My query is: >>> Columns: a1 b1 c1 d1 >>> Titles: districts vi/si m/F (Combination abbreviation of these three >>> cells >>> should come automatically) >>> Example: Nashik vi m nskmvi >>> How to do this with a formula? Please suggest me a formula at the >>> earliest. >>> Thank you. >>> Regards >>> Mukesh Baviskar >>> Mobile: 9403161157 >>> >>> Search for old postings at: >>> http://www.mail-archive.com/accessindia@accessindia.org.in/ >>> >>> To unsubscribe send a message to >>> accessindia-requ...@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 >>> >>> >> >> >> -- >> Thanks with best regards >> Aashutosh Pandey >> >> >> Search for old postings at: >> http://www.mail-archive.com/accessindia@accessindia.org.in/ >> >> To unsubscribe send a message to >> accessindia-requ...@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 >> > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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 > > -- Thanks with best regards Aashutosh Pandey Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
Hello Ashutosh, Thank you. It is working fine. Now I have one more query: cell a1 cell b1 any name of district abbreviation of district (for example: Nashik: nsk, Pune: pu, Latur: l, etc) My question is: When I type a name of any district in cell a1 the abbreviation of that particular district should come in cell b1 automatically. Suppose I type 'Nashik' in cell a1 the abbreviation of Nashik should automatically come in cell b1 'nsk'. Please guide me at the earliest because I have to complete my database within two/three days. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 -- From: "Aashutosh pandey" Sent: Friday, September 21, 2012 2:59 PM To: Subject: Re: [AI] Excel formula Sir you can use =concatinet() hopeit will work On 9/20/12, Mukesh wrote: Dear Friends, I have created a database in excel. My query is: Columns: a1 b1 c1 d1 Titles: districts vi/si m/F (Combination abbreviation of these three cells should come automatically) Example: Nashik vi m nskmvi How to do this with a formula? Please suggest me a formula at the earliest. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 -- Thanks with best regards Aashutosh Pandey Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
Hello, How to use this formula? If you don't mind please attach a excel sheet explaining this formula which will help me better. Because I don't know how to use this formula. Please send your reply on my personal email id because attachments are not permitted on list. Thank you. Mukesh Baviskar -- From: "Aashutosh pandey" Sent: Friday, September 21, 2012 2:59 PM To: Subject: Re: [AI] Excel formula Sir you can use =concatinet() hopeit will work On 9/20/12, Mukesh wrote: Dear Friends, I have created a database in excel. My query is: Columns: a1 b1 c1 d1 Titles: districts vi/si m/F (Combination abbreviation of these three cells should come automatically) Example: Nashik vi m nskmvi How to do this with a formula? Please suggest me a formula at the earliest. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 -- Thanks with best regards Aashutosh Pandey Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
Hello Zoher, It's very short and easy to remember. Only cell address + number of months are required. Thank you. Regards Mukesh Baviskar -- From: "zoher" Sent: Sunday, June 17, 2012 11:21 AM To: Subject: Re: [AI] Excel formula you can use edate function also. if your date is in a1 then type the following formula. =edate(a1,60) note the cell where you type the formula, after getting the answer convert it in to date format by pressing ctrl 1, number tab, date. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh" To: Sent: Sunday, June 17, 2012 9:59 AM Subject: Re: [AI] Excel formula It's working fine. Thank you for your quick guidance. Regards Mukesh Baviskar -- From: "Chetan Sharma" Sent: Sunday, June 17, 2012 9:29 AM To: Subject: Re: [AI] Excel formula Assuming that you have a date in cell A1. The following formula is one simple way to come up with a date five years later. =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) HTH! On 6/17/12, Mukesh wrote: Dear friends, I'm in a need of a following excel formula urgently to complete my database. I want to know the date after 5 year. for example: First date: 28/5/2007 and date after 5 years: 28/5/2012. Please tell me the formula to get this answer automatically by formula. Waiting for your positive response at the earliest. Thank you. Sincerely, Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 -- The greatest pleasure in life is doing what people say you cannot do. Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
EDate() function is good for adding months/years to a given date, it would not support addition of days to a given date. For example, it would not yield correct result if I wish to determine the days 45 days after today. The Date() function suggested by Chetan comes in handy in such situations: =DATE(YEAR(A1),MONTH(A1),DAY(A1)+45) Also, the Date() function returns a date value, so you don't need a format conversion. On 6/17/12, zoher wrote: > you can use edate function also. > if your date is in a1 then type the following formula. > =edate(a1,60) > note the cell where you type the formula, after getting the answer convert > it in to date format by pressing ctrl 1, number tab, date. > > zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala > - Original Message - > From: "Mukesh" > To: > Sent: Sunday, June 17, 2012 9:59 AM > Subject: Re: [AI] Excel formula > > >> It's working fine. Thank you for your quick guidance. >> >> Regards >> >> Mukesh Baviskar >> >> -------------- >> From: "Chetan Sharma" >> Sent: Sunday, June 17, 2012 9:29 AM >> To: >> Subject: Re: [AI] Excel formula >> >>> Assuming that you have a date in cell A1. The following formula is one >>> simple way to come up with a date five years later. >>> >>> =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) >>> >>> HTH! >>> >>> >>> >>> >>> On 6/17/12, Mukesh wrote: >>>> Dear friends, >>>> I'm in a need of a following excel formula urgently to complete my >>>> database. >>>> I want to know the date after 5 year. for example: >>>> >>>> First date: 28/5/2007 and date after 5 years: 28/5/2012. >>>> Please tell me the formula to get this answer automatically by formula. >>>> Waiting for your positive response at the earliest. Thank you. >>>> >>>> Sincerely, >>>> >>>> Mukesh Baviskar >>>> Mobile: 9403161157 >>>> >>>> Search for old postings at: >>>> http://www.mail-archive.com/accessindia@accessindia.org.in/ >>>> >>>> To unsubscribe send a message to >>>> accessindia-requ...@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 >>>> >>>> >>> >>> >>> -- >>> The greatest pleasure in life is doing what people say you cannot do. >>> >>> >>> Search for old postings at: >>> http://www.mail-archive.com/accessindia@accessindia.org.in/ >>> >>> To unsubscribe send a message to >>> accessindia-requ...@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 >>> >> >> >> Search for old postings at: >> http://www.mail-archive.com/accessindia@accessindia.org.in/ >> >> To unsubscribe send a message to >> accessindia-requ...@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 >> > > > > Search for old postings at: > http://www.mail-archive.com/accessindia@accessindia.org.in/ > > To unsubscribe send a message to > accessindia-requ...@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 > > -- Best Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
you can use edate function also. if your date is in a1 then type the following formula. =edate(a1,60) note the cell where you type the formula, after getting the answer convert it in to date format by pressing ctrl 1, number tab, date. zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala - Original Message - From: "Mukesh" To: Sent: Sunday, June 17, 2012 9:59 AM Subject: Re: [AI] Excel formula It's working fine. Thank you for your quick guidance. Regards Mukesh Baviskar -- From: "Chetan Sharma" Sent: Sunday, June 17, 2012 9:29 AM To: Subject: Re: [AI] Excel formula Assuming that you have a date in cell A1. The following formula is one simple way to come up with a date five years later. =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) HTH! On 6/17/12, Mukesh wrote: Dear friends, I'm in a need of a following excel formula urgently to complete my database. I want to know the date after 5 year. for example: First date: 28/5/2007 and date after 5 years: 28/5/2012. Please tell me the formula to get this answer automatically by formula. Waiting for your positive response at the earliest. Thank you. Sincerely, Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 -- The greatest pleasure in life is doing what people say you cannot do. Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] Excel formula
It's working fine. Thank you for your quick guidance. Regards Mukesh Baviskar -- From: "Chetan Sharma" Sent: Sunday, June 17, 2012 9:29 AM To: Subject: Re: [AI] Excel formula Assuming that you have a date in cell A1. The following formula is one simple way to come up with a date five years later. =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) HTH! On 6/17/12, Mukesh wrote: Dear friends, I'm in a need of a following excel formula urgently to complete my database. I want to know the date after 5 year. for example: First date: 28/5/2007 and date after 5 years: 28/5/2012. Please tell me the formula to get this answer automatically by formula. Waiting for your positive response at the earliest. Thank you. Sincerely, Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 -- The greatest pleasure in life is doing what people say you cannot do. Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@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
Re: [AI] EXCEL FORMULA
of course yes... On 4/29/10, Amiyo Biswas wrote: > Ok, if you use formatting features, it's handier than formulas. Isn't it? > Best regards, > Amiyo Biswas > Cell: 91-9433464329 > > - Original Message - > From: "Zujar Shabbir Kanchwala" > To: > Sent: Thursday, April 29, 2010 12:32 AM > Subject: Re: [AI] EXCEL FORMULA > > >> 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-boun...@accessindia.org.in >> [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Amiyo Biswas >> Sent: Wednesday, April 28, 2010 8:01 AM >> To: accessindia@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" >> To: >> 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-boun...@accessindia.org.in >>> [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Amiyo Biswas >>> Sent: Tuesday, April 27, 2010 6:20 AM >>> To: accessindia@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" >>> To: >>> Sent: Monday, April 26, 2010 11:00 AM >>> Subject: Re: [AI] EXCEL FORMULA >>> >>> >>>
Re: [AI] EXCEL FORMULA
Ok, if you use formatting features, it's handier than formulas. Isn't it? Best regards, Amiyo Biswas Cell: 91-9433464329 - Original Message - From: "Zujar Shabbir Kanchwala" To: Sent: Thursday, April 29, 2010 12:32 AM Subject: Re: [AI] EXCEL FORMULA 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-boun...@accessindia.org.in [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Amiyo Biswas Sent: Wednesday, April 28, 2010 8:01 AM To: accessindia@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" To: 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-boun...@accessindia.org.in [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Amiyo Biswas Sent: Tuesday, April 27, 2010 6:20 AM To: accessindia@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" To: 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" To: 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" To: 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" To: 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" To: 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 b
Re: [AI] EXCEL FORMULA
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-boun...@accessindia.org.in [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Amiyo Biswas Sent: Wednesday, April 28, 2010 8:01 AM To: accessindia@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" To: 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-boun...@accessindia.org.in > [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Amiyo Biswas > Sent: Tuesday, April 27, 2010 6:20 AM > To: accessindia@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" > To: > 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" >> To: >> 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" >>> To: >>> 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" >>>> To: >>>> 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 pla
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" To: 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-boun...@accessindia.org.in [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Amiyo Biswas Sent: Tuesday, April 27, 2010 6:20 AM To: accessindia@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" To: 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" To: 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" To: 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" To: 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" To: 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-requ...@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-requ...@accessindia.org.in with the subject unsubscribe. To change your subscription to dig
Re: [AI] EXCEL FORMULA
Hai, Ceiling function solved my problems. We can use this function in many ways. Thanks. Kamal Verma - Original Message - From: "Amiyo Biswas" To: Sent: Tuesday, April 27, 2010 6:19 AM 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" To: 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" To: 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" To: 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" To: 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" To: 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-requ...@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-requ...@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-requ...@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-requ...@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-requ...@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-requ...@accessindia.org.in with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit t
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-boun...@accessindia.org.in [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Amiyo Biswas Sent: Tuesday, April 27, 2010 6:20 AM To: accessindia@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" To: 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" > To: > 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" >> To: >> 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" >>> To: >>> 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" >>>> To: >>>> 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-requ...@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/acc
Re: [AI] EXCEL FORMULA
Ok, thank you for the roundup formula. I did not know it. However, I wanted to know if somebody has suggested any solution for the number format. If we use formulas, we have to put them in other cells. A formatting option will solve the actual problem presented by our friend. Best regards, Amiyo Biswas Cell: 91-9433464329 - Original Message - From: "Mukesh Baviskar" To: Cc: "Access India" Sent: Monday, April 26, 2010 8:43 PM Subject: Re: [AI] EXCEL FORMULA Hello, round off the figure in nearest rupee of 9.03 is 9 and not 10. Whenever The figure after decimal is >50 it will round up the nearest figure which 9. You may use the 'round' formula for this. For example: =ROUND(a1,0) IN B1 If you want to change 9.03 to 10 use roundup formula as: roundup(a1,0)in c1 you will get the answer 10 instead of 9 Try it and solve your problem. If you have any doubt contact me on my mobile. Thank you. 9.03 9 10 10.1 10 11 10.5 11 11 10.4 10 11 9.09 9 10 Mukesh R. Baviskar 9403161157 - Original Message - From: "Amiyo Biswas" To: 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" To: 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" To: 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-requ...@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-requ...@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-requ...@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-requ...@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-requ...@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
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" To: 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" To: 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" To: 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" To: 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" To: 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-requ...@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-requ...@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-requ...@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-requ...@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-requ...@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-requ...@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
Re: [AI] EXCEL FORMULA
Hello, round off the figure in nearest rupee of 9.03 is 9 and not 10. Whenever The figure after decimal is >50 it will round up the nearest figure which 9. You may use the 'round' formula for this. For example: =ROUND(a1,0) IN B1 If you want to change 9.03 to 10 use roundup formula as: roundup(a1,0)in c1 you will get the answer 10 instead of 9 Try it and solve your problem. If you have any doubt contact me on my mobile. Thank you. 9.03 9 10 10.1 10 11 10.5 11 11 10.4 10 11 9.09 9 10 Mukesh R. Baviskar 9403161157 - Original Message - From: "Amiyo Biswas" To: 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" To: 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" To: 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-requ...@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-requ...@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-requ...@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-requ...@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
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" To: 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" To: 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" To: 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" To: 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-requ...@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-requ...@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-requ...@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-requ...@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-requ...@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
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" To: 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" To: 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" To: 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-requ...@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-requ...@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-requ...@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-requ...@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
Re: [AI] EXCEL FORMULA
Ok, it's not giving 10 for 9.03, as Kamal wanted. - Original Message - From: "Mukesh Baviskar" To: 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" To: 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-requ...@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-requ...@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-requ...@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
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" To: 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-requ...@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-requ...@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
Re: [AI] EXCEL FORMULA
Dear members, Thanks a lot for the valuable suggestions. The list has really helped us immensely to crack our troubles instantly. On 4/23/10, Amiyo Biswas wrote: > Hello, > > In Excel, use ^ (caret) for power sign, e.g. 6^3 =216. Alternately, you can > use the power function for it. Say for instance, when cell A1 contains 6 and > B1 3, the following formula in cell C1 will yield 216: > =Power(a1^b1) > > For compound interest, the formula should be: > =A2*(1+B2/100)^C2 > When A2 is Principal, B2 Rate of interest and C2 Number of years > > For inserting a caret sign, use shift+6 on the alphanumeric keypad. > > With Best regards, > Amiyo Biswas. > Cell: 91-9433464329 > > - Original Message - > From: "Himanshu Sahu" > To: > Sent: Friday, April 23, 2010 11:20 AM > Subject: Re: [AI] EXCEL FORMULA > > > Dear list members, > I also have a query regarding Excel formula. > How can we put "Power” sign in excel sheet? > Simply I wish to extract compound interest. Does the formula vary in > Excel sheet from the conventional one? > > > On 4/23/10, BHAWANI SHANKAR VERMA wrote: >> i know prof. anil khurana, at shyamlal college. he was an active >> participant >> in accessindia. in my opinion he is a master in excel. >> >> - Original Message - >> From: "Surya Prakash Sharma" >> To: >> Sent: Thursday, April 22, 2010 9:01 PM >> Subject: Re: [AI] EXCEL FORMULA >> >> >>> Hi, >>> could you please send a tutorial on excel formulas on my mail ID. i have >>> gone through the notes on excel provided by nab delhi. thanks in advance. >>> sps >>> - Original Message - >>> From: "Kamal Verma" >>> To: >>> 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-requ...@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-requ...@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-requ...@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 >> > > > -- > Thanks and regards >Himanshu Sahu > Reach: 09051055000 > > > > To unsubscribe send a message to accessindia-requ...@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-requ...@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 > -- Thanks and regards Himanshu Sahu Reach: 09051055000 To unsubscribe send a message to accessindia-requ...@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
Re: [AI] EXCEL FORMULA
Hello, In Excel, use ^ (caret) for power sign, e.g. 6^3 =216. Alternately, you can use the power function for it. Say for instance, when cell A1 contains 6 and B1 3, the following formula in cell C1 will yield 216: =Power(a1^b1) For compound interest, the formula should be: =A2*(1+B2/100)^C2 When A2 is Principal, B2 Rate of interest and C2 Number of years For inserting a caret sign, use shift+6 on the alphanumeric keypad. With Best regards, Amiyo Biswas. Cell: 91-9433464329 - Original Message - From: "Himanshu Sahu" To: Sent: Friday, April 23, 2010 11:20 AM Subject: Re: [AI] EXCEL FORMULA Dear list members, I also have a query regarding Excel formula. How can we put "Power” sign in excel sheet? Simply I wish to extract compound interest. Does the formula vary in Excel sheet from the conventional one? On 4/23/10, BHAWANI SHANKAR VERMA wrote: i know prof. anil khurana, at shyamlal college. he was an active participant in accessindia. in my opinion he is a master in excel. - Original Message - From: "Surya Prakash Sharma" To: Sent: Thursday, April 22, 2010 9:01 PM Subject: Re: [AI] EXCEL FORMULA Hi, could you please send a tutorial on excel formulas on my mail ID. i have gone through the notes on excel provided by nab delhi. thanks in advance. sps - Original Message - From: "Kamal Verma" To: 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-requ...@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-requ...@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-requ...@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 -- Thanks and regards Himanshu Sahu Reach: 09051055000 To unsubscribe send a message to accessindia-requ...@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-requ...@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
Re: [AI] EXCEL FORMULA
Hello, It is "^". Kamal Verma - Original Message - From: "Himanshu Sahu" To: Sent: Friday, April 23, 2010 11:20 AM Subject: Re: [AI] EXCEL FORMULA Dear list members, I also have a query regarding Excel formula. How can we put "Power” sign in excel sheet? Simply I wish to extract compound interest. Does the formula vary in Excel sheet from the conventional one? On 4/23/10, BHAWANI SHANKAR VERMA wrote: i know prof. anil khurana, at shyamlal college. he was an active participant in accessindia. in my opinion he is a master in excel. - Original Message - From: "Surya Prakash Sharma" To: Sent: Thursday, April 22, 2010 9:01 PM Subject: Re: [AI] EXCEL FORMULA Hi, could you please send a tutorial on excel formulas on my mail ID. i have gone through the notes on excel provided by nab delhi. thanks in advance. sps - Original Message - From: "Kamal Verma" To: 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-requ...@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-requ...@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-requ...@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 -- Thanks and regards Himanshu Sahu Reach: 09051055000 To unsubscribe send a message to accessindia-requ...@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-requ...@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
Re: [AI] EXCEL FORMULA
Dear list members, I also have a query regarding Excel formula. How can we put "Power” sign in excel sheet? Simply I wish to extract compound interest. Does the formula vary in Excel sheet from the conventional one? On 4/23/10, BHAWANI SHANKAR VERMA wrote: > i know prof. anil khurana, at shyamlal college. he was an active participant > in accessindia. in my opinion he is a master in excel. > > - Original Message - > From: "Surya Prakash Sharma" > To: > Sent: Thursday, April 22, 2010 9:01 PM > Subject: Re: [AI] EXCEL FORMULA > > >> Hi, >> could you please send a tutorial on excel formulas on my mail ID. i have >> gone through the notes on excel provided by nab delhi. thanks in advance. >> sps >> - Original Message - >> From: "Kamal Verma" >> To: >> 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-requ...@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-requ...@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-requ...@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 > -- Thanks and regards Himanshu Sahu Reach: 09051055000 To unsubscribe send a message to accessindia-requ...@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
Re: [AI] EXCEL FORMULA
i know prof. anil khurana, at shyamlal college. he was an active participant in accessindia. in my opinion he is a master in excel. - Original Message - From: "Surya Prakash Sharma" To: Sent: Thursday, April 22, 2010 9:01 PM Subject: Re: [AI] EXCEL FORMULA Hi, could you please send a tutorial on excel formulas on my mail ID. i have gone through the notes on excel provided by nab delhi. thanks in advance. sps - Original Message - From: "Kamal Verma" To: 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-requ...@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-requ...@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-requ...@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
Re: [AI] EXCEL FORMULA
Hi, could you please send a tutorial on excel formulas on my mail ID. i have gone through the notes on excel provided by nab delhi. thanks in advance. sps - Original Message - From: "Kamal Verma" To: 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-requ...@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-requ...@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
Re: [AI] EXCEL FORMULA
Hello Sir, You can use ceiling function from excel to get your desired output , Just try below syntax. Suppose your cell A1 has a value then =CEILING(A1,1) This will convert your value to next higher integer , Just give a try. Hope this helps , Thanks. With best regards Saidarshan Bhagat. Siemens Information Systems Ltd. Gaa Central Functions. Project Monitoring 130 Pandurang Budhkar Marg, Worli Mumbai 400 018. Tell : +91 22 2495 7653. Mobile : +91 9820 223489. Mail to saidarshan.bha...@siemens.com www.siemens.co.in -Original Message- From: accessindia-boun...@accessindia.org.in [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Kamal Verma Sent: Thursday, April 22, 2010 11:29 AM To: accessindia@accessindia.org.in 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-requ...@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 Important notice: This e-mail and any attachment there to contains corporate proprietary information. If you have received it by mistake, please notify us immediately by reply e-mail and delete this e-mail and its attachments from your system. Thank You. To unsubscribe send a message to accessindia-requ...@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
