Spreadsheet help please

[MD5]

MS Office 2007 Excel.

I'm trying to create a formula with autosum where A20 = A4 - the sum of A5 to A19 inclusive for example, but have been unable to make it work when using the shift plus left click shortcut to select cells A5 to A19. The formula appears as =SUM(A4-A5:A19), and #VALUE! appears in A20, with nothing calculated when figures are entered. The result is the same when the brackets are altered to =SUM(A4-(A5:A19)).

The formula does work when selecting and subtracting each cell individually, which seems too long winded. I created all of my more complex spreadsheets in Office Pro 97, yet this is so simple, so what have I missed? Back to school for me - I'm either rusty or losing the plot (or 2007 is rubbish!).

 

[davidsw]

A20 should read =sum(A4-sum(A5:A19))

David

 

[wemorgan]

is this what you want?

=(A4-SUM(A5:A19))

 

[MD5]

Thanks so much David and Will. It's the manual interjections in autosum that I'm rusty on!

 

[Dieselman]

Thanks so much David and Will. It's the manual interjections in autosum that I'm rusty on!

You mean basic formula.

 

[Williamwoo]

even simpler (marginally!):

=A4-SUM(A5:A19)

 

[MD5]

Thanks so much David and Will. It's the manual interjections in autosum that I'm rusty on!

You mean basic formula.

No, I meant what I said, and not 'algebra', as you first stated before your edit replaced it with 'formula', Will. I set the formula out in my first post, and I expected autosum to follow suit without me adding words! They should call it semiautosum!

 

[MOCAŠ]

They've improved it a bit in Excel 2007/2010, but not to the extent you'd like. Entering =SUM(A4-A5:A19) will now return the value in cell A4 (rather than #VALUE!) but still ignores cells A5 to A19

On second thoughts, that's not really an improvement.

 

[Dieselman]

No, I meant what I said, and not 'algebra', as you first stated before your edit replaced it with 'formula', Will. I set the formula out in my first post, and I expected autosum to follow suit without me adding words! They should call it semiautosum!

Your basic formula as written was incorrect, the brackets needed to be specific, not around the complete formula.
If it is incorrect as written then Exel will replicate the error.

 

[MD5]

Looks like autocorrect is also playing up now!

 

[MOCAŠ]

Your basic formula as written was incorrect, the brackets needed to be specific, not around the complete formula.
If it is incorrect as written then Exel will replicate the error.

The point MD5 was making was that he was trying to use the Autosum facility to construct the formula, starting out by selecting =SUM and then selecting the relevant cell ranges. However, Autosum isn't capable of handling this - it requires the user to understand the syntax for any formula involving both single and multiple cell ranges. Hence "Semiautosum".

 

[MD5]

Autocorrect seems to be working better now!