# Math problem in excel

1. ## 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.

2. Try going into Tools > Options > Calculation tab, and at the bottom check the box next to "Precision as displayed"

3. 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.

4. 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. 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. 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.

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

#### 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