rolling sum with specified start and end row

%3CLINGO-SUB%20id%3D%22lingo-sub-1491895%22%20slang%3D%22en-US%22%3Erolling%20sum%20with%20specified%20start%20and%20end%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1491895%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20calculate%20the%20surface%20under%20a%20segment%20of%20line%20BF%3A%20y%20%3D%20x%26nbsp%3B%20(see%20image%20below)%3C%2FP%3E%3CP%3Evia%20pythagoras%20the%20following%20can%20be%20deduced%3A%3C%2FP%3E%3CP%3E0.5(d-c)(b-a)%2Bc(b-a)%3C%2FP%3E%3CP%3EWhen%20substituting%3A%20b-a%20%3D%20n%3C%2FP%3E%3CP%3E0.5n(d-c)%2Bcn%3C%2FP%3E%3CP%3EIn%20Excel%20column%20BF%20specifies%20the%20y(x)%3C%2FP%3E%3CP%3Esurface%20under%20BF%20in%20excel%3A%20SUMIF(INDIRECT(%E2%80%9CBF%22%26amp%3Ba%20%26amp%3B%20%22%3ABF%22%26amp%3Bb)%2C%22%26lt%3B%26gt%3B%23N%2FA%22)%3C%2FP%3E%3CP%3Ewhen%20putting%20the%20first%20formula%20in%20excel%2C%20it%20only%20mimics%20the%20indirect%20function%20when%20adding%20%2B1%20for%20segment%20n%3A%26nbsp%3B%200.5(n%2B1)(d-c)%2Bc(n%2B1)%3C%2FP%3E%3CP%3EWhy%20do%20I%20have%20to%20do%20that%2C%20how%20does%20excel%20approach%20the%20indirect%20function%20exactly%3F!!!%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22baseflow%20segment.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201298i6BC9E6A3FF0D5DC7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22baseflow%20segment.JPG%22%20alt%3D%22baseflow%20segment.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1491895%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1492439%22%20slang%3D%22en-US%22%3ERe%3A%20rolling%20sum%20with%20specified%20start%20and%20end%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492439%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711189%22%20target%3D%22_blank%22%3E%40treg2020%3C%2FA%3E%26nbsp%3Bhello%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20just%20asking%20how%20the%20INDIRECT%20function%20works%3F%20If%20so%2C%20it%20returns%20a%20range%20reference%20from%20a%20static%20parameter.%20There%20are%20a%20few%20functions%20which%20do%20this.%20You%20can%20watch%20%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2F-wnqlQB_j3k%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ethis%20video%3C%2FA%3E%20to%20see%20that%20and%20others.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi all, 

 

I want to calculate the surface under a segment of line BF: y = x  (see image below)

via pythagoras the following can be deduced:

0.5(d-c)(b-a)+c(b-a)

When substituting: b-a = n

0.5n(d-c)+cn

In Excel column BF specifies the y(x)

surface under BF in excel: SUMIF(INDIRECT(“BF"&a & ":BF"&b),"<>#N/A")

when putting the first formula in excel, it only mimics the indirect function when adding +1 for segment n:  0.5(n+1)(d-c)+c(n+1)

Why do I have to do that, how does excel approach the indirect function exactly?!!!baseflow segment.JPG

1 Reply
Highlighted

@treg2020 hello there,

 

Are you just asking how the INDIRECT function works? If so, it returns a range reference from a static parameter. There are a few functions which do this. You can watch this video to see that and others. 

Most Excel users know OFFSET returns a reference to a range of cells, but there are actually 8 functions that return references. Download the Excel file here...