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

New Member
Joined
Sep 23, 2023
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
BCDEF
11entered valueentered value=C12*D12
1215000081.2
13
14=E12
151200000
16
Sheet4
Cell Formulas
RangeFormula
E11,E14E11=FORMULATEXT(E12)
E12E12=C12*D12
E15E15=E12


1695479548024.png
 
Last edited:
Upvote 1
Solution
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!
 
Upvote 0
Your welcome. Thanks for the feedback. Welcome to the Mr. Excel forum!

Best Wishes!
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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