Formulas within a spreadsheet

Question

Level 1

8 points

Formulas within a spreadsheet

Having used Excel for years I am now trying to convert to Numbers. What I need is very very simple but I can't get it. I am tracking sales for my commissions. I have columns headed as A-Job ID #; B-Cust Name; C-Sales amount; D-Front Comm; E-Back Comm.

The formula I want is this: The formula will be in D-Front Comm to read as =sum C10(sales amount)*0.08/2 The problem I keep having is that Numbers changes the C10 (sales amount) in the formula to A10 (Job ID #). It is then calculating my commission on the Job ID# in Column A and not the Sales Amount in Column C. I used this in Excel for many many years, This has me stumped. HELP!!! Thanks. Pam

MacBook Pro 15", macOS 10.15

Posted on Nov 18, 2019 5:10 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

36,134 points

Nov 18, 2019 5:27 PM in response to pmineo

Is this what you are looking for?

The formula in D2, filled down.

=C2*0.08÷2

Row 5 is defined as a Footer Row. Click the row number at the left and choose Convert to Footer Row from the contextual menu that appears.

The formula in D5 is =SUM(D)

SG

Reply

Answer 2

SGIII

Level 8

36,134 points

Nov 20, 2019 4:08 PM in response to pmineo

I suspect what may be going on is that in Numbers > Preferences > General you have 'Use header names as labels' checked.

Uncheck that box and you should see something similar to my screenshot.

This is a discussion area for Numbers for Mac. If you're on iPad OS you don't have option to turn that off. Turning it off makes it less confusing for discussions here.

SG

Reply

Answer 3

pmineo Author

Level 1

8 points

Nov 20, 2019 2:31 PM in response to SGIII

Almost. I don't need the total 8.8. AND Taking what you have hi-lited as soon as I would put in an ID # the C2 in the formula is replaced by the number that is put in the ID # column. So if I put 12345 in the ID column the formula changes to read Sales Amount 12345*0.08/2. If I put nothing in the ID column and put in Cust name it pulls the name and completely ignores the formula as written using the cell of C2.

It's insane. I can't figure out why or what I may have done. I have even started a new sheet thinking I did something that totally tainted the original sheet.

Reply

Answer 4

Nov 20, 2019 3:32 PM in response to pmineo

That makes absolutely no sense at all. If the formula refers to cell C2, changing the value in A2 should not affect it in any way. Unless you are moving columns around and breaking references, there is no reason why your reference to column C should ever change. Can you show us a portion of your sheet so we can see what you are doing?

Reply

Formulas within a spreadsheet

Similar questions