excel calulations?

Question

I have a column of numbers that are generated by various formulas. Some of the number are positive and some negative (depending on formula inputs). I want to add the colum but exclude from the sum those numbers that are negative. How do I do this.

Answer 1

You don't need to add any new columns. If the numbers to be added are say in D2:D100, you can use this formula to add just the positive ones:
=sum(if(D2:D100>0,D2:D100,0))

Use Ctrl-Shift-Enter (rather than just Enter) to input the formula.

Answer 2

It's just a matter of the IF statement. This will be more useful if you don't want to lose your numbers while calculating the sum of the positive and negative numbers.

1. Create two more columns. On the first column you created, on the cell adjacent to the first number, type this:
IF([cell number]>0,[cell number],0)
just replace the [cell number] annotations with that cell number. This will display all non-negative numbers. Click and drag the lower-right corner of that cell until the last number.

2. Do the same for the second column you created. This time, type
IF([cell number]<0,[cell number],0)
This will display all negative numbers on your list.

3. After this, just place an autosum function on the end of the list and you've calculated what you want.

For more, here's the source file i created for your question:

Hope it helps.
The file has no virus, don't worry.. :-)

Answer 3

You can do it with 3 columns:

- In column A, you have your original numbers (positive or negative)

- In column B, you write a formula. For example, in cell B1, you would write:

= if(A1>0; 1; 0)

- In column C, you write another formula. For example, in cell C1, you write:

= A1*B1

- You duplicate these formulas all the way down the columns, so that each row is only concerned with itself: you can use Control-D to do this.

- Now, column A still has your original numbers; column B has 1 for positive numbers, and 0 for negative (or zero); and column C will be the same as A for positive numbers and 0 for everything else.

- So you can just sum over column C, and you're done.

Answer 4

Assuming you are in excel, there is an "if , then a, then b" type function which can replace negative numbers with zero. Then you can add the column.

Answer 5

Assuming you don't want to add any extra columns of numbers use the following formula. It assumes you are adding the numbers in the cells A1 thru A10. If you are adding different cells adjust the formula accordingly.

=SUMPRODUCT(A1:A10,N(A1:A10>0))

Answer 6

One container - 12 products Your gadget shows 4 boxes and seven products meaning in style of goods it would be (4x12) + 7 = fifty 5 however the actual count style shows in ordinary terms 2 boxes and 13 products (in products, (2x12) + 13 = 37 So in products the lack is fifty 5-37 = 18 in different words , a million container and six products. So in excel what you're able to do is tutor them in products particularly than in boxes. to illustrate stability as according to the gadget (4x12) + 7 = fifty 5 stability as according to actual count style (2x12) + 13 = 37 as a result scarcity is (fifty 5-37) = 18 Divide this by employing 12 = 18/12 = a million.5 boxes - meaning a million container and six products in case you nevertheless desire any rationalization, only deliver me a mail