Sum of differences formula

[artz]

Hi,

I am seeking a cell formula or VBA that will provide the sum of 20 differences starting with the most current value, in this case, the value in I39. The range is dynamic, first data is in I8 following a header in I7. If the formula includes cells that are either blank or text (like I7) a blank should be returned for that cell.

=SUM($I$39-I37,$I$39-I36,$I$39-I35,$I$39-I34,$I$39-I33,$I$39-I32,$I$39-I31,$I$39-I30,$I$39-I29,$I$39-I28,$I$39-I27,$I$39-I26,$I$39-I25,$I$39-I24,$I$39-I23,$I$39-I22,$I$39-I21,$I$39-I20,$I$39-I19,$I$39-I18)

A capture of the worksheet is shown below:

Excel Workbook
	I	J	K
7	Close	CI	CI Desired
8	43.13	#REF!	*
9	43.14	#VALUE!	*
10	43.7	#VALUE!	*
11	44.44	#VALUE!	*
12	44.67	#VALUE!	*
13	44.93	#VALUE!	*
14	44.55	#VALUE!	*
15	44.94	#VALUE!	*
16	45.65	#VALUE!	*
17	45.69	#VALUE!	*
18	45.81	#VALUE!	*
19	46.51	#VALUE!	*
20	45.88	#VALUE!	*
21	46.16	#VALUE!	*
22	45.49	#VALUE!	*
23	46.08	#VALUE!	*
24	45.8	#VALUE!	*
25	47.12	#VALUE!	*
26	48.33	#VALUE!	*
27	48.16	#VALUE!	*
28	49.35	#VALUE!	*
29	50.15	179.42	179.42
30	49.05	173.2	173.2
31	48.28	166.19	166.19
32	48.76	160.84	160.84
33	49	157	157
34	48.61	152.91	152.91
35	50.79	148.84	148.84
36	50.43	144.78	144.78
37	50.06	138.93	138.93
38	52.7	134.15	134.15
39	54.48	129.78	129.78
Sheet1

The simple sum formula shown below cannot adjust for variability in the number of rows containing data, nor can it exclude text or blanks. Column J shows how the output should look. Does anyone in the Forum have a suggestion for code to do this?

Thanks,

Art

 

[artz]

I received an email with the following message "HOTPEPPER has just replied to a thread you have subscribed to entitled - Sum of differences formula - in the Excel Questions forum of MrExcel Message Board."

HOTPEPPER,

If you attempted to reply maybe you didn't hit "Submit Reply" after a preview because I don't see your post here.

Thanks,

Art

 

[Aladin Akyurek]

Try to set up an example for "the sum of" 4 "differences" along with the desired results without any formulas and explain in words how the results are calculated...

 

[artz]

Hi Aladin,

Thanks for your response. I am not sure how you mean to do this but I'll try using only 4 sums rather the 20 actually used.

In the example below, the calculation goes as follows:

A difference is calculated in J16 starting at the last value in Column I, for this example, I16 and taking a first difference with cell I14 (two rows back). All subsequent differences are taken from again, the last value in column I, I16 and one row back, i.e, I13. The next difference is then between I16 and I12, then between I16 and I11.

For row 15 in column I, the first difference is with cell I13 (two rows back). Subsequent differences I15 and one row back, I15 -I12, then I15-I11 and then I15-I10. And so on back.

The differences are then summed.

Said another way, the calculation for J16, would be (I16-I14 + I16-I13 + I16-I12 + I16-I11), in J15, the calculation would be: (I15-I13 + I15-I12 + I15-I11 + I15-I10).

I realized while writing this, that my initial formula was wrong in that I16 is not stationary. I think that I've got it right now.

Also, below is the worksheet capture:

Excel Workbook
	I	J
7	Close	CI
8	43.13	172.52
9	43.14	#VALUE!
10	43.7	#VALUE!
11	44.44	#VALUE!
12	44.67	#VALUE!
13	44.93	5.31
14	44.55	2.25
15	44.94	2.02
16	45.65	4.01
Sheet1

The values in cells J8 - J12 are incorrect because they refer to cells that re blank or text filled.

The values in J13 through J16 are calculated correctly.

As I mentioned in my earlier post, the number of rows is variable and the formula needs to place a blank in the cell if the "sum of the differences" cannot be calculated due to a blank or text filled cell.

Also, in the real calculation, the calculation goes back 21 rows (from the last row used) so that there are 20 differences (current row, skip one row, then 19 more) that are summed.

I hope that this is more clear.

Thanks,

Art

 

[shg]

How about, in J13 and copy down,

=4*I13-SUM(I8:I11)

 

[artz]

shg,

Thanks for your response. Not what I expected the formula to be but still a some issues. In order to exclude blanks, I tried the following:

=IF(I11:I14<>"",4*I16-SUM(I11:I14,""))

Excel doesn't like this formula, I don't know why and also, I need to return a blank if there is text in the cell. (header row)

The other one too is that the number of rows can vary. I don't think that your formula adjusts for this. Maybe I didn't say it (and should have) but if there is no further data in column I, a blank should be returned.

Any thoughts?

Thanks,

Art

 

[Aladin Akyurek]

shg,

Thanks for your response. Not what I expected the formula to be but still a some issues. In order to exclude blanks, I tried the following:

=IF(I11:I14<>"",4*I16-SUM(I11:I14,""))

Excel doesn't like this formula, I don't know why and also, I need to return a blank if there is text in the cell. (header row)

The other one too is that the number of rows can vary. I don't think that your formula adjusts for this. Maybe I didn't say it (and should have) but if there is no further data in column I, a blank should be returned.

Any thoughts?

Thanks,

Art

Does this help?...

=IF(COUNT(I11:I14)=4,4*I16-SUM(I11:I14,""))

 

[artz]

Hi Aladin,

I just tried you code in cell J16 and it returns, #VALUE!, so it seems to have some issue.

Any thoughts welcome.

Thanks,

Art

 

[Aladin Akyurek]

Hi Aladin,

I just tried you code in cell J16 and it returns, #VALUE!, so it seems to have some issue.

Any thoughts welcome.

Thanks,

Art

I had just replaced your I11:I14<>"" with the COUNT bit in your version,
where a paren is missing from the essential bit sgh suggested. Thus:

=IF(COUNT(I11:I14)=4,4*I16-SUM(I11:I14),"")

This does not test whether I16 itself is empty. We need perhaps to add another control...

=IF(ISNUMBER(I16),IF(COUNT(I11:I14)=4,4*I16-SUM(I11:I14),""),"")

Does this do better?

 

[artz]

Hi Aladin,

We are almost there. The formula: =IF(ISNUMBER(I16),IF(COUNT(I11:I14)=4,4*I16-SUM(I11:I14),""),"") works fine, however, we need one more IF to place a blank, "", in cell J16 if I16 is blank. Then I think we've got it.

Do you see how to do this?

Thanks,

Art