Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a excel spreadsheet, and while I like to think of myself as quite the excel wizard, this one has me stumped, it's probably something ridiculous but I really can't get it too work. I have a table of two columns, and a cell containing a specific number, for example, in A1 there is an amount of 10. The B column would be filled with number, so say B1 contains 1 and B2 contains 2, and the same again for the C column.

Now, I would like the cell D1 to contain the total for referencing A1, subtracting all the amounts from the B column, and then adding all of the amounts from the C column. Now, the formula I have used is as follows:

=A1-(B1:B10)+(C1:C10)

Now i'm not sure if this is causing the dreaded #VALUE error because their are empty cells in the formula, while the B or C column may not be full right now, they could be full another time which is why the formula has to stretch so far the column so I don't have to alter the equation everyday.

I've tried formatting the cell and various other things I could think of but it just doesn't seem to work, i'd be extremely appreciative to anyone that can help me out with this one, this little spreadsheet is meant to make my life easier but instead it's just ruining my happy little mood.

Thanks again guys!

share|improve this question
add comment

migrated from stackoverflow.com Jul 8 '13 at 17:05

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 3 down vote accepted

B1:B10 is an array (you can think of them like matrices) and there's no operation on it in your formula. What you probably want to do is:

=A1-SUM(B1:B10)+SUM(C1:C10)
share|improve this answer
    
Why I didn't think of that myself I will never know, that's ridiculous, naturally it worked, and thank you very much! –  Aaron Lee Jul 8 '13 at 9:08
    
@AaronLee You're welcome :) –  Jerry Jul 8 '13 at 9:09
    
Stick around for a while if you can, I may have a few more questions coming soon because this has got to get a whole lot more information and formulas in there soon haha –  Aaron Lee Jul 8 '13 at 9:10
    
Ok man, thanks for the advice! –  Aaron Lee Jul 8 '13 at 9:12
add comment

You will need something like this :

=A1-SUM(B1:B10)+SUM(C1:C10)
share|improve this answer
1  
Thanks for the answer, but Jerry got their first, upvote for both still, thanks guys. –  Aaron Lee Jul 8 '13 at 9:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.