Microsoft Excel 2007Sum if >0

Thank you so much for your help; I used this formula, as you suggested:

=0+IF(E3>0,E3,0)+IF(I3>0,I3,0)+IF(M3>0,M3,0)+IF(Q3>0,Q3,0)+IF(U3>0,U3,0)+IF(Y3>0,Y3,0)+IF(AC3>0,AC3,0)+IF(AG3>0,AG3,0)+IF(AK3>0,AK3,0)+IF(AO3>0,AO3,0)+IF(AS3>0,AS3,0)

However, it is returning the total of the negative numbers as well (i.e. the final product is something like -14000.)

Any ideas why it would be doing this?

Again, thank you!!

Sorry for the delay, (time difference for me here in the UK).

That's strange. I don't use Excel 2007 myself as I am still on Excel 2002, (XP), but the formula should still work because it is just a simple IF statement. Without your actual figures, I can't check your spreadsheet's math, so I did some more testing myself.

Since your target cells are spread out and are not consecutive, I tested a couple of ways. I used the same cells as you have quoted, but with my own figures, eg;
E3= 10, I3= -50, M3= 23, Q3= 0, U3= -420, Y3= -510, AC3= 740, AG3= 5, AK3= 44, AO3= -2, AS3= 410

Then I copied these to B12 to B22 to create a simple, one column, list, eg;
B12= E3
B13= I3
B14= M3
B15= Q3
B16= U3
B17= Y3
B18= AC3
B19= AG3
B20= AK3
B21= AO3
B22= AS3

Doing a SUM function on (B12:B22) gives 250, so that is my test figure. It adds them all, including zeros and minus figures.

Using the formula I gave on the Row 3 figures, eg;
=0+IF(E3>0,E3,0)+IF(I3>0,I3,0)+IF(M3>0,M3,0)+IF(Q3>0,Q3,0)+IF(U3>0,U3,0)+IF(Y3>0,Y3,0)+IF(AC3>0,AC3,0)
+IF(AG3>0,AG3,0)+IF(AK3>0,AK3,0)+IF(AO3>0,AO3,0)+IF(AS3>0,AS3,0)

That gives me 1232. That means it just adds up the positive numbers.

As a double check, using a similar formula on the Col B figures, eg;
=0+IF(B12>0,B12,0)+IF(B13>0,B13,0)+IF(B14>0,B14,0)+IF(B15>0,B15,0)+IF(B16>0,B16,0)+IF(B17>0,B17,0)
+IF(B18>0,B18,0)+IF(B19>0,B19,0)+IF(B20>0,B20,0)+IF(B21>0,B21,0)+IF(B22>0,B22,0)

That gives me 1232, the same result.

So, I am mystified. Can I ask, where do your figures in the Row 3 cells derive from? If there is anything unusual about those cell contents, that might account for the discrepancy. But I can't see what would cause that.

Can you do an error check audit? Excel 2007 has a similar function to Excel 2002 called "Formula Auditing > Evaluate Formula", which can step you through what Excel evaluates each part of the formula to.

That Formula Auditing isn't in the same place in E2007 that it is in E2002, so have a look at the guidance in the link below, to get you there;
http://www.dummies.com/how-to/content/understanding-excel-2007s-formula-auditing-tools.html

It's called "Dummies.com". Don't worry about that name though, I'm sure it's not meant to be personal,

Can you step through your formula, and see if you can find out where E2007 is making any errors? In truth, Excel doesn't normally make errors and we generally find that there's an error or unexpected consequence of a cell we completed.

Let me know if you find anything.

Mark

Hi, Mark;

Thank you so much for investing so much time in researching my current problem! I am really grateful; it?s been helpful.

Here?s some more information; the spreadsheet is for determining mileage on vehicles throughout the year (I help to manage a fleet). The format goes something like:

A1: Vehicle number B2: Vehicle Starting Mileage for January C2: Vehicle Ending Mileage for February D2: Total Mileage for February using IMSUB(C2,B2).

My formula repeats itself using all twelve months, then has figures at the end for totals (total yearly mileage, average weekly mileage, etc).

The problem is that I am pulling the mileage information from driving reports that people submit to me; if, for instance, the reports are not submitted during January, the beginning and ending mileage will be ?0?, and if a half-month is missing, it will be even more difficult (i.e. the ending mileage for November might be 500 miles different from the next report I have, from mid-December).

So I added an additional column to each month (it would be E2 in the above representation) with the formula: ?=IF(C2:D2>0,IMSUB(G2,D2),0)? That way I get the difference between the beginning and ending mileage of the two months.

The formula I originally asked for help about is the one totaling all of the additional columns I added (E2 and similar). So yes, it is based on a formula, and maybe the formula I have for those cells is messed up. Maybe that is throwing off the calculations, although I don?t think it should.

The reason there are negative numbers to begin with is because one month will have ?0? mileage records, then the next month will have the normal mileage reading (i.e. 14,275) and when the IMSUB goes to formulate things, it takes the 14,275 away from the 0, resulting in a negative number.

I don?t know if it would be easier to email you a Word ?02 compatible version of the whole form and have you look at it from there, because, frankly, my explanations above are a little bit hard to understand. Sorry about that.

Is the problem in the previous formula (?=IF(C2:D2>0,IMSUB(G2,D2),0)?)? I am not very good at formulas in Excel, so it wouldn?t surprise me the least bit if that formula is somehow not working.

Anyway, thank you again, so much!

I did try the Excel 2007 auditing and error checking steps, and either I didn't understand what it was saying, or else there is nothing wrong.


Also, if you need to contact me to get a copy of the spreadsheet itself, my email address is nhearn AT cityofhesperia.us (replace spaces and 'AT' with '@').

Thank you!

Nicole

Message was edited by: admin to edit email address to prevent spam harvester from picking it up.

I wonder if that is the right function to use here. I am aware of it but have never used it myself, as it is an Engineering Function for handling complex numbers. Is that appropriate in your case?

Here's another discussion about IMSUB in these forums last year;
IMSUB.

I think I can see where this problem lies though. Disregarding IMSUB, you need the total mileage done for each fleet car, for each month. That's a simple C2-B2 calculation, where C2 is the mileage at the end of the month, and B2 is the mileage at the beginning of the month. The 'end' of the month figure is not necessarily the 'beginning' of the next month's figure, especially if the drivers do not submit their figures in time.

This means that each 'beginning' of the month should have a default figure of 0, unless you change it to input 'actual' figures when you finally get them. The fault of that is not yours, it is the drivers who delay passing you the figures.

With a default figure of zero, you should not get any minus figures. In your example, if one month, (eg January), has 0 mileage, and the next month,(eg February), has 14,275, that 'next month' calculation will be (End figure at end of Feb = 14,275 minus Start figure at beginning of February = 0), or 14,275 - 0 = 14,275

So looking at your A row again, you need additional columns, eg;

A1 Vehicle #?
B1 Starting mileage at 1/1/09 (default=0)
C1 End mileage at 1/31/09
D1 Total mileage for vehicle #? for January = C1 - B1
E1 Start mileage at 2/1/09 (default=0)
F1 End mileage at 2/28/09
G1 Total mileage for vehicle #? for February = F1 - E1
{repeat as necessary}.

Of course, these are all in the same row, but these forums do not tabulate well.

Does that help? If not let me know.

I have asked our Forum Admin, Lee, to remove your email address from your post. Bots trawl these and other forums for email addresses for spam purposes, so we recommend not posting them.

Mark

it helps to break it down in steps.

Below the numbers make a new row, with formulas like
= max (a1,0) in b1
= max (a2,0) in b2
etc
Max is a somewhat simpler way of getting only positive numbers. Negative numbers will become 0.

Then sum this new calculated cells to another cell (=sum(b1,b2,etc))

Then hide the new row.

Three easy steps that are easy to check also. There might be smarter ways I've never mastered, but this should surely work.

Kees

Thank you so much for your continued help!

Mark, I was able to successfully remove the IMSUB from the beginning and ending calculations, using the C2-B2 calculation you showed below. Thanks!

There is one complication left, with both your advice and Kees??.I think I?m not understanding exactly what you?re suggesting.

The current formula (using C2-B2 calculations) is:
C2: 80873 (January beginning mileage)
D2: 81530 (January ending mileage)
E2:
F2: C2-D2 (Total January Mileage)
G2: 81536 (February beginning mileage)
(etc.)

What I would like to see in cell E2 is the difference between G2 and D2 (in this case, it should be 6 miles). If I did a simple subtraction calculation, that would work great (G2-D2=6), but the complication would arise if, for instance, nobody submitted reports for February; at which case, the G2 column would change:

G2: 0
Which means E2 would change: (0-81530 would equal -81530).

How can I get a formula for E2 that says:

If G2>0
And if D2>0,
Then G2-D2

I can do one or the other (=if(G2>0,G2-D2,0) or =if(D2>0,G2-D2,0)) but I can?t do both, and it really needs to do both, because it is just as often that G2 will be blank, as it is that D2 will be blank.

Which brings me to another concern, that I know there must be some solution to, but it seems to make my head spin thinking about it; if we take the chart to three months instead of the two-month timeline we?ve been summarizing below, the numbers might look like:

C2: 80873 (January beginning mileage)
D2: 81530 (January ending mileage)
E2:
F2: C2-D2 (Total January Mileage)
G2: 0 (February beginning mileage?no reports submitted)
H2 (February ending mileage?no reports submitted)
I2:
J2: H2-G2 (Total February Mileage)
K2: 81575 (March beginning mileage)
L2: 82360 (March ending mileage)

As you can see, the numbers would be completely faulty even if the E2 and I2 columns correctly factored in only positive numbers: for instance, since January ends at 81530 and March begins at 81575, there are 45 miles unaccounted for, but both of the E2 and I2 columns would say that there is no mileage unaccounted for.

I?m sure there?s some way to do it, as Excel can do practically anything, but I?m beginning to wonder if I?m in over my head with my current expectations for this form!

Thank you again, both of you, for all you?ve done to help out with this project. I am so grateful!

(Also, thanks for saving me from the wicked and evil trolls, Lee and Mark.)

No problem.

Just a small correction from your post. You calculated cell F2 as C2-D2, but of course it would have to be D2-C2, leaving a distance traveled in January of 657 miles, instead of -657 miles.

Nicole, I''m not sure how much further we should go with this. Your original post was about a problem with minus figures, and I feel we have all, yourself included, worked through that difficulty.

The issues you now have are, it seems to me, related to late notification of mileage traveled figures from your car fleet service. The trouble you are experiencing is that you cannot second guess what those figures are going to be, and so anything you enter into the appropriate cells, (the 'first of the month' and 'last of the month' cells), are going to be incorrect and will cause problems down the line, unless you get the actual and accurate figures. That's a management problem with processes, and not an Excel problem.

But realizing this, that's why I suggested entering default figures of 0, (zero), into the cells where data is to be collected, so that, if data is late, at least the formulas still work. Entering that data later should automatically correct the figures and analysis you need.

One thing I see from your latest post above, is that E2, I2, M2, etc, is not needed. They are blank and serve no purpose because you now have start and end mileage figures for each month, instead of your previous method of using the 'End of month' mileage as the figure for the start of the next month. If 0 is entered in all of the mileage data cells initially by default, then you will get no minus figures.

Correction. If mileage is entered in a particular month's 'Start' cell, but you receive no data for the end mileage for the end of that month, then you will get a minus figure. But again, that is the fault of the reporting system, and not of the spreadsheet, and to avoid that, you re-enter 0 in that Start cell for the time being.

You're right that, if one month's figures are missing, then the next month's figures will not be accurate. But as I said, you can't double guess that, and Excel can't fix it for you.

What you can do, but this depends on your working relationship with your boss/team-leader if you have one, is to show them this professional looking spreadsheet, and show them the raw data, so that they can see where the problem lies with the late reporting of the mileage figures. I am fairly sure that that person will;

1] Be impressed by the spreadsheet, (most bosses I know have never attempted to create one themselves), and

2] Be able to see the 'physical' problems of the system breakdown, the reporting delay.

Perhaps there is something you can do to highlight any questionable figures.

If you are using charts, use bar charts. That way, if any vehicle is not shown as being used in any month, the bar for that month will be missing. In addition, if that month's data is missing but the next month's data is given, then the bar for the next month will be much higher, perhaps off-scale. That's a great visual tool for highlighting problems.

Whilst I have highlighted apparent working practices problems, don't hesitate to come back again if you do have Excel problems.

Mark