The range can’t be used as a single value.

Question

The range can’t be used as a single value.

Sorry all, a bit new to spreadsheets

I am creating a budget spreadsheet in Numbers to learn how it all works

I have a costing for Oil use; 12 monthly columns that are added together to produce a total for the year.

However there is a standing charge to be added to this for the oil tank and that is £60 per year.

So I started with a formula that states simply:

SUM(B6:M6)

This returns my yearly oil consumption cost but when I amend it to add the standing charge of £60p.a. like this

SUM(B6:M6)+60

I get the syntax error message "The range B6:M6 can't be used as a single value"

I am sure there must be an easy work around for this but I haven't found it yet.

Anyone got any thoughts ?

Posted on Apr 25, 2015 3:58 AM

Reply

Answer 1

Best reply

Jeff Shenk

Level 4

2,993 points

Apr 25, 2015 4:19 AM in response to David Graeme-Baker

I don't get that error message, but the arguments for a function like SUM should be separated by commas (or semicolons, if the comma is your decimal separator), not arithmetic operators. Your expression should be:

=SUM (B6:M6,60)

or else:

=SUM (B6:M6) + 60

Reply

Answer 2

Yellowbox

Level 7

20,347 points

Apr 25, 2015 12:24 PM in response to David Graeme-Baker

Hi David,

See if this works for you:

Formula in B8 (and Fill Right)

=January Income−SUM(B3:B7)

Regards,

Ian.

Reply

Answer 3

Apr 25, 2015 4:03 AM in response to David Graeme-Baker

Ooops

The pasted image in my previous post was incorrect:

It should have been this:

You will note that the formula now shows "B6:M6+60" not "B6:M6=60"

Still get the same error message though

Reply

Answer 4

Apr 25, 2015 5:00 AM in response to Jeff Shenk

Thanks Jeff - that sorted it !

That works fine.

But this one doesn't:

Strange really !!

Reply

Answer 5

Jeff Shenk

Level 4

2,993 points

Apr 25, 2015 7:00 AM in response to David Graeme-Baker

It isn't really strange. the one that doesn't work is the sum of a single item (which is the equal to the single item, so SUM doesn't mean anything here.)

That single item is the number 60 plus the range B6:M6. But you can't use the plus operator to add a number and a range (because a range isn't a single value).

Reply

Answer 6

Apr 25, 2015 7:14 AM in response to Jeff Shenk

Hmmm - this is quite a bit to take in !

OK let me ask you about another instance.

I have a column and at the top I have my Income for that month and below I have expenses incurred during that month.

What I am aiming to do is out put the profit i.e. Income minys expenses.

If I do this:

I get an error

But if I do this

I get a result but obviously it shows my Profit as a negative number.

That's why I find it strange.

Simply reversing the order of the values stops the formula working.

In one case the "range" (B3:B8) is producing a number value, and in the other it isn't.

Reply

Answer 7

Apr 25, 2015 7:21 AM in response to David Graeme-Baker

You can see that the way I am getting around it is to have the expenses totalled in the row B8 and then I use that figure (not range) in my calculation.