Addition of negative and positive number

[amit dev thapa]

there are 3 columns , sales , loss and % .. what i was trying to do is ..if the number in D3 is negative .. than it should add all negative number in (d3:13) and then Divide D3 by this number , primarily i want to see , if the Loss (negative number ) adds up to ... than what is the share of A( Cell B3) .. similarly profit (positive number ), if the since B2 cell is positive then add all positive number and what is the share of company B .. D4/ addition of all positive number

 

[NARAYANK991]

Hi ,

Your explanation in the post does not match the formula in your uploaded workbook , so it is not clear what you want to do.

Does the following formula help ?

=D3/SUMPRODUCT((SIGN($D$3:$D$13) = SIGN(D3)) * ($D$3:$D$13))

Narayan

 

[amit dev thapa]

Hi ,

Your explanation in the post does not match the formula in your uploaded workbook , so it is not clear what you want to do.

Does the following formula help ?

=D3/SUMPRODUCT((SIGN($D$3:$D$13) = SIGN(D3)) * ($D$3:$D$13))

Narayan

hi Narayan .. it does not work , i know that formulae is incorrect hence need help..in E3 cell , if (D3<0.. then add negative number and divide it by D3 or if the D3> 0, then add all positive number and divide it by D3

 

[NARAYANK991]

hi Narayan .. it does not work , i know that formulae is incorrect hence need help..in E3 cell , if (D3<0.. then add negative number and divide it by D3 or if the D3> 0, then add all positive number and divide it by D3

Hi ,

Can you not use the formula I posted earlier with a slight change ?

=SUMPRODUCT((SIGN($D$3:$D$13) = SIGN(D3)) * ($D$3:$D$13))/D3

Narayan

 

[amit dev thapa]

Hi ,

Can you not use the formula I posted earlier with a slight change ?

=SUMPRODUCT((SIGN($D$3:$D$13) = SIGN(D3)) * ($D$3:$D$13))/D3

Narayan

Thanks for your prompt response ..it is still not working , it is showing 256% on E3 cell.. actual should be 39% (sum of negative number is -5,804,150.31 and D3 number is -2,226,388), when you divide cell D3 with the above number you get 39%

 

[NARAYANK991]

Hi ,

39% was what you would have got with my first posted formula , and you said it is not working.

I give up.

Narayan

 

[amit dev thapa]

Hi ,

39% was what you would have got with my first posted formula , and you said it is not working.

I give up.

Narayan

apologies .. it is working .. i guess in your previous formula , i did not copy the whole formulae
Narayan .. is this can be done with some easy formula

 

[NARAYANK991]

Hi ,

What can be simpler than this formula ?

The formula is multiplying two arrays ; one array has TRUE / FALSE values , TRUE where the sign of the value is the same as the sign of the value in the current cell , and FALSE where the signs are different ; the second array is an array of the values themselves.

Basically the above gives us the sum of all negative values if the current value is negative , and the sum of all positive values if the current value is positive.

I doubt that you can have a simpler formula , unless you use helper cells to store these two sums , and then divide each value by the appropriate sum.

See the attached file.

Narayan

 

[amit dev thapa]

Hi ,

What can be simpler than this formula ?

The formula is multiplying two arrays ; one array has TRUE / FALSE values , TRUE where the sign of the value is the same as the sign of the value in the current cell , and FALSE where the signs are different ; the second array is an array of the values themselves.

Basically the above gives us the sum of all negative values if the current value is negative , and the sum of all positive values if the current value is positive.

I doubt that you can have a simpler formula , unless you use helper cells to store these two sums , and then divide each value by the appropriate sum.

See the attached file.

Narayan

Thanks got it , much appreciated