8 Replies Latest reply: Jul 28, 2014 3:41 AM by Yellowbox
Level 1 (0 points)

Hi everyone,

Numbers version 3.2 here

I'm pulling my hair out trying to make Numbers figure out for me how to subtract 1/5 of a currency amount.

For example I want to input say, \$20 in cell A1, 1/5 into cell B1 and I want cell C1 to tell me what is left over when taking 1/5 of \$20, or \$1 out of every \$5. Now, I know that the answer is \$16 - I've been trying to tell the formula that C1 equals A1 divided by 1/5 but it tells me that the answer is \$19.80 because it's reading 1/5 as 0.2 cents! So far I'm having to manually tell it that B1 is \$4 and it's driving me crazy and wasting my time (math is not my strongest point and I'd rather spend my time entering the data than working it out).

I tried changing the data format of my B column gazillions of times to fractions & decimals but it refuses to do this very simple sum.

What am I missing here?

Many, many, many thanks in advance.

MacBook Pro, iOS 7.1.2
• Level 8 (45,958 points)

• Level 8 (45,958 points)

I like this one better

• Level 6 (11,615 points)

Judging by the result of \$19.80, your formula must have been subtracting 1/5 (which is equal to 0.2) from A1, not dividing A1 by 1/5.  \$20 / (1/5) would be \$100.

Forget about it being dollars, the currency symbol and decimal places are just formatting, not part of the math:

20 - 1/5 = 19 4/5 = 19.80

but

20 - (1/5)*20 = 20 - 4 = 16

• Level 1 (0 points)

Hey John,

Thanks so much for your help!!

Somehow, both your suggestions return a warning that the formula contains a syntax error... I can't work it out!

• Level 9 (78,695 points)

rtemisia wrote:

I've been trying to tell the formula that C1 equals A1 divided by 1/5 but it tells me that the answer is \$19.80 because it's reading 1/5 as 0.2 cents!

To be correct, it is reading it as 0.2 dollars or 20 cents.

• Level 8 (45,958 points)

The essential step is to format the cell containing the fraction as "fraction" as in the first screenshot. Format tool > Cell > Data Format > Fraction.

Numbers will interpret an entry of 1/5 as 0.2 or 20%. Then, even though you are seeing "1/5" in cell C2 in the screenshot, the formula in D2 will take \$20, multiply it by 0.2, and you get the second screenshot.

To directly determine what is left over when taking 1/5 of \$20, use the formula in my second reply: D2 = \$20 multiplied by the quantity of one minus C2.

If you're still stuck, post a screenshot of your Numbers entries and formulas like I did.

Screenshots (Mac):

To take a screenshot hold ⌘ Shift 4 to create a selection crosshair. Click and hold while you drag the crosshair over the area you wish to capture and then release the mouse or trackpad. You will hear a "camera shutter" sound. This will deposit a screenshot named "Screen Shot...." followed by a date and time on your Desktop.

When you post your response, click the "camera" icon above the text field:

This will display a dialog box which enables you to choose the screenshot file (remember it's on your Desktop) and click the Insert Image button.

⌘ Shift 4 and then pressing the space bar captures the frontmost window.

⌘ Shift 3 captures the entire screen.

Don't capture or upload any information you consider personal.

• Level 9 (78,695 points)

⌘ Shift 4 and then pressing the space bar captures the frontmost window.

⌘ Shift 3 captures the entire screen.

Don't capture or upload any information you consider personal.

and Command Shift 4 then Spacebar and click & drag will capture only what you drag out.

• Level 6 (8,989 points)

Hi rtemisia,

A picture paints a thousand words...

Have a look at this User Tip for posting Screen Shots:

Wayne Contello has written a User Tip on how to post a screen shot.

https://discussions.apple.com/docs/DOC-6591

Regards,

Ian.