Results 1 to 5 of 5
Thread: Dividing in Excel

20010410, 20:00 #1CathyNCGuest
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!!!!!!!!

20010410, 20:23 #2
 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

20010411, 11:28 #3CathyNCGuest
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 coworker 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.

20010411, 11:43 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 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

20010411, 12:34 #5CathyNCGuest
Re: Dividing in Excel
{{{{{{{{Rory}}}}}}} Thank you so much. That did it. You are great!!!!
LOL can you tell you just made my day :}