Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUM()
Richard,
excel understand that you would like to sum all above figures and "edit" the formula automatic. re-type it as you need hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Richard Wrigley" wrote: Greetings from Norfolk This may be a silly question! I set up SUM(D5:D10) and put the result in D15. When trying out the sheet I made entries (numeric) in D5, D6...D10 all OK. I then made entries in D11, D12, D13 and D14; The formulae in D15 modified itself (!!??) to include these values in the Summation. i.e. the answer was progressively D5 to D10, D5 to D11, D5 to D12 etc. Can anybody explain and assist in preventing this occuring again. Ilooked through the various settings for Excel, but could not find any that appear relevant ! -- Richard. "I have yet to see any problem, however complicated, which when looked at in the right way, did not become still more complicated" Poul Anderson |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUM()
This is Excel trying to be helpful with automatic formula extension
Use Tool | Options ; open the Edit tab; deselect Extend Data Range Formats & Formulas Is that the Norfolk where 'broads' needs bodies of water or the one where it means females? Ah, but your email address contains UK so I guess its the former. You should learn how to set up you newsgroup reader (Outlook maybe) to give a false email address if you want to avoid lots of spam - there are 'bots' (computer programs) which gather email address from newsgroups. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Richard Wrigley" wrote in message ... Greetings from Norfolk This may be a silly question! I set up SUM(D5:D10) and put the result in D15. When trying out the sheet I made entries (numeric) in D5, D6...D10 all OK. I then made entries in D11, D12, D13 and D14; The formulae in D15 modified itself (!!??) to include these values in the Summation. i.e. the answer was progressively D5 to D10, D5 to D11, D5 to D12 etc. Can anybody explain and assist in preventing this occuring again. Ilooked through the various settings for Excel, but could not find any that appear relevant ! -- Richard. "I have yet to see any problem, however complicated, which when looked at in the right way, did not become still more complicated" Poul Anderson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUM()
Alternative:
place formula in another column -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Richard Wrigley" wrote in message ... Greetings from Norfolk This may be a silly question! I set up SUM(D5:D10) and put the result in D15. When trying out the sheet I made entries (numeric) in D5, D6...D10 all OK. I then made entries in D11, D12, D13 and D14; The formulae in D15 modified itself (!!??) to include these values in the Summation. i.e. the answer was progressively D5 to D10, D5 to D11, D5 to D12 etc. Can anybody explain and assist in preventing this occuring again. Ilooked through the various settings for Excel, but could not find any that appear relevant ! -- Richard. "I have yet to see any problem, however complicated, which when looked at in the right way, did not become still more complicated" Poul Anderson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUM()
Greetings from Norfolk
This may be a silly question! I set up SUM(D5:D10) and put the result in D15. When trying out the sheet I made entries (numeric) in D5, D6...D10 all OK. I then made entries in D11, D12, D13 and D14; The formulae in D15 modified itself (!!??) to include these values in the Summation. i.e. the answer was progressively D5 to D10, D5 to D11, D5 to D12 etc. Can anybody explain and assist in preventing this occuring again. Ilooked through the various settings for Excel, but could not find any that appear relevant ! -- Richard. "I have yet to see any problem, however complicated, which when looked at in the right way, did not become still more complicated" Poul Anderson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUM()
If you click on the formula cell and put the cursor in the range, the range
should be shown by a coloured rectangle around the cells used within the range. You can alter the size of this rectangle to only cover the cells you require by moving one of the handles on one of the corners of the rectangle. "Richard Wrigley" wrote: Greetings from Norfolk This may be a silly question! I set up SUM(D5:D10) and put the result in D15. When trying out the sheet I made entries (numeric) in D5, D6...D10 all OK. I then made entries in D11, D12, D13 and D14; The formulae in D15 modified itself (!!??) to include these values in the Summation. i.e. the answer was progressively D5 to D10, D5 to D11, D5 to D12 etc. Can anybody explain and assist in preventing this occuring again. Ilooked through the various settings for Excel, but could not find any that appear relevant ! -- Richard. "I have yet to see any problem, however complicated, which when looked at in the right way, did not become still more complicated" Poul Anderson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUM()
Alternatively to Bernard's original and "alternative" - if you *don't* want
to disable the Extend feature (which does prove useful) - you can edit your formula to read as: SUM($D$5:$D$10) This makes the range reference absolute and prevents the function from automatically including the new entries in the column. -- HTH |:) Bob Jones [MVP] Office:Mac "Bernard Liengme" wrote in message ... Alternative: place formula in another column -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Richard Wrigley" wrote in message ... Greetings from Norfolk This may be a silly question! I set up SUM(D5:D10) and put the result in D15. When trying out the sheet I made entries (numeric) in D5, D6...D10 all OK. I then made entries in D11, D12, D13 and D14; The formulae in D15 modified itself (!!??) to include these values in the Summation. i.e. the answer was progressively D5 to D10, D5 to D11, D5 to D12 etc. Can anybody explain and assist in preventing this occuring again. Ilooked through the various settings for Excel, but could not find any that appear relevant ! -- Richard. "I have yet to see any problem, however complicated, which when looked at in the right way, did not become still more complicated" Poul Anderson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
have some problem with database | Excel Discussion (Misc queries) | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |