Microsoft Excel 2007 -summing IF eqn. results

Dell Inspiron 1545
December 23, 2010 at 07:11:25
Specs: Windows Vista, 1.795 GHz / 4055 MB
In Microsoft Excel 2007, I'm trying to sum the results of a IF equation but I keep on obtaining an answer of 0 (the results are either 0 or 1) so the answer cannot be 0. I've tried the sum function elsewhere in the worksheet and it is working fine. The only way I have found that I can sum the IF equation results is by adding each cell individually and as there is over 52,000 cells this isnt feasible. Any help on this issue would be greatly appreciated as I can't seem to solve it.

See More: Microsoft Excel 2007 -summing IF eqn. results

Report •


#1
December 23, 2010 at 09:12:58
Since you haven't posted any example data, or any of the formulas you've tried, there's no way we can help you.

Your post can essentially be reduced to this:

"My formula isn't working. Please help."

There's not much we can do with that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 23, 2010 at 11:00:44
Sorry its very similar in nature to the problem that jamer02 was having. The excel sheet is being used to analyse wind data results. The worksheet has a list of wind speeds that were recorded every 10 minutes at a height of 15m over an entire calender year. I want to check whether there are 2,500 hrs of wind speed at >2.5 m/s. To find out if the data passed this criteria, I set up an IF equation to let the cell =1 if true and let the cell =0 if false. Now I have a column with a series of 1s and 0s. I want to sum this column to find out how many 1s (represent 10 mins) I have so that I can determine whether I have 2500 hrs of wind speeds @ > 2.5 m/s. When I use the sum function it returns a 0 for this column, although it works everywhere else on the sheet.

Report •

#3
December 23, 2010 at 12:28:11
re: "Sorry its very similar in nature to the problem that jamer02 was having"

We get hundreds of questions in this forum and can't be expected to remember every question we see.

I'll repeat what I said earlier:

Since you haven't posted any example data, or any of the formulas you've tried, there's no way we can help you.

Telling us that you have "windspeeds" and how often they were recorded doesn't help us help you.

Telling us that you have data that looks like this...

    A
1  2.5
2  1.4
3  3.6

...and that you have a formula that looks like this.

=IF(A1>2.5,....)

...might just give us the information we need.

We can't see your spreadsheet from where we're sitting nor can we read minds. You need to give us enough information to work with if you want our help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 23, 2010 at 12:59:40
Sorry I posted you the link earlier today with that webpage, where this problem was being discussed.

Data is as follows:

15 metre-10 min Average: 3.3728 (B13) 2.507302 (B14)
2.810747 (B15)
2.0221165(B16)

IF Equation:
=IF(B13>2.5,"1","0")
=IF(B14>2.5,"1","0")
=IF(B15>2.5,"1","0")
=IF(B16>2.5,"1","0")


Ans from IF Eqn:
1
1
1
0

I'm at the point where I have a column full of 1s and 0s from the IF equation, now I want to sum the results to determine if in one calender year I have 2,500 hours of wind speed at > 2.5. When I use the formula =SUM(B13:B16) it returns an answer of 0. As I outlined above the sum formula is working fine elsewhere in the sheet to sum other columns but for this column that I'm interested in it keeps returning a value of 0. I've tried copying and paste special but it didnt work. Instead of summing the entire 52,000 cells, I tried just summing 3 cells but the answer was still incorrect.

As I stated above it will only sum them correctly if i select the cell and use the plus sign and then select the next cell, which isnt an option with the amount of data I have to analyse.

Sorry about the lack of information, I'm a new user to the site. I couldnt find a way around this problem so I had to seek help. Your help is greatly appreciated, I owe you a pint of the blackstuff "Guiness" if you ever visit the emerald isle...


Report •

#5
December 23, 2010 at 13:23:12
It's amazing what happens once you post data and equations.

Drop the quotation marks.

Quotation marks tell Excel that the values are text and the SUM function will not add Text values.

Try this in a cell:

=1="1"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 24, 2010 at 03:37:27
Yes I have to apologise for the delay in posting the correct information Derby..As suggested the quotation marks were the cause of the problem. This perhaps reflects my limited understanding of Excel. I have to thank you for your help, I could still be faffing around going nowhere without your input...

A big big thanks, happy xmas and a happy new year...


Report •

#7
December 24, 2010 at 07:37:06
I'm glad I could help.

Have a good holiday.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question