Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Amend Formula
Hi
I have the following formulas in cells A1 and A2 =COUNTIF(Log!N2928:N3657,"4")/2 =COUNTIF(Log!P2928:P3657,"4")/2 Is there an easy way to amend the formulas to: =COUNTIF(Log!N3660:N4208,"4")/2 =COUNTIF(Log!P3660:P4208,"4")/2 without having to manually typing them in (please note that I need to maintain the column references) Many thanks in anticipation Colin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Amend Formula
Hi Colin,
Press Ctrl + H, and replace N2928:N3657 with N3660:N4208. Again replace P2928:P3657 with N3660:N4208. Just two simple steps are you are done. Thanks. -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "colin" wrote: Hi I have the following formulas in cells A1 and A2 =COUNTIF(Log!N2928:N3657,"4")/2 =COUNTIF(Log!P2928:P3657,"4")/2 Is there an easy way to amend the formulas to: =COUNTIF(Log!N3660:N4208,"4")/2 =COUNTIF(Log!P3660:P4208,"4")/2 without having to manually typing them in (please note that I need to maintain the column references) Many thanks in anticipation Colin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Amend Formula
Hi
Many thanks for the suggestion - if I do a find and replace for 2928 in both cells and then do a replace all then the first cell is correctly updated, however the second replacement amends as follows: =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 The second cells formula amends the second part of the range which to me appears odd - Iam using excel 2007 - is this a bug? Kind regards Colin "DILipandey" wrote: Hi Colin, Press Ctrl + H, and replace N2928:N3657 with N3660:N4208. Again replace P2928:P3657 with N3660:N4208. Just two simple steps are you are done. Thanks. -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "colin" wrote: Hi I have the following formulas in cells A1 and A2 =COUNTIF(Log!N2928:N3657,"4")/2 =COUNTIF(Log!P2928:P3657,"4")/2 Is there an easy way to amend the formulas to: =COUNTIF(Log!N3660:N4208,"4")/2 =COUNTIF(Log!P3660:P4208,"4")/2 without having to manually typing them in (please note that I need to maintain the column references) Many thanks in anticipation Colin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Amend Formula
--Select the cells
--Launch the replace window using Ctrl+H --Click options and make sure the selection for 'Lookin' is formulas --Do the below number replacements find 2928 with 3660 find 3657 with 4208 If this post helps click Yes --------------- Jacob Skaria "colin" wrote: Hi Many thanks for the suggestion - if I do a find and replace for 2928 in both cells and then do a replace all then the first cell is correctly updated, however the second replacement amends as follows: =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 The second cells formula amends the second part of the range which to me appears odd - Iam using excel 2007 - is this a bug? Kind regards Colin "DILipandey" wrote: Hi Colin, Press Ctrl + H, and replace N2928:N3657 with N3660:N4208. Again replace P2928:P3657 with N3660:N4208. Just two simple steps are you are done. Thanks. -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "colin" wrote: Hi I have the following formulas in cells A1 and A2 =COUNTIF(Log!N2928:N3657,"4")/2 =COUNTIF(Log!P2928:P3657,"4")/2 Is there an easy way to amend the formulas to: =COUNTIF(Log!N3660:N4208,"4")/2 =COUNTIF(Log!P3660:P4208,"4")/2 without having to manually typing them in (please note that I need to maintain the column references) Many thanks in anticipation Colin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Amend Formula
Hi Colin
It seems to be a (another) bug! Replace the larger value first, then it seems to work. Regards, Per "colin" skrev i meddelelsen ... Hi Many thanks for the suggestion - if I do a find and replace for 2928 in both cells and then do a replace all then the first cell is correctly updated, however the second replacement amends as follows: =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 The second cells formula amends the second part of the range which to me appears odd - Iam using excel 2007 - is this a bug? Kind regards Colin "DILipandey" wrote: Hi Colin, Press Ctrl + H, and replace N2928:N3657 with N3660:N4208. Again replace P2928:P3657 with N3660:N4208. Just two simple steps are you are done. Thanks. -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "colin" wrote: Hi I have the following formulas in cells A1 and A2 =COUNTIF(Log!N2928:N3657,"4")/2 =COUNTIF(Log!P2928:P3657,"4")/2 Is there an easy way to amend the formulas to: =COUNTIF(Log!N3660:N4208,"4")/2 =COUNTIF(Log!P3660:P4208,"4")/2 without having to manually typing them in (please note that I need to maintain the column references) Many thanks in anticipation Colin |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Amend Formula
Hi Jacob,
Thanks for the reply - I have checked the options and the lookin is set to formulas however it still replaces the second part of the range =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 Rgds Colin "Jacob Skaria" wrote: --Select the cells --Launch the replace window using Ctrl+H --Click options and make sure the selection for 'Lookin' is formulas --Do the below number replacements find 2928 with 3660 find 3657 with 4208 If this post helps click Yes --------------- Jacob Skaria "colin" wrote: Hi Many thanks for the suggestion - if I do a find and replace for 2928 in both cells and then do a replace all then the first cell is correctly updated, however the second replacement amends as follows: =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 The second cells formula amends the second part of the range which to me appears odd - Iam using excel 2007 - is this a bug? Kind regards Colin "DILipandey" wrote: Hi Colin, Press Ctrl + H, and replace N2928:N3657 with N3660:N4208. Again replace P2928:P3657 with N3660:N4208. Just two simple steps are you are done. Thanks. -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "colin" wrote: Hi I have the following formulas in cells A1 and A2 =COUNTIF(Log!N2928:N3657,"4")/2 =COUNTIF(Log!P2928:P3657,"4")/2 Is there an easy way to amend the formulas to: =COUNTIF(Log!N3660:N4208,"4")/2 =COUNTIF(Log!P3660:P4208,"4")/2 without having to manually typing them in (please note that I need to maintain the column references) Many thanks in anticipation Colin |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Amend Formula
Can you try entering the formulas in to a new workbook/sheet and try the
replacement.. If this post helps click Yes --------------- Jacob Skaria "colin" wrote: Hi Jacob, Thanks for the reply - I have checked the options and the lookin is set to formulas however it still replaces the second part of the range =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 Rgds Colin "Jacob Skaria" wrote: --Select the cells --Launch the replace window using Ctrl+H --Click options and make sure the selection for 'Lookin' is formulas --Do the below number replacements find 2928 with 3660 find 3657 with 4208 If this post helps click Yes --------------- Jacob Skaria "colin" wrote: Hi Many thanks for the suggestion - if I do a find and replace for 2928 in both cells and then do a replace all then the first cell is correctly updated, however the second replacement amends as follows: =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 The second cells formula amends the second part of the range which to me appears odd - Iam using excel 2007 - is this a bug? Kind regards Colin "DILipandey" wrote: Hi Colin, Press Ctrl + H, and replace N2928:N3657 with N3660:N4208. Again replace P2928:P3657 with N3660:N4208. Just two simple steps are you are done. Thanks. -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "colin" wrote: Hi I have the following formulas in cells A1 and A2 =COUNTIF(Log!N2928:N3657,"4")/2 =COUNTIF(Log!P2928:P3657,"4")/2 Is there an easy way to amend the formulas to: =COUNTIF(Log!N3660:N4208,"4")/2 =COUNTIF(Log!P3660:P4208,"4")/2 without having to manually typing them in (please note that I need to maintain the column references) Many thanks in anticipation Colin |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Amend Formula
Hi Per,
Thanks for the suggestion That works - many thanks Must be a built in design feature!!! Kind regards Colin "Per Jessen" wrote: Hi Colin It seems to be a (another) bug! Replace the larger value first, then it seems to work. Regards, Per "colin" skrev i meddelelsen ... Hi Many thanks for the suggestion - if I do a find and replace for 2928 in both cells and then do a replace all then the first cell is correctly updated, however the second replacement amends as follows: =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 The second cells formula amends the second part of the range which to me appears odd - Iam using excel 2007 - is this a bug? Kind regards Colin "DILipandey" wrote: Hi Colin, Press Ctrl + H, and replace N2928:N3657 with N3660:N4208. Again replace P2928:P3657 with N3660:N4208. Just two simple steps are you are done. Thanks. -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "colin" wrote: Hi I have the following formulas in cells A1 and A2 =COUNTIF(Log!N2928:N3657,"4")/2 =COUNTIF(Log!P2928:P3657,"4")/2 Is there an easy way to amend the formulas to: =COUNTIF(Log!N3660:N4208,"4")/2 =COUNTIF(Log!P3660:P4208,"4")/2 without having to manually typing them in (please note that I need to maintain the column references) Many thanks in anticipation Colin |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Amend Formula
Hi Jacob
I see the same behaviour as OP. Have tested it in a new excel 2007 workbook, where I pasted OP's formulas. If I replace the larger value first, it is working correct. Regards, Per "Jacob Skaria" skrev i meddelelsen ... Can you try entering the formulas in to a new workbook/sheet and try the replacement.. If this post helps click Yes --------------- Jacob Skaria "colin" wrote: Hi Jacob, Thanks for the reply - I have checked the options and the lookin is set to formulas however it still replaces the second part of the range =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 Rgds Colin "Jacob Skaria" wrote: --Select the cells --Launch the replace window using Ctrl+H --Click options and make sure the selection for 'Lookin' is formulas --Do the below number replacements find 2928 with 3660 find 3657 with 4208 If this post helps click Yes --------------- Jacob Skaria "colin" wrote: Hi Many thanks for the suggestion - if I do a find and replace for 2928 in both cells and then do a replace all then the first cell is correctly updated, however the second replacement amends as follows: =COUNTIF(Log!N3660:N3657,"4")/2 =COUNTIF(Log!P2928:P3660,"4")/2 The second cells formula amends the second part of the range which to me appears odd - Iam using excel 2007 - is this a bug? Kind regards Colin "DILipandey" wrote: Hi Colin, Press Ctrl + H, and replace N2928:N3657 with N3660:N4208. Again replace P2928:P3657 with N3660:N4208. Just two simple steps are you are done. Thanks. -- Click on Yes, if it is useful. Thanks & Best Regards, Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "colin" wrote: Hi I have the following formulas in cells A1 and A2 =COUNTIF(Log!N2928:N3657,"4")/2 =COUNTIF(Log!P2928:P3657,"4")/2 Is there an easy way to amend the formulas to: =COUNTIF(Log!N3660:N4208,"4")/2 =COUNTIF(Log!P3660:P4208,"4")/2 without having to manually typing them in (please note that I need to maintain the column references) Many thanks in anticipation Colin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Amend year | Excel Worksheet Functions | |||
amend formula | Excel Discussion (Misc queries) | |||
how can I amend my address data source | New Users to Excel | |||
Printing: how do I amend my print defaults? | Excel Discussion (Misc queries) | |||
Amend formula to include addition condition | Excel Worksheet Functions |