8 Replies Latest reply: Jul 28, 2014 3:41 AM by Yellowbox
rtemisia Level 1 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
  • John Galt Level 8 Level 8 (41,205 points)

    Screen Shot 2014-07-24 at 11.37.32 PM.png

     

    Screen Shot 2014-07-24 at 11.39.31 PM.png

     

    Screen Shot 2014-07-24 at 11.42.56 PM.png

  • John Galt Level 8 Level 8 (41,205 points)

    I like this one better

     

    Screen Shot 2014-07-25 at 12.21.30 AM.png

  • Badunit Level 6 Level 6 (11,400 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

  • rtemisia Level 1 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!

  • Chris CA Level 9 Level 9 (77,915 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.

  • John Galt Level 8 Level 8 (41,205 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:


    ccc1d9a6bbfa2bcd11f062bbf381272e.png

     

    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.

     

    Drag the screenshot to the Trash after you post your reply.


  • Chris CA Level 9 Level 9 (77,915 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.

     

    Drag the screenshot to the Trash after you post your reply.


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

  • Yellowbox Level 5 Level 5 (7,570 points)

    Hi rtemisia,

     

    Not answering your question about fractions, but this may help you to post a Screen Shot as suggested by John Galt.

    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.