Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Dividing then Multiplying in One Cell

This is a discussion on Dividing then Multiplying in One Cell within the Excel Questions forums, part of the Question Forums category; Hi there, My question is as follows: I have data which I need to divide, then multiply by a percentage. ...

  1. #1
    New Member
    Join Date
    Aug 2010
    Posts
    28

    Default Dividing then Multiplying in One Cell

    Hi there,

    My question is as follows: I have data which I need to divide, then multiply by a percentage.

    For example: I wish to divide A1 by B1, then multiply this total by a percentage in C1. Preferably, I would like to do all of this in one cell.

    I have been trying A1/B1*C1, but I believe I need to seperate the first calculation from the second one?

    Is this relatively easy to do? Any help would be appreciated! I am using Excel 2007 and happy to provide any further information.

  2. #2
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,650

    Default Re: Dividing then Multiplying in One Cell

    That formula works fine

    Sheet4

     ABCD
    11001010%1

    Spreadsheet Formulas
    CellFormula
    D1=A1/B1*C1


    Excel tables to the web >> Excel Jeanie HTML 4
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,211

    Default Re: Dividing then Multiplying in One Cell

    Looks like you are after one of these...

    =(A1/B1)*C1

    =(A1/B1)*(1+C1)
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    4,912

    Default Dividing then Multiplying in One Cell

    It's not clear what you are trying to do, an example may be easier to help solve.

    However,
    Code:
    =A1/B1*C1
    Is mathematically identical to
    Code:
    =(A1/B1)*C1
    Since a division operation supersedes a multiplication one


  5. #5
    New Member
    Join Date
    Aug 2010
    Posts
    28

    Default Re: Dividing then Multiplying in One Cell

    Hi there,

    Thank you for such a quick response. It seems my method is therefore incorrect, as opposed to the formula used.

    Essentially, I want the following:

    A1/B1 + C1 (C1 is a percentage of the total of A1/B1).

    So, if A1 is 400, B1 is 0.75 and C1 is 20%, I want to first do the following:

    400/0.75

    Then work out what 20% of this figure is and add it within the cell, giving me a total of 640.

    Apologies if this isn't very clear - Mathematics has never been my strong point, unfortunately.

  6. #6
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    4,912

    Default Dividing then Multiplying in One Cell

    Ty:
    Code:
    =(A1/B1)*(1+C1)
    This is what Aladin suggested in his post


  7. #7
    Board Regular
    Join Date
    Mar 2011
    Location
    USA USA USA USA
    Posts
    556

    Default Re: Dividing then Multiplying in One Cell

    Quote Originally Posted by H7H12 View Post
    Hi there,

    Thank you for such a quick response. It seems my method is therefore incorrect, as opposed to the formula used.

    Essentially, I want the following:

    A1/B1 + C1 (C1 is a percentage of the total of A1/B1).

    So, if A1 is 400, B1 is 0.75 and C1 is 20%, I want to first do the following:

    400/0.75

    Then work out what 20% of this figure is and add it within the cell, giving me a total of 640.

    Apologies if this isn't very clear - Mathematics has never been my strong point, unfortunately.

    JackDanIce's formula does this exactly.


    =(A1/B1)*(1+C1)
    "Frankly my dear, I don't give a ****." --Rhett Butler

    If posting code, use
    code tags...

    All posts based on Excel 2003

  8. #8
    New Member
    Join Date
    Aug 2010
    Posts
    28

    Default Re: Dividing then Multiplying in One Cell

    Perfect! Thank you so much, JackDanIce, Desu Nota and Aladin!

    Hope you don't mind me asking, but can you recommend anywhere where I can read about what's happening within the formula, with the *(1+C1)? The name of the calculation will be great - Just so I can learn more about it!

  9. #9
    Board Regular
    Join Date
    Mar 2011
    Location
    USA USA USA USA
    Posts
    556

    Default Re: Dividing then Multiplying in One Cell

    Quote Originally Posted by H7H12 View Post
    Perfect! Thank you so much, JackDanIce, Desu Nota and Aladin!

    Hope you don't mind me asking, but can you recommend anywhere where I can read about what's happening within the formula, with the *(1+C1)? The name of the calculation will be great - Just so I can learn more about it!

    It is just math!

    You wanted to get 20% of the total, then add it to the value A1/B1;doing this method requires two steps.

    Example:

    1) 400/.75 = 533.33

    2) 20% of 533.33 is the same as saying 533.33*0.20 = 106.66

    3) 533.33 + 106.66 = 640


    instead:

    1) 400/7.5 = 533.33

    2) 533.33*(1+20%) which is 533.33*(1+0.20) = 640

    Equation two is saying essentially multiply 533.33 by 120% (or by 100% + 20%).





    If you want to give this a name it would be the distributive property (I guess ) A(B+C) = AB + AC


    533.33(1+0.20) = 533.33(1) + 533.33(0.20) = 533.33 + 106.66 = 540

    Hope that clarifies it!
    Last edited by Desu Nota from Columbus; Jun 20th, 2011 at 01:36 PM.
    "Frankly my dear, I don't give a ****." --Rhett Butler

    If posting code, use
    code tags...

    All posts based on Excel 2003

  10. #10
    New Member
    Join Date
    Aug 2010
    Posts
    28

    Default Re: Dividing then Multiplying in One Cell

    Wonderful - thank you! Told you my maths wasn't very good .

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com