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

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
175
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Last edited:
Upvote 0
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:
Code:
Function ShowFormula(r As Range) As String
If r.HasFormula Then ShowFormula = r.Formula
End Function


Excel 2010
ABC
1=C1=C1
27
Sheet1
Cell Formulas
RangeFormula
A1=C1
C1=showformula(A1)
 
Last edited:
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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 .
 
Upvote 0
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
Code:
=AVERAGE(A2:A6)
and press alt on the right of my spacebar with F9, I will read
Code:
=AVERAGE({1;2;3;4;5})
in formula bar. If I press enter, the formula becomes
Code:
=AVERAGE({1;2;3;4;5})
but the cell still shows
Code:
3
If then I click show formula in formula tab, the cell shows
Code:
=AVERAGE({1;2;3;4;5})
.

Of course the issue with
Code:
=AVERAGE({1;2;3;4;5})
is that the formula is static, does not depends on A2:A6 changing values anymore
 
Last edited:
Upvote 0
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
Code:
=AVERAGE(A2:A6)
and press alt on the right of my spacebar with F9, I will read
Code:
=AVERAGE({1;2;3;4;5})
in formula bar. If I press enter, the formula becomes
Code:
=AVERAGE({1;2;3;4;5})
but the cell still shows
Code:
3
If then I click show formula in formula tab, the cell shows
Code:
=AVERAGE({1;2;3;4;5})
.

Of course the issue with
Code:
=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.
 
Upvote 0
Concatating formula , or formula arguments for a lookup/match

Code:
(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):

MikeFredSamJames
PooRichStaceyIron
PooCraigFrogWater
HarryLaraLizWood
BootheyBoydyMiguelZen
AshleyAndrewsStaceyAnn
FodeSmithDribbleParsley

<tbody>
</tbody>


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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top