0

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!

Improve this question

2 Answers 2

Reset to default
3

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)
Improve this answer
3
  • 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
    Commented Jul 8, 2013 at 9:08
  • @AaronLee You're welcome :)
    – Jerry
    Commented Jul 8, 2013 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
    Commented Jul 8, 2013 at 9:10
1

You will need something like this : 

=A1-SUM(B1:B10)+SUM(C1:C10)
Improve this answer
1
  • 1
    Thanks for the answer, but Jerry got their first, upvote for both still, thanks guys.
    – Aaron Lee
    Commented Jul 8, 2013 at 9:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .