Numbers Formula Help

Question

Level 1

4 points

Numbers Formula Help

Hi

I have another problem with a formula in Numbers. In my spreadsheet, cell G102 gives me the total of all the figures in that column. In cell G103 I want to show 95% of the total in G102. I can do this by using G102-(G102*0.05), this works fine, unless G102 is empty. I then get the error message The operator "-" expects a number, date or duration but found ".".

I've tried using =IF(G102=0,"",G102-(G102*0.05)), but still get the same error. I know my formula works, but I would really like to get rid of that pesky red warning triangle. Any ideas please?

Cheers

Colin.

Mac Pro, OS X Mavericks (10.9.1)

Posted on Jun 4, 2014 7:55 AM

Reply

Answer 1

Yellowbox

Level 7

22,095 points

Jun 4, 2014 8:10 AM in response to ColinBowling

Hi Colin,

What is the formula in G102?

How does G102 sometimes end up empty?

A screen shot of the lower part of yout table will help us to see.

Regards,

Ian.

Reply

Answer 2

ColinBowling Author

Level 1

4 points

Jun 4, 2014 8:27 AM in response to Yellowbox

Hi Ian

G102 has the formula that can be seen in the screen shot. It was formulated by Numbers when I dragged my mouse across those cells. The aim of the sheet is to be used as a template for business expenses, so unless an entry has been made under that kind of expense G102 will be empty (I prefer empty to £0). The two other warning triangles refer back to the cell G103.

The expense in column G is the only one that I need to deduct 5% from and the 105 row shows the totals rounded up to whole £ without any pence. The cell D105 (bottom red triangle) gives the total of the rounded up row.

I hope this helps.

Colin.

Reply

Answer 3

Yellowbox

Level 7

22,095 points

Jun 4, 2014 8:51 AM in response to ColinBowling

Hi Colin,

I have used a Header Row, a Header Column and some Footer Rows to make this more "Numbers like".

Shortened version (not through to column G, but you get the idea).

Formula in B6 (and Fill Right):

=SUM(B)

Formula in B7 (and Fill Right):

=IF(B6=0,"No Values",B6×0.95)

I think that x0.95 is shorter than B6 - B6x0.05 😉.

You can use whatever you like instead of "No Values" to indicate a blank column (of Body Cells). Then use that value as an error trap in further calculations.

Update:

The expense in column G is the only one that I need to deduct 5% from

OK, I missed that. No need to start in B and Fill Right.

Regards,

Ian.

Reply

Answer 4

Yellowbox

Level 7

22,095 points

Jun 4, 2014 8:57 AM in response to Yellowbox

Rethinking:

You can use whatever you like instead of "No Values" to indicate a blank column (of Body Cells). Then use that value as an error trap in further calculations.

How about =IF(B6=0,0,B6×0.95)

Then zero will be zero in further calculations. 😊 blush.

Regards,

Ian.

Reply

Answer 5

ColinBowling Author

Level 1

4 points

Jun 5, 2014 1:28 AM in response to Yellowbox

Hi Ian

Thanks for your replies. I’ve set up a new sheet to use until I can get this worked out. I’ve set it out as your example above.

When I enter =SUM(B) in B6 I get the error message:

This formula can’t reference its own cell, or depend on another formula that references this cell.

Just to recap what I would like referring to the cells on your example and not my original sheet:

All values are currency to two decimal places. B6, C6 and D6 should show the sum of their columns.

B7 only, should show 95% of B6.

B8 should show B7 but rounded up with no decimal places. C8 and D8 should do the same but for C6 and D6.

Finally, B9 should show the total of B8:D8.

Then, and this is the bit that is tripping me up, I would like all of those cells to be empty (not show a zero) when there are no values in their respective columns. I think I must be going about this in the wrong way and I’m willing to start again from scratch if need be.

Incase it makes a difference, the version of Numbers that I am using is 3.2 (1861).

Thank you for your patience with this Ian.

All the best

Colin.

Reply

Answer 6

Yellowbox

Level 7

22,095 points

Jun 5, 2014 1:50 AM in response to ColinBowling

Hi Colin,

Yup, Numbers 3.2

When I enter =SUM(B) in B6 I get the error message:

This formula can’t reference its own cell, or depend on another formula that references this cell.

In my example, there are three Footer Rows. They (and Header Rows) are special and exclude themselves from formulas that refer to a whole column. Therefore they don't reference their own cell.

So, a formula in a Footer Row such as =SUM(B) will sum all the Body Cells in that column (in my example, B2:B5). The beauty of this approach is that you can add or delete Body Rows and the formula will continue to work without having to revise the cell range.

I think if you can get the SUM formulas to work, your other question (show blank instead of zero) will be easier to tackle.

Keep smiling!

Regards,

Ian.

Reply

Answer 7

ColinBowling Author

Level 1

4 points

Jun 6, 2014 2:00 AM in response to Yellowbox

Hi Ian

This looks interesting, I never knew about footers. I'll try it when I get back and let you know what has happened.

Thanks

Colin.

Reply

Answer 8

Yellowbox

Level 7

22,095 points

Jun 6, 2014 9:12 AM in response to ColinBowling

Hi Colin,

For your next trick, you can make a cell appear to be blank even though it contains a number.

B6 (and Fill Right)

=SUM(B)

same for Row 7, but now the trick with D7

Use Conditional Highlighting to make the value the same colour as the cell backgound colour if the expense is zero.

Because we are now talking Footer Rows with a distinctive fill colour, white won't hide the zero. In my example, I had to choose "Mercury" to match the cell background fill. You will have to adapt this if your Footer Rows have some other fill colour.

Click on D7 (In my example)

Click on the rainbow circle to bring up the Colour Window (or do shift command c)

Choose the Crayons Palette, then Mercury:

Zero is now invisible, but is still available for further calculations. No need to convert to blank, then a complex formula to ignore blanks in your overall sum.

Happy Numbering!

Regards,

Ian.

Reply

Answer 9

ColinBowling Author

Level 1

4 points

Jun 10, 2014 8:57 AM in response to Yellowbox

😕Hi Ian

I like Footers, thanks for the introduction! Here's where I am, I don't think I'm much further on, but I am feeling happier about it.

In B6 I have: =IF(COUNT(B)<1,""(SUM(B))

This works fine, if I just used =SUM(B) I was gettng a zero when the column had no entries in it. That formula has been filled right, so down to here works as expected. Thank you.

In B7 where I want to show 95% of B6 I have entered: IF(B6=0,"",B6*0.95).

I would of expected that to give me the result I wanted, but it gives me the error message: The operator “*” expects a number but found “.” which is pretty much what I had at the start of this thread. I tried =IF(B6=0,0,B6×0.95) as you suggested above, but got the same error message. The same thing happened with IF(B6=0,"No Values",B6×0.95).

Row 8 I would like to show the totals of B7, C6 and D6 but whole £s (GBP) only. I've done this by seting the cells as curency with no decimals.

B9 I would like to show the total of (B8:D8), again with no zeros if the total should be 0.

Where am I going wrong? 😕

On a similar note, do you know of a good online resource for Numbers, preferbly something that is lesson based, starting from basic and progressing to more complex sheets?

Thanks again!

Colin.

Reply

Answer 10

Yellowbox

Level 7

22,095 points

Jun 11, 2014 12:26 AM in response to ColinBowling

Hi Colin,

Is this getting close to what you want?

One Header Row (1)

Four Footer Rows (6,7,8,9)

B6 =IF(SUM(B)=0,"",SUM(B))

B7 =IF(B6="","",B6×0.95)

So when alll body cells in B are empty,

C6 =SUM(C)

D6 =SUM(D

B8 =SUM(B7,C6,D6)

B9 I would like to show the total of (B8:D8), again with no zeros if the total should be 0.

I'm not sure what is going into C8 and D8.

a good online resource for Numbers, preferbly something that is lesson based, starting from basic and progressing to more complex sheets?

From the Help Menu, choose Formulas and Functions Help. It is not lesson-based. It appears to reproduce what you can find in the Function Browser.

Regards,

Ian.

Reply

Answer 11

ColinBowling Author

Level 1

4 points

Jun 11, 2014 5:48 AM in response to Yellowbox

Hi Ian

I think you've cracked it! 🙂

On my test sheet I've got B6 and B7 the same as you. I have an extra row witht he title Rounded up as row 8, that is giving me the results of B7, C6 and D6 round to whole pounds. Then in B8 I have =IF(SUM(B8:D8)=0,"",SUM(B8:D8)), simply because my real sheet has more columns and I wanted to see if SUM(B8:D8) would work for me.

I've transfered my formulas onto my original sheet and everything appears to be as it should. The one thing I don't understand though is in B6 =IF(SUM(B)=0,"",SUM(B)) worked but in B7 SUM(B)=0 didn't and we had to change it to SUM(B)="".

Thank you so much for your help and patience it is very much appreaciated.

Cheers

Colin.

Reply

Answer 12

Yellowbox

Level 7

22,095 points

Jun 11, 2014 6:18 AM in response to ColinBowling

Hi Colin,

Happy to help (and I am learning as I go).

The one thing I don't understand though is in B6 =IF(SUM(B)=0,"",SUM(B)) worked but in B7 SUM(B)=0 didn't and we had to change it to SUM(B)="".

That is because

B6 =IF(SUM(B)=0,"",SUM(B))

B7 =IF(B6="","",B6×0.95)

If SUM(B)=0, B6 becomes "" (NULL) not zero.

So the question (does B6 equal zero?) returns false. It is not zero, but NULL

Then in B8 I have =IF(SUM(B8:D8)=0,"",SUM(B8:D8)), simply because my real sheet has more columns and I wanted to see if SUM(B8:D8) would work for me.

Another use for Header Columns. As you expand the number of columns, a Header Column can refer to the whole of that Row:

SUM(9) is the SUM of Row 9. Click on the Row label 9 to insert that reference.

You can round off to whole pounds by setting cell formats to Currency with decimals 0, or use the ROUND function.

Happy Numbering!

Regards,

Ian.

Message was edited by: Yellowbox. Fixed a typo

Reply

Answer 13

ColinBowling Author

Level 1

4 points

Jun 11, 2014 6:59 AM in response to Yellowbox

Hi Ian

Another use for Header Columns. As you expand the number of columns, a Header Column can refer to the whole of that Row:

SUM(9) is the SUM of Row 9. Click on the Row label 9 to insert that reference.

That's handy, I think I may move my Total into a header row.

Cheers

Colin.

Reply