How to rename a formula

Hi all,

I thought this might be simple, but it seems to be more difficult than it sounds.

If I have 2 formulas in the one cell. Example:
=sum(A1:A5)*IF(A1>0,A1*B1,0)

Is there a way to rename each formula as such:
=TOTAL*VARIABLE

Where;
TOTAL=sum(A1:A5)
and
VARIABLE=IF(A1>0,A1*B1,0)


BUT... The tricky part is, I don't want the renamed formulas to be absolute. I.e. If I drag the formula down, I would like the cell referencing to drag down also.


I have attached an example for reference.

Thanks in advance.
D.

use the functionality of Named Ranges.

create one Named Range called Total and another called Variable with exactly the formula you want, and then use those two Named Ranges in a formula just like you desire.

so you want a formula that doesnt reference cells, and you want a formula in each cell....but you want them both in the same cell?

sorry, that doesnt make any sense to me, perhaps you can try top explain in more detail what you are trying to achieve?

I believe that icestationzebra answered your question for you already then. If you use "define name", give the ramne the name TOTAL, and in the "refers to" section, use =SUM(Sheet1!$A$1:$A$5), then every time you enter =total, it will give you the total of A1:A5

Originally Posted by icestationzbra

use the functionality of Named Ranges.

create one Named Range called Total and another called Variable with exactly the formula you want, and then use those two Named Ranges in a formula just like you desire.

OK, I understand now. Im not sure if what you want is possible using just regular functions/formulas, perhaps some-one can put some VBA code together for you

Go to name manager and define name there u can do that
Attached file is there to check
do =answer() and put enter and there would no cell referencing in that

@vjharry, if you do the naming correctly, it will give you the total you want. However, the ranges do not adjust if copied/dragged

@m1zz13, I would question why you need this in the 1st place. If you create the formula, like you have, and then just copy/drag, I dont see the problem. If your formulas are that long, maybe they can be condensed?

please do Alt+M+M
there you will find that

or in Formulas tab there u will find that ...

@FDibbins .....i have shown all it want by m1zz13..

@m1zz13.. pl let me now if any thing left
Thanks Again,

FDibbins,
I am trying to make a 400 character+ formula into something more manageable. The only issue, once this is finished, I would like to copy this onto more cells, and when I drag it down down, whichever cells that aren't absolute, I need them to continue referencing down the column.

it is merely to make it more visually manageable and less confusing.


VJay,
Thanks it works perfectly.

yes I know how to get into the name manager...your method does not work. inthe file you uploaded, type in =answer into any cell - try G19 - it gives 0

ok cool, glad you got the answer you want