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

 

[Aladin Akyurek]

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

Art: The formula does do that:

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

When I16 is not a number, it puts in J16 a blank (I.e., ""). Right?

 

[artz]

Hi Aladin,

Yes, you are correct; you've got it. I need to move this to the 20 term calculation but otherwise, unless I find a surprise, I think that you've got it.

Thanks again to you and sgh,

Art

 

[Aladin Akyurek]

Hi Aladin,

Yes, you are correct; you've got it. I need to move this to the 20 term calculation but otherwise, unless I find a surprise, I think that you've got it.

Thanks again to you and sgh,

Art

I trust that will not be a problem. Our thanks for the feedback.