+ Reply to Thread
Results 1 to 6 of 6

Math problem in excel

  1. #1
    Registered User
    Join Date
    03-13-2007
    Posts
    40

    Math problem in excel

    i have a sheet set up to give me certain formulas for our payroll tax. for instance,
    these are the numbers that show in the columns, they seem to rounded up on their own when needed, which is what i want.

    column c = gross = 270.00
    column d = Fed 15% = 40.50
    column e = SS 6.2% = 16.74
    column f = mc 1.45% = 3.92
    column g = State 6% = 16.20
    column i = Net = 192.65

    the problem is this: i have a "Totals" column so that I know how much to send to the IRS each month. It does not add the rounded up figures "shown" but adds the un-rounded figures some how. the problem is that over time these figures are coming up wrong.

    for instance, the mc 1.45% column shown above is actually 3.915. after several of these columns, my figures are wrong. take a look.

    Date: gross: mc 1.45% shown in cell mc 1.45% actual

    3.1.07 1000.00 14.50 14.50
    3.5.07 270.00 3.92 3.915
    3.12.07 100.00 1.45 1.45
    3.19.07 50.00 .73 .725
    3.26.07 50.00 .73 .725

    totals 21.32 21.315

    it gets worse... if you add all of the "shown" mc column, it is actually 21.33

    here is the formula i am using for the "Total MC 1.45%" cell =SUM(F3:F60) now i have another problem, i am suppose to match the s.s. tax and the medicare, so i have another cell that is suppose to simply multiply the "Total MC 1.45%" cell by 2. here is the formula i use for that cell. =K10*2 it says that 21.32 x 2 = 42.63 this is how i discovered this mess in the first place.

    i hope this is understandable, as i know it's a bit hard to explain without seeing it. any help would be really great, as this is my first month to begin doing this and i need to get this thing right.

    thanks in advance.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Try going into Tools > Options > Calculation tab, and at the bottom check the box next to "Precision as displayed"

  3. #3
    Registered User
    Join Date
    03-13-2007
    Posts
    40
    thanks for the help...

    it was a little scary... it said all of the data would be permantly wrong! ha ha

    oh well, it was exactly what i needed to do... all wrong! ha

    here's one more question...

    say i have 10 rows, with the 10th row a sum of the 9 above it. i am using a formula of =SUM(A1:A9) all works well....

    what if i want to add another row between A9 and A10, but i want to do it easily without having to go through a bunch of hoops to get my SUM to continue to calculate to total of all rows, including the new ones. also, is there an easy way to insert rows that follow the same formula.

    thanks you for your help

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Well, first make sure 'Extend data range formats and formulas' is checked in Tools > Options > Edit tab.

    As for increasing the SUM range when inserting a row just above the formula.. instead of using a simple SUM formula, you could try:

    =SUM(INDIRECT("A1:A"&ROW()-1))

    This will SUM all cells from A1 down to the row just before the cell the formula is entered into. This means you can insert rows anywhere above the cell with the formula and it will update for you. I think.

  5. #5
    Registered User
    Join Date
    03-13-2007
    Posts
    40
    the sum idea worked!!!! very cool!!

    as far as the 'Extend data range formats and formulas' , all i find that remotely looks like that is "Extend list formats and formulas", which is already checked and it doesn't work... at least the way i am doing it. i am highlighting row 10, my sum row, and then clicking on insert row... as it wants to put the row above whatever it is you are on at the time. it inserts the row, but with now formulas.

    i really appreciate all your time, as i am fairly new at excel....

    once again, thank you for your time.

  6. #6
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Thanks, glad I could help.

    I didn't think the "extend.." checkbox would fix it.. it didn't on mine. Glad the INDIRECT function worked for you though. I haven't used it for that purpose before, but it does seem to provide a good workaround for situations like that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1