MS Excel Formula is needed

Asked By jamespaul20 0 points N/A Posted on - 08/07/2011

I need an Excel formula which allows summing a single column. If the summing value comes negative, it will show “1” and if the value comes negative it will show the true value.

Just for an example: C1:C20 the summing value is 15, it will show “1” or   C1:C20 the summing value is -15; it will show “-15”.

Please Help.

• Status: Open
• Question Views: 1160
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Sherin Viluel

Go To Solution

Answered By AngusM 0 points N/A #111080

MS Excel Formula is needed

It's actually very simple.

• Enter this formula in the cell that you want the computation to appear in:

  =IF(SUM(D3:D12)>0,1,SUM(D3:D12))

• Just replace both instances of "(D3:D12)" with the range of cells that you will be working with.

Just to explain the what the formula does…

• If will check the condition "(SUM(D3:D12)>0" meaning if the sum of the contents of the given range of cells is greater than 0 (or in other words, a positive number), the condition will return TRUE.
• If the condition is TRUE, the cell will display "1".
• If the condition is FALSE, it will display the sum of the numbers in the specified range of cells.

Hope this helps.

About AngusM

Questions
0

Answers
21

Best Answers
12

• Vote Up 0 Vote Down

• Posted on - 08/14/2011
• Question Category: MS Excel

Best Answer

Best Answer

Answered By Sherin Viluel 0 points N/A #111081

MS Excel Formula is needed

 

According to your requirement what you want is if the total value (Sum) is positive the answer should show “1” and if the total value (sum) is negative the answer should show the same negative total value.

I will explain a way to get the result that you expected by using the same example that you took.(That means I will take the sum of C1 to C 20)

First select a cell that you want to get the expected result. (Please don’t take C1, C20 and any cell between them)

Then type the following formula on that cell.

=IF (SUM (C1:C20)>0,"1", SUM (C1:C20))

In the following image I have typed that formula and see the result “1” when the total (sum) value is positive.

 

In the following image it will show the same negative result when the total value is negative.

 

 

As such you are able to get the result which you want.

About Sherin Viluel

Questions
0

Answers
50

Best Answers
37

• Vote Up 0 Vote Down

• Posted on - 06/17/2012
• Question Category: MS Excel