Hello again, need to retrieve the cell calculations of the formula and not the results. Whats the way of doing it again?

[Spyros13]

I've done it before.

Its not f9 (which shows the calclatations in parts of a formula). Besides, f9 is not working for me right now.

Its something else which is quite simple. But havn't (though should and could have) used all this time.

I did it once for a concatonate. Now want to do it for my Aggregate Small function.

Whats the best way to paste the viewing of the formula calculations as the result itself?

 

[Kamolga]

Do you mean formula evaluation in formula tab? That is what I use when I have an error or need to understand the result of nested ifs for example, to find out where it comes from. You click once evaluate and then enter every time.


Evaluate Formula: Alt + T,U,F. For quickly evaluate you large and tough formula, Press Alt + T, then U and F. It will launch dialog box of Evaluate formula. To show the key tips, press ALT or F10

 

[Scott Huish]

If you want Excel to just show all the formulas instead of the result hit CTRL-~


Otherwise, you'll need a UDF like this one:

Function ShowFormula(r As Range) As String
If r.HasFormula Then ShowFormula = r.Formula
End Function

Excel 2010
	A	B	C
1	=C1		=C1
2	7
Sheet1

Cell Formulas
Range	Formula
A1	=C1
C1	=showformula(A1)

 

[Spyros13]

Hi Scott, Thank you for your reply.

Thats a very useful Macro Formula. But no, that is not what I want or need.

I need a formula or 'trick' that evaluates the formula and displays its evaluations, and not the result or the formula itself.

so, instead of =AGGREGATE(15,3,(Sheet2!$E$7:$E$51=V41)/(Sheet2!$E$7:$E$51=V41)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6)),3) giving the 3rd value (which is 24) in the column found, I just actually do just want :

=AGGREGATE(15,3,{#DIV/0!,7,#DIV/0!,#DIV/0!,16,#DIV/0!,24,#DIV/0!,#DIV/0!,#DIV/0!.....#DIV/0!},3) displayed in the cell instead.

Ive done it before for a large array formula check to utlimately concatonate these results, but i forgot how to !! I would very much appreciate any help you can give in doing this. This would be ultimately useful for me.

I know it can be done. I just forgot how to. Thank you for any help in advance.

S.

 

[Spyros13]

Yes I do mean that exactly !! But to show these perminantly. in the cell. I had managed to do it before for and index match array. My problem is I lost the workbook sheet. Otherwise would have just opened it up. (Laptop was stolen)

 

[Spyros13]

Do you mean formula evaluation in formula tab? That is what I use when I have an error or need to understand the result of nested ifs for example, to find out where it comes from. You click once evaluate and then enter every time.


Evaluate Formula: Alt + T,U,F. For quickly evaluate you large and tough formula, Press Alt + T, then U and F. It will launch dialog box of Evaluate formula. To show the key tips, press ALT or F10

Yes I do mean that .

 

[Kamolga]

I can't even copy the evaluation at a certain stage to paste it into a cell. I certainly could appreciate a dynamic (update on value change) evaluation in a cell.
Creating a UDF to replace formula parts by its values is overcomplicated.

On my computer in I select A2:A6 in

=AVERAGE(A2:A6)

and press alt on the right of my spacebar with F9, I will read

=AVERAGE({1;2;3;4;5})

in formula bar. If I press enter, the formula becomes

=AVERAGE({1;2;3;4;5})

but the cell still shows

3

If then I click show formula in formula tab, the cell shows

=AVERAGE({1;2;3;4;5})

.

Of course the issue with

=AVERAGE({1;2;3;4;5})

is that the formula is static, does not depends on A2:A6 changing values anymore

 

[Spyros13]

I can't even copy the evaluation at a certain stage to paste it into a cell. I certainly could appreciate a dynamic (update on value change) evaluation in a cell.
Creating a UDF to replace formula parts by its values is overcomplicated.

On my computer in I select A2:A6 in

=AVERAGE(A2:A6)

and press alt on the right of my spacebar with F9, I will read

=AVERAGE({1;2;3;4;5})

in formula bar. If I press enter, the formula becomes

=AVERAGE({1;2;3;4;5})

but the cell still shows

3

If then I click show formula in formula tab, the cell shows

=AVERAGE({1;2;3;4;5})

.

Of course the issue with

=AVERAGE({1;2;3;4;5})

is that the formula is static, does not depends on A2:A6 changing values anymore

Thank you. I don't on my current laptop and stand alone latest excel , I cant even get CTRL-f9 or ALT-f9 to work .

Though show formula does work, and last time I managed an in-sheet way to show these in the cells.

There was a way to get those Function Argument Results into other cells. I know it. It was tricky , but I did it once successfully.

No udf required.

 

[Spyros13]

Concatating formula , or formula arguments for a lookup/match

(Sheet2!$E$7:$E$51=V123)/(Sheet2!$E$7:$E$51=V123)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6))

my answer is there, for every record.

How do I either get it to evaluate and show these results concatonated?

or , and id accept this, how can I perminantly strip out (or reveal, or copy) this formula's arguments as they become evaluated per record, and paste that somewhere else?

Columns E,F,G and H (rows 7 to 13):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mike[/TD]
[TD]Fred[/TD]
[TD]Sam[/TD]
[TD]James[/TD]
[/TR]
[TR]
[TD]Poo[/TD]
[TD]Rich[/TD]
[TD]Stacey[/TD]
[TD]Iron[/TD]
[/TR]
[TR]
[TD]Poo[/TD]
[TD]Craig[/TD]
[TD]Frog[/TD]
[TD]Water[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Lara[/TD]
[TD]Liz[/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]Boothey[/TD]
[TD]Boydy[/TD]
[TD]Miguel[/TD]
[TD]Zen[/TD]
[/TR]
[TR]
[TD]Ashley[/TD]
[TD]Andrews[/TD]
[TD]Stacey[/TD]
[TD]Ann[/TD]
[/TR]
[TR]
[TD]Fode[/TD]
[TD]Smith[/TD]
[TD]Dribble[/TD]
[TD]Parsley[/TD]
[/TR]
</tbody>[/TABLE]


The Argument in excel formula help is showing all the occurances of "Poo" which is what im looking for.

{Div;2;3;Div;Div;Div...}

It will do so for everyother record. I need the excel argument only (not the result in the cell which is just an error) or I need it to evaluate as its showing in the formula argument box.

 

[Spyros13]

Re: Concatating formula , or formula arguments for a lookup/match

It is really unfortunate.

A. I DID DO IT BEFORE - WHICH SAVED ME ALOT OF TIME (& I used the mid function to extraxt the values i needed afterwards). Or I did text to columns.

B. my lap top with 4 years work was stolen, and its been a pain to re-do some of my unsaved work

C. Straight after doing this "excel argument" extractor paste process, I had other work to do, saved it, and never thought in milliom yeats 6 months later my computer would be stolen.

Now I have been souring the web all over for the process again, which was straightforward. and "you couldnt believe it if you saw it".

It can be done & ill kicking myself If I let this one pass me by.