Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
autosum refusal to sum highlighted range
I apologize if this has been discussed and solved previously, but a quick
search didn't provide any answers. The following applies to 2003, 2000, and 2007. Autosum fails to insert the correct cell range into the formula when a cell in the highlighted range contains another sum() function and another cell in the range contains a negative reference, i.e. = -D5. For example: D3 contains the formula sum(B3:C3) D6 contains the negative reference = -B20 When cells D3 to D8 are highlighted and the autosum button is pressed, the forumula in D8 is sum(D6:D7) when it should be sum(D3:D7). (Cells D4 and D5 are empty. The range D3:D8 is formated to number). If the negative cell reference is changed to *-1, autosum works correctly. Also, if D3 does not contain a sum() formula, autosum works correctly. (Note: Cell B3 contains sum(A2.A3) and Cell B6 contains sum(A5.A6) ) I believe this is serious issue as a user doesn't normally double check the resulting formula from an autosum on a highlighted, simple one column range. Comments? Lawrence |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
autosum refusal to sum highlighted range
Please note D6 contains the negative reference = -B6 (not -B20 as stated below)
Sorry. Lawrence "lawrence" wrote: I apologize if this has been discussed and solved previously, but a quick search didn't provide any answers. The following applies to 2003, 2000, and 2007. Autosum fails to insert the correct cell range into the formula when a cell in the highlighted range contains another sum() function and another cell in the range contains a negative reference, i.e. = -D5. For example: D3 contains the formula sum(B3:C3) D6 contains the negative reference = -B20 When cells D3 to D8 are highlighted and the autosum button is pressed, the forumula in D8 is sum(D6:D7) when it should be sum(D3:D7). (Cells D4 and D5 are empty. The range D3:D8 is formated to number). If the negative cell reference is changed to *-1, autosum works correctly. Also, if D3 does not contain a sum() formula, autosum works correctly. (Note: Cell B3 contains sum(A2.A3) and Cell B6 contains sum(A5.A6) ) I believe this is serious issue as a user doesn't normally double check the resulting formula from an autosum on a highlighted, simple one column range. Comments? Lawrence |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
autosum refusal to sum highlighted range
On Apr 29, 3:24 pm, lawrence
wrote: I apologize if this has been discussed and solved previously, but a quick search didn't provide any answers. The following applies to 2003, 2000, and 2007. Autosum fails to insert the correct cell range into the formula when a cell in the highlighted range contains another sum() function and another cell in the range contains a negative reference, i.e. = -D5. For example: D3 contains the formula sum(B3:C3) D6 contains the negative reference = -B20 When cells D3 to D8 are highlighted and the autosum button is pressed, the forumula in D8 is sum(D6:D7) when it should be sum(D3:D7). (Cells D4 and D5 are empty. The range D3:D8 is formated to number). If the negative cell reference is changed to *-1, autosum works correctly. Also, if D3 does not contain a sum() formula, autosum works correctly. (Note: Cell B3 contains sum(A2.A3) and Cell B6 contains sum(A5.A6) ) I believe this is serious issue as a user doesn't normally double check the resulting formula from an autosum on a highlighted, simple one column range. Comments? Lawrence I would say a more serious issue is a user who doesn't bother to check what any sort of automatic entry does. Ignoring another SUM function in some circumstances is a feature, not a bug. It can assume that you are creating a column of sections with totals and only want to sum up to the previous section. But also, when I tried to recreate this (XL2003) by selecting D3:D8 and hitting Autosum, it worked how you want it to. Now, if I select only D8 and hit Autosum, it chooses only D6:D7. But, IME, that is because of the empty cells D4:D5 and is normal behavior. Perhaps you have more cells with SUM functions nearby (in C8?) and it is copying from them? You like little annoyances? Here's mine for the week: Yesterday F4 stopped working as repeat for me. And the button isn't broken, it works elsewhere. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
autosum refusal to sum highlighted range
"Spiky" wrote: I would say a more serious issue is a user who doesn't bother to check what any sort of automatic entry does. Yes, I agree relying on an automatic entry is asking for trouble. However, I do expect a certain predictable behaviour. In this case the user is highlighting a single column range and then hitting the autosum with the expectation that autosum would sum() the selected range. In this simple example, overriding the users selection is not appropriate. If the user had used '=B6*-1' and not '=-B6' in cell D6, this issue would never have surfaced. Ignoring another SUM function in some circumstances is a feature, not a bug. It can assume that you are creating a column of sections with totals and only want to sum up to the previous section. It may be a feature, but rather than silently changing your selected range, a good feature would point out your possible mistake and allow you to accept the change or confirm your original intent. But also, when I tried to recreate this (XL2003) by selecting D3:D8 and hitting Autosum, it worked how you want it to. Now, if I select only D8 and hit Autosum, it chooses only D6:D7. But, IME, that is because of the empty cells D4:D5 and is normal behavior. Perhaps you have more cells with SUM functions nearby (in C8?) and it is copying from them? Try it again as follows: A B C D 1 2 10 3 10 sum(a2:a3) sum(b3:c3) 4 5 10 6 10 sum(a5:a6) =-b6 7 8 Highlight d3:d8 and then hit autosum. It will *not* use highlighted range but substitutes it's own d6:d7. Change d6 to =b6*-1 and try again and it works! I've tried this on a few different machines and the versions indicated. You like little annoyances? Here's mine for the week: Yesterday F4 stopped working as repeat for me. And the button isn't broken, it works elsewhere. Yes annoyances are everywhere. Avoiding them seems futile some days. Lawrence |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
autosum refusal to sum highlighted range
On Apr 30, 12:32 pm, lawrence
wrote: "Spiky" wrote: I would say a more serious issue is a user who doesn't bother to check what any sort of automatic entry does. Yes, I agree relying on an automatic entry is asking for trouble. However, I do expect a certain predictable behaviour. In this case the user is highlighting a single column range and then hitting the autosum with the expectation that autosum would sum() the selected range. In this simple example, overriding the users selection is not appropriate. If the user had used '=B6*-1' and not '=-B6' in cell D6, this issue would never have surfaced. Ignoring another SUM function in some circumstances is a feature, not a bug. It can assume that you are creating a column of sections with totals and only want to sum up to the previous section. It may be a feature, but rather than silently changing your selected range, a good feature would point out your possible mistake and allow you to accept the change or confirm your original intent. But also, when I tried to recreate this (XL2003) by selecting D3:D8 and hitting Autosum, it worked how you want it to. Now, if I select only D8 and hit Autosum, it chooses only D6:D7. But, IME, that is because of the empty cells D4:D5 and is normal behavior. Perhaps you have more cells with SUM functions nearby (in C8?) and it is copying from them? Try it again as follows: A B C D 1 2 10 3 10 sum(a2:a3) sum(b3:c3) 4 5 10 6 10 sum(a5:a6) =-b6 7 8 Highlight d3:d8 and then hit autosum. It will *not* use highlighted range but substitutes it's own d6:d7. Change d6 to =b6*-1 and try again and it works! I've tried this on a few different machines and the versions indicated. You like little annoyances? Here's mine for the week: Yesterday F4 stopped working as repeat for me. And the button isn't broken, it works elsewhere. Yes annoyances are everywhere. Avoiding them seems futile some days. Lawrence Interesting. And if you put a double unary in B6, it works. Maybe we should make all of our SUMs have this. I guess report it to Microsoft. Here's another: Have a file with at least 2 sheets. Protect a sheet with unlocked cells selectable, locked cells unselectable. Now try CTRL-PGUP/DOWN. That should switch between sheets, but it now behaves as ALT-PGUP/DOWN. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignoring #N/A in an Autosum range | Excel Worksheet Functions | |||
Autosum | Excel Discussion (Misc queries) | |||
Refusal to calculate | Excel Worksheet Functions | |||
Refusal to save | Excel Discussion (Misc queries) | |||
Autosum | Excel Discussion (Misc queries) |