CODEWITHSUNDEEP
excelmath
Dave M
Dave M

Reputation: 51

Excel formula: unnecessary extra steps in SUMIF process

It seems like I'm overlooking a very simple math formula (ideally without "if's") to do this from the start.

Not looking for a programming or spreadsheet solution, all within one cell, to solve this in multiple steps -- I can combine all that myself. Maybe I'm wrong, but it seems I'm breaking apart a very simple mathematical / algebraic solution into a number of unnecessary steps.

A    | B      | C    | D    | E        | F |  
:- - | :----- | :--- | :--- | :----    | :----------- |  
Rank | Col B | Col C | B+C  | Formula1 | B+C+Formula2 |  
1    |  700  |  20   |  720 |          |  849  |  
2    |  680  |  1    |  681 |          |  810  |  
3    |  679  |  1    |  680 |       5  |  809  |  
4    |  678  |  5    |  683 |       7  |  807  |  
5    |  673  |  7    |  680 |      11  |  797  |  
6    |  666  |  11   |  677 |      30  |  783  |  
7    |  655  |  30   |  685 |          |  761  |  
8    |  625  |  5    |  630 |          |  706  |  
9    |  620  |  1    |  621 |       2  |  697  |  
10   |  619  |  2    |  621 |       3  |  695  |  
11   |  617  |  3    |  620 |       8  |  691  |  
12   |  614  |  8    |  622 |          |  685  |  
13   |  606  |  1    |  607 |       5  |  670  |  
14   |  605  |  5    |  610 |      40  |  668  |  
15   |  600  |  40   |  640 |          |  658  |  
16   |  560  |  1    |  561 |       2  |  579  |  
17   |  559  |  2    |  561 |       6  |  577  |  
18   |  557  |  6    |  563 |      10  |  573  |  
19   |  551  |  10   |  561 |          |  561  |  
20   |  541  |  1    |  542 |          |  542  |

Hi, for this table, I've taken a couple steps.
1) In Column C, 

=SUM($b2,-$b3)

2) In Column D, 

=SUM($b2,$c2)

3) In Column E, 

=IF(SUM($d2,-$d3)<SUM($b2,-$b3),$c3,"")

At this step, I'm ensuring that all Column D records ("B+C") maintain at least as much distance above the next record, as they originally did in Column B.

4) In Column F, 

=SUM($d2,$e2:$e$21)

Now adding every single Column E figure to all figures above it.

Upvotes: 0

Views: 52

Answers (1)

Salix alba
Salix alba

Reputation: 7824

So we have

C2 = B2 - B3, 
C3 = B3 - B4, .....
D2 = B2 + C2 = 2 * B2 - B3, 
D3 = B3 + C3 = 2 * B3 - B4, ....

E2 = if( D2 - D3 < B2 - B3 , C3 , 0 )
E2 = if( (2 * B2 - B3) - (2 * B3 - B4) < (B2 - B3), B3 - B4, 0)
E2 = if( 2 * B2 - B3 - 2 * B3 + B4) < B2 - B3, B3 - B4, 0)
E2 = if( 2 * B2 - 3 * B3 + B4 < B2 - B3, B3 - B4, 0)
E2 = if( B2 - 2 * B3 + B4 < 0 , B3 - B4, 0)
E2 = if( B2 + B4 < 2 * B3, B3 - B4, 0)
E2 = if( (B2 + B4)/2 < B3, B3 - B4, 0)

So E2 is basically seeing if B3 is greater than the mean of B2+B4.

I can't see a way to easily simplify the column F formula. It could be written in terms of a SUMIF but you probably would need an extra column with 2*B3 - B2 - B4 in it.

It does seem a little unusual as it is more typical to do cumulative sums rather than the acculumation from the bottom.

Upvotes: 0

Related Questions