Results 1 to 5 of 5
  1. #1
    CathyNC
    Guest

    Dividing in Excel

    Please help I am going crazy trying to find this.
    I have a spread sheet (I hope I say this okay without being to confusing) and in row F11 I have a formula E11/D11. Then in H11 I have another formula =Sum(G11/F11). However the product that shows up in H11 is off by 4,000+ dollars. Is there a way to do this all in one formula in H11. Something like =Product (E11/D11)/(G11/F11))??? something to that effect anyway. I have tried all different ones and I can't seem to get it to work.
    HELP!!!!!!!!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Dividing in Excel

    Hi Cathy,

    It is not clear exactly what operation you are trying to perform in H11. just to clarify the use of the symbol / means division, so in F11 you are dividing the contents of E11 by D11. In H11 you are dividing the contents of G11 by the contents of F11 (I cannot see the need for SUM() in that expression).

    If you want the product of those 2 operations in H11 you could use =(E11/D11)*(G11/F11).

    If I have not understood what you are trying to achieve please come back.

    Andrew

  3. #3
    CathyNC
    Guest

    Re: Dividing in Excel

    Okay I'll try to be a little more clearer. I was afraid I sound confusing. This is a spreadsheet of a co-worker who put in the original formulas. Example, in B I have a number I plug in 50, then next column C I put in 3, the next column D is a formula where I divide =SUM(B11/C11). In E I put in 61, in F I again have a formula =E11/D11, in G I plug in 1452199 and in H I again have a formula =SUM(G11/F11). All the formulas come out right until H, it is off by over 4,000. Oh and I am trying to divide. I'm not sure why he has the SUM in the formula, because in other places he doesn't. Also it's not just this one, it is the whole column that the figures are off. I just thought if I combined the equation in this field it would work. Because in column F it comes to 3.7, if I take out the formula and just type in 3.7 the correct amount comes into H. Just an FYI D and E are set to be numbers with 1 decimal place.

    Thanks so much for any help you can give me.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Dividing in Excel

    Hi Cathy,
    I think the problem you've got is a rounding error. From the numbers you've given column F evaluates to 3.66 but I think Excel is only displaying one decimal place so it shows 3.7 - even though the value it actually stores is 3.66. This means that in column H, the result you get is actually 1452199/3.66 = 396,775.68 whereas I guess you're expecting 1452199/3.7 = 392,486.22.
    The upshot is that Excel is actually calculating everything correctly, it's just not using exactly the numbers it's displaying. If you want it to use 3.7 rather than 3.66 you can change the formula in F to read =ROUND(E11/D11,1) which will actually round up the value Excel stores rather than just the displayed value.
    Incidentally, you don't need the =SUM() bit in any of your formulae but they're not actually doing any harm.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    CathyNC
    Guest

    Re: Dividing in Excel

    {{{{{{{{Rory}}}}}}} Thank you so much. That did it. You are great!!!!
    LOL can you tell you just made my day :-}

Posting Permissions

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