Totals not coming out correct.

**Shadow-Warrior** · 12-17-2008, 06:19 PM

I have been working on a Spreadsheet for GVWR (Gross Vehicle Weight Ratings). I'm having a problem with the Formulas in Excel, I thought you may know what's going on and why it's not giving me the right data. I'm pretty good with Excel, I've been using it for about 17 years for calculating Tank formulas, etc, but have never run in to this before.

I have three columns to the right of the sheet. One is Stock Vehicle weight of an item____(E) The next column is the Aftermarket weight of the item______ (F) Then the third column (G) is the total weight minus the stock weight using a very simple formula =SUM(E6-F6) gives the correct weight in the third column, but at the end of each area (Topic) i have a Total Weight area in the third column (G), using the formula of =SUM(G6:G26) which should just add up everything in column G, but it doesn’t. I have re-made the spreadsheet three times, I can't figure out what I'm doing wrong...any idea's. I know it's smoething real simple that I'm missing. I feel like such a N00b. :lol:

Thanks

Mike

**Kieran** · 12-17-2008, 07:18 PM

The formula in G seems to be backwards from your explanation.
Total weight minus the stock weight would be =F6-E6, not =E6-F6 (note the SUM() formula is not required here.

If this error is the case and you have amended the cell formats in column g to not show negative numbers clearly, then the Topic total could be incorrect.
Try selecting the range G6 to G26 and format the cells as general. It should show up if there is an error ins displaying negative numbers.

**Mikeopolo** · 12-17-2008, 07:20 PM

Can you post a sample worksheet?

**Shadow-Warrior** · 12-17-2008, 08:42 PM

Originally Posted by Kieran

The formula in G seems to be backwards from your explanation.
Total weight minus the stock weight would be =F6-E6, not =E6-F6 (note the SUM() formula is not required here.

If this error is the case and you have amended the cell formats in column g to not show negative numbers clearly, then the Topic total could be incorrect.
Try selecting the range G6 to G26 and format the cells as general. It should show up if there is an error ins displaying negative numbers.

I just tried what you said, I made it general and also removed the sum in the G column, it still is reducing the total at the buttom and you can see the formula in the upper left corner of what the total is? I don't know what's happening, it's a simple addition and subtraction formula, but it will not total at the bottom, it will subtract instead. I even made it a General" instead of "Number" with no -. I did a screen capture, I hope you can see what I'm talking about.

\1

Thanks everyone.

Mike

**Shadow-Warrior** · 12-18-2008, 11:18 AM

Any Idea's Thanks

Mike

**Mikeopolo** · 12-18-2008, 01:42 PM

The total in G21 appears to be -3, which seems to be correct.
This appears to be made up of -5 in G10, and +3 in G13.
The -5 in G10 arises because there is no after-market weight in F10.

Does this help?
Regards
Mike

**Shadow-Warrior** · 12-18-2008, 01:50 PM

Originally Posted by Mikeopolo

The total in G21 appears to be -3, which seems to be correct.
This appears to be made up of -5 in G10, and +3 in G13.
The -5 in G10 arises because there is no after-market weight in F10.

Does this help?
Regards
Mike

Not really, that is the way it's supposed to be. You need to look at the bottom at the Total where is shows "2" instead of adding the total, it's subtracting it. You can see in the upper left corner the formula which should be correct, but it's not adding them up, it's deducting the numbers.

I do want it to deduct the weight from a stock item then give the left over weight, which I then can add up to see exactly how much weight has been added. Make sense?

G21 needs to add up all the numbers in the "G" column, but it's subtracting. What am I doing wrong?

**Mikeopolo** · 12-18-2008, 01:55 PM

Sorry, I should have written: The total in G21 appears to be -2, which seems to be correct.
The -2 in G21 appears in red, as does the -5 in G10, whereas the +3 in G13 appears in black.
If you format column G to general, it should show the minus sign.
Do you agree that G10 shows -5?

**Shadow-Warrior** · 12-18-2008, 02:02 PM

Originally Posted by Mikeopolo

Sorry, I should have written: The total in G21 appears to be -2, which seems to be correct.
The -2 in G21 appears in red, as does the -5 in G10, whereas the +3 in G13 appears in black.
If you format column G to general, it should show the minus sign.
Do you agree that G10 shows -5?

I think I see what your saying, because it's in red means it's (-5) that make sense, but not what I want it to do, it should be in black as a 5 lbs. not minus 5 lbs, this maybe the problem then. If that's the case, how do i make it read so if there is no other number to deduct, it dosen't make it a minus and just keep the orginal weight "5" Thanks for your help Mike

Mike

**Mikeopolo** · 12-18-2008, 02:07 PM

OK that's progress!
Make the formula in G8: =max(E8,F8), and copy to all the data rows of that column.
This will show the original weight if no aftermarket weight is provided.
Regards
Mike

**Shadow-Warrior** · 12-18-2008, 02:07 PM

Originally Posted by Shadow-Warrior

I think I see what your saying, because it's in red means it's (-5) that make sense, but not what I want it to do, it should be in black as a 5 lbs. not minus 5 lbs, this maybe the problem then. If that's the case, how do i make it read so if there is no other number to deduct, it dosen't make it a minus and just keep the orginal weight "5" Thanks for your help Mike

Mike

So what your saying is I'm using the wrong formula in the "G" column? What should I use instead of a =(F8-E8) ?

**Shadow-Warrior** · 12-18-2008, 02:16 PM

Originally Posted by Mikeopolo

OK that's progress!
Make the formula in G8: =max(E8,F8), and copy to all the data rows of that column.
This will show the original weight if no aftermarket weight is provided.
Regards
Mike

Thats It! Thank You, Thank You! Your the man Mike! Thank you sir, this had me baffled for the last four days, I tired all kinds of things, but that. Thanks again.

Mike

**Shadow-Warrior** · 12-18-2008, 02:18 PM

Originally Posted by Shadow-Warrior

Thats It! Thank You, Thank You! Your the man Mike! Thank you sir, this had me baffled for the last four days, I tired all kinds of things, but that. Thanks again.

Mike

Opp's, not to fast, I was wrong, now it's not deducting the numbers in Column E it's just keeping what's in "F"....bummer, I thought we had it.

Mike

**Shadow-Warrior** · 12-18-2008, 02:30 PM

Ok, let's take it from a different angle. Let's start fresh, here is what I want to do, and you tell me how to accomplish this.

I want to have a number in column “E”, I then want to input a different number in Column “F” then have Column “G” subtract both E & F giving a final number. Then I want to total the numbers at the bottom in column G.

I thought it would look like this E = No Formula, F = No Formula, G =MAX(E8-F8), then Total of G =SUM(G8:G19)

**Mikeopolo** · 12-18-2008, 02:34 PM

Sorry for my poor logic
I think you want the extra weight in G, and it should read 0 if there is no extra weight

Try this in G8 and copy down:
=max(f8-e8,0)

**Shadow-Warrior** · 12-18-2008, 02:45 PM

Originally Posted by Mikeopolo

Sorry for my poor logic
I think you want the extra weight in G, and it should read 0 if there is no extra weight

Try this in G8 and copy down:
=max(f8-e8,0)

It works, but if there is a number by it self in column "E" column "G" dosen't see it and keeps a "0" there.....

Mike

**Mikeopolo** · 12-18-2008, 02:47 PM

Ok, but what does column G mean - its heading says "Added weight", so if there's no aftermarket weight, the added weight is 0?

**shg** · 12-18-2008, 02:47 PM

A suggestion: rather than trying to figure out formulas, perhaps you could explain exactly what each column represents, and what result you're trying to calculate.

Addition and subtraction shouldn't be this hard.

**Shadow-Warrior** · 12-18-2008, 02:54 PM

Originally Posted by Mikeopolo

Ok, but what does column G mean - its heading says "Added weight", so if there's no aftermarket weight, the added weight is 0?

True, the last formula does work, I made the mistake of inputing a number in E when there was no reason I should unless there was another number for F. Thanks Mike, that is the correct formula. It works.

Originally Posted by shg

A suggestion: rather than trying to figure out formulas, perhaps you could explain exactly what each column represents, and what result you're trying to calculate.

Addition and subtraction shouldn't be this hard.

I agree, I guess it's kind of hard sometimes to get a point accross by keyboard. It's easier to speak and explain yourself a lot better. LOL

Thanks everyone, especially you Mike.

Mike