MS Excel Formula is needed

Asked By 0 points N/A Posted on -
qa-featured

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.

SHARE
Best Answer by Sherin Viluel
Answered By 0 points N/A #111080

MS Excel Formula is needed

qa-featured

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.

Best Answer
Best Answer
Answered By 0 points N/A #111081

MS Excel Formula is needed

qa-featured

 

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.

Login/Register to Answer

Related Questions