Formula for formula description.

[J23Excel]

I would like to create a formula to reference a cell with a formula and then replace each of the cell references within the cell with an offset reference; in effect, I would like to use column c to automatically describe, in plain english, what the formula in cell B1 is calculating. The example formula in the attached is a simple formula, but I would like to use this formula so that more complicated formulas can be described in my spreadsheet. I am thinking that there must be a way to do this with some combination of functions like replace, offset, formulatext or textsplit, but I am struggling with how to best do within excel (without using macros).

Then, I would like to create a formula to automatically write a Lambda function that I can then copy/paste into name manager.

In the attached:

B$1 already equals $C$8/$C/7*365
$C$1 should equal: Accounts Receivable / Revenue * 365
$D$1 should equal: Lambda(Accounts Receivable / Revenue,Accounts Receivable / Revenue*365)


Thank you in advance to the community for suggestions or help (or if this question has been answered previously, to a referral to the answer).

 

[Rijnsent]

Hi J23, welcome to the forum. This is not the complete answer, but this is a start. Note that the stuff you want can be quite hard. If you don't use multiple columns of data, but simple cells, I would start using named ranges, like so: Named Ranges in Excel
This example below was made using XL2BB, which makes showcasing your question much easier, do give it a try!

Map1
	A	B	C	D	E	F	G
1	Rec Days	91,25	=$B8/$B7*365
2				TESTS
3					$B8	$B7	365
4			METHOD 1:		Acc Rec	Revenu	365
5			METHOD 2:		Acc Rec	Revenu	365
6			=$B8/$B7*365	=$B8/$B7*365	=Acc Rec/$B7*365	=Acc Rec/Revenu*365	=Acc Rec/Revenu*365
7	Revenu	500
8	Acc Rec	125
Blad2

Cell Formulas
Range	Formula
B1	B1	=$B8/$B7*365
C1	C1	=FORMULATEXT(B1)
D3:G3	D3	=TEXTSPLIT(FORMULATEXT(B1),{"=";"*";"-";"+";"/"})
D4:G4	D4	=IFERROR(OFFSET(INDIRECT(D3),0,-1),D3)
D5:G5	D5	=IFERROR(INDIRECT(ADDRESS(ROW(INDIRECT(D3)),1)),D3)
C6	C6	=C1
D6:G6	D6	=SUBSTITUTE(C6,D3,D4)
Dynamic array formulas.

 

[J23Excel]

Thank you for your helpful suggestions and solution! This works well and I can further adapt it for my use case from here.

I was not aware of the xl2bb add-in but will get that installed.

Thank you!