Named formulas to reference relative rows?

Formula: B2+C2

In columnD I want to reference the "formula" and have it calculate based on the values in whatever row references the formula.

As it stands I can only get the formula to calculate within the same row.




I attached a worksheet of what I'm looking for.

not sure I follow... copying down the formulae generates the correct totals as far as I can see (ie year1 + year2 are relative to the row in which the formula is called) ... but begs the question why not simply:

F2: =SUM($B2:$C2)
G2: =$B2-$C2

copied down

Apologies if I'm missing a key point here...

The worksheet I sent was very simplified to not confuse anyone with the mess Im working on.

Basically, Im working on a weight chart which alot of the calculations vary depending on male / female.

If I leave things as they are the functions get more messy than I like:
=IF(ISNUMBER(C13),IF(ISNUMBER(SEARCH("FEMALE",$D$9)),SUM(163.205*LOG10(E13+F13-D13)-97.684*LOG10(feet*12+inches)-78.387)/100,IF(ISNUMBER(SEARCH("MALE",$D$9)),SUM(((86.01*LOG(E13-D13,10))-(70.041*LOG(feet*12+inches,10))+36.76)/100),"- -")),"")

I could just leave it as is (and copy down, which is what i have been doing), but Im trying to simplify things so they arn't so messy if I ever need to modify things.

It would I think be a good idea to post a small representative sample of your data (remove names etc) with existing results present, this way we can test with reflective data and ensure our logic ties out to the existing non-named versions.

I see your point re: shortening the formula - there are differing opinions on whether or not one should use Named Ranges - yes they make it more readable but perhaps at the expense of audit trail (more complex to work through should a problem arise) ... they can also affect performance.

Perhaps we may be able to get there...

If we assume the formula is going in say G13 - then highlight that cell and then subsequently create the Named Ranges such that:

Please Login or Register  to view this content.

You should then find you can use:

Please Login or Register  to view this content.

Having looked at the file, if you follow the earlier instructions (but amending existing names as opposed to creating new ones) you should find you get your desired results via the shortened formulae.

Works without incident for me I'm afraid... be sure you're entering $D13 and not $D$13 etc in your formulae.

EDIT: added working attachment

I have just added your file updated as per instructions to my last post.