Display result of formula which is millions as nn.n without losing original value of cell and no 'M' in the result cell either.

[bookendinSA]

Hi,
Please and thank you for your help. All my attempts to correctly display changes the values of the cell (view the true value when clicking on the cell). I must not have an "M" after the value - just the number. So custom formatting is not helping me - I probably don't know enough - hence my struggle. And the display results cannot be in an additional column/cell - it must be correctly displayed in the cell "E12" which also contains a formula: See below. The xl2bb is not working, even though I've enabled macros - does not copy the code so I can't paste it.

Formula
C12 | ='Sales data'!D4 | Value = 150,000
D12 | ='Sales data'!E4 | Value = 8.00
E12 | =(C12*D12) | Value = 1,200,000 | Formatting = number, no decimal spaces, show thousands delimiter

Problem: E12 should display as 1.2 BUT clicking on the cell should still display the real value of 1,200,00

 

[awoohaw]

I think this custom format works:
(but the cell cannot ever show 1200000 because you have a formula there not a value.)
(It is possible with a VBA on worksheet change macro, though but I am no expert on that).

Mr excel questions 64.xlsm
	B	C	D	E	F
11		entered value	entered value	=C12*D12
12		150000	8	1.2
13
14				=E12
15				1200000
16
Sheet4

Cell Formulas
Range	Formula
E11,E14	E11	=FORMULATEXT(E12)
E12	E12	=C12*D12
E15	E15	=E12

 

[bookendinSA]

Thank you so much! The data provided are pivot tables output & they obviously formatted it the way you've just advised me to do. Since there is no formula in a pivot table it works. I was trying to match their display format in my working papers (busy with studies) which as you say is never going to work with formula. My solution is to use piviot tables to extract the data from my working papers and then to apply your formula advice. Much gratitude!

 

[awoohaw]

Your welcome. Thanks for the feedback. Welcome to the Mr. Excel forum!

Best Wishes!