Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
I use 2003 Excel. If I write a formula which works properly with the data
sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
What is the formula that you are using right now?
mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
=SUM(D49:D52)/COUNT(D49:D52)
"Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
Where are you placing that formula? What range are you sorting?
Show an example of what your data looks like and what you are trying to accomplish. mmcap wrote: =SUM(D49:D52)/COUNT(D49:D52) "Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
Placing the formula in H49 and right now I have to sort A2-A358
alphabetically and other times I need to sort by groups which is J2-J358 and the list will get larger with time. "Jason Lepack" wrote: Where are you placing that formula? What range are you sorting? Show an example of what your data looks like and what you are trying to accomplish. mmcap wrote: =SUM(D49:D52)/COUNT(D49:D52) "Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
"mmcap" wrote: The formula is in H49 and right now I sort the entire sheet by column A alphabetically and other times I need to sort by groups which is column J. The list will get larger with time. "Jason Lepack" wrote: Where are you placing that formula? What range are you sorting? Show an example of what your data looks like and what you are trying to accomplish. mmcap wrote: =SUM(D49:D52)/COUNT(D49:D52) "Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
PS I sort the entire sheet when I do the sort.
"mmcap" wrote: Placing the formula in H49 and right now I have to sort A2-A358 alphabetically and other times I need to sort by groups which is J2-J358 and the list will get larger with time. "Jason Lepack" wrote: Where are you placing that formula? What range are you sorting? Show an example of what your data looks like and what you are trying to accomplish. mmcap wrote: =SUM(D49:D52)/COUNT(D49:D52) "Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
"mmcap" wrote: Placing the formula in H49 and right now I have to sort A2-A358 alphabetically and other times I need to sort by groups which is J2-J358 and the list will get larger with time. "Jason Lepack" wrote: Where are you placing that formula? What range are you sorting? Show an example of what your data looks like and what you are trying to accomplish. mmcap wrote: =SUM(D49:D52)/COUNT(D49:D52) "Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
I have no idea what your data looks like or why you would want to take
the average of those cells, wwithout that information I can propose no solution. I can tell you that when you perform your sort your formulas will move. I would suggest possibly removing your formulas from your sort range if you are intending to keep your formulas in the same spot every time. Post back with a bunch of rows of data. Show what it looks like, what it's doing that it's not supposed to and what you would like it to do. Cheers, Jason Lepack mmcap wrote: PS I sort the entire sheet when I do the sort. "mmcap" wrote: Placing the formula in H49 and right now I have to sort A2-A358 alphabetically and other times I need to sort by groups which is J2-J358 and the list will get larger with time. "Jason Lepack" wrote: Where are you placing that formula? What range are you sorting? Show an example of what your data looks like and what you are trying to accomplish. mmcap wrote: =SUM(D49:D52)/COUNT(D49:D52) "Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
Obviously you are trying to perform a conditional sum. By sorting you
group the records according to an attribute found in another column. This attribute is the key to your problem. Say you sorted by column C, and it contains values like "a", "b", "c" etc. Presumably, in the sorted table, rows 49:52 contain "c" in column C. Then you need the following formula: =SUMPRODUCT(D2:D380*(C2:C380="c"))/SUMPRODUCT(--(C2:C380="c")) This formula looks at the entire table and does not care where "c" is in column C. It is unaffected by sorting. Modify accordingly (i.e. the range references and the value you are looking for). If the value of your criterion is numeric you do not need (in fact you must not use) quotes. HTH Kostis Vezerides mmcap wrote: =SUM(D49:D52)/COUNT(D49:D52) "Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
There are weekly data releases for this specific group. The reason for the
formula is to get a four week average. If you would like I could send a copy of the sheet. "Jason Lepack" wrote: I have no idea what your data looks like or why you would want to take the average of those cells, wwithout that information I can propose no solution. I can tell you that when you perform your sort your formulas will move. I would suggest possibly removing your formulas from your sort range if you are intending to keep your formulas in the same spot every time. Post back with a bunch of rows of data. Show what it looks like, what it's doing that it's not supposed to and what you would like it to do. Cheers, Jason Lepack mmcap wrote: PS I sort the entire sheet when I do the sort. "mmcap" wrote: Placing the formula in H49 and right now I have to sort A2-A358 alphabetically and other times I need to sort by groups which is J2-J358 and the list will get larger with time. "Jason Lepack" wrote: Where are you placing that formula? What range are you sorting? Show an example of what your data looks like and what you are trying to accomplish. mmcap wrote: =SUM(D49:D52)/COUNT(D49:D52) "Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula won't adjust after sorting data
Sure, you can send it to:
vezerid at act dot edu Regards mmcap wrote: There are weekly data releases for this specific group. The reason for the formula is to get a four week average. If you would like I could send a copy of the sheet. "Jason Lepack" wrote: I have no idea what your data looks like or why you would want to take the average of those cells, wwithout that information I can propose no solution. I can tell you that when you perform your sort your formulas will move. I would suggest possibly removing your formulas from your sort range if you are intending to keep your formulas in the same spot every time. Post back with a bunch of rows of data. Show what it looks like, what it's doing that it's not supposed to and what you would like it to do. Cheers, Jason Lepack mmcap wrote: PS I sort the entire sheet when I do the sort. "mmcap" wrote: Placing the formula in H49 and right now I have to sort A2-A358 alphabetically and other times I need to sort by groups which is J2-J358 and the list will get larger with time. "Jason Lepack" wrote: Where are you placing that formula? What range are you sorting? Show an example of what your data looks like and what you are trying to accomplish. mmcap wrote: =SUM(D49:D52)/COUNT(D49:D52) "Jason Lepack" wrote: What is the formula that you are using right now? mmcap wrote: I use 2003 Excel. If I write a formula which works properly with the data sorted by column A and then sort by a different column it no longer works. There are two main groups that I need to sort by. Sometimes I need the data sorted alphabetically (column A) and sometimes I need it sorted by groups (column G). If I write a formula in cell (J50) while sorted alphabetically and then sort by groups the formula in cell (J50) might move to cell (J40) and the data that is referenced by that formula could be scattered anywhere on the sheet so the formula is no longer relevant. Is there any way to make the formula adjust to where it will still refer to the same data even if the data and the formula cells move after being sorted? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |